Converting Numbers to Time in Excel (All Things to Know)

Method 1 – Use Format Cells Window

• Select Cell C5: Click on cell C5 in your Excel worksheet.
• Apply the Formula: Enter the following formula in the formula bar:

• • This formula will convert the value in cell B5 (which represents hours) into a time format.

• Drag the Fill Handle: Click and drag the Fill Handle icon (a small square at the bottom-right corner of the selected cell) down to cell C9. This will apply the same formula to the entire range from C5 to C9.

• Open the Format Cells Window: With the range C5:C9 still selected, press Ctrl+1 (or right-click and choose Format Cells) to open the Format Cells dialog box.

• Choose Time as Category: In the Format Cells window, navigate to the Number tab. Select Time as the category.
• Select Intended Type: Choose the desired time format (e.g., “h:mm:ss” for hours, minutes, and seconds).
• Click OK: Confirm your selection by clicking the OK button.

As a result, the output in cells C5 to C9 will display the converted time values based on the original numerical data in column B.

Method 2 – Using the TEXT Function

• Select Cell C5: Click on cell C5 in your Excel worksheet.
• Insert the Formula: Enter the following formula in the formula bar:

• • This formula will convert the value in cell B5 (which represents hours) into a time format with AM/PM notation.

• Drag the Fill Handle: Click and drag the Fill Handle icon down to cell C9. This will apply the same formula to the entire range from C5 to C9.

Method 3 – Using TIME, LEFT, RIGHT and LEN Functions

• Select Cell C5: Click on cell C5.
• Apply the Formula: Enter the following formula:

• • This formula converts the numerical value in cell B5 (representing hours) into a time format.

• Drag the Fill Handle: Drag the Fill Handle icon down to cell C9 to apply the formula to the entire range.

Method 4 – Running Excel VBA Code

• Choose the Intended Sheet: Navigate to the sheet where you want to perform the conversion.
• Open VBA Editor: Press Alt+F11 to open the VBA Editor.

• Insert a Module: Go to Insert and click Module.
• Insert the Following Code and RUN:

Sub FormatNumberToAMPM()

    Dim timeRange As Range
    Dim cell As Range
   
    Set timeRange = Range("B5:B9")
   
    timeRange.Copy Destination:=Range("C5:C9")
   
    For Each cell In Range("C5:C9")
        cell.Value = cell.Value / 24
        cell.NumberFormat = "hh:mm:ss AM/PM"
    Next cell
End Sub

• • This VBA code converts the values in the specified range (B5:B9) from numerical format (hours) to time format with AM/PM notation.

• • The resulting output will appear as follows:

How to Convert Numbers to Time Units in Excel

• Select Cell C5: Click on cell C5 in your Excel worksheet.
• Insert the Formula: Enter the following formula in the formula bar:

• • This formula will convert the value in cell B5 (which represents hours) into a time format with custom labels for hours, minutes, and seconds.

• Drag the Fill Handle: Click and drag the Fill Handle icon down to cell C9. This will apply the same formula to the entire range from C5 to C9.

Read More: Convert Number to Time hhmmss in Excel

How to Convert Hours, Minutes and Seconds to Time in Excel

Case 1 – Convert Hours to Time

• Choose Cell C5: Click on cell C5.
• Enter the Following Formula:

• • This formula converts the numerical value in cell B5 (representing hours) into a time format.

• Drag the Fill Handle: Drag the Fill Handle icon down to cell C9 to apply the formula to the entire range.

• Format as Time: Select the range C5:C9, go to the Home tab, extend the Down-arrow, and choose Time as the number format.

• • The resulting output will appear as follows:

Read More: Convert Number to Hours and Minutes in Excel

Case 2 – Convert Minutes to Time

• Choose Cell C5: Click on cell C5.
• Apply the Formula:

• • This formula converts the numerical value in cell B5 (representing minutes) into a time format.

• Drag the Fill Handle: Drag the Fill Handle icon down to cell C9 to apply the formula to the entire range.
• Format as Time: Select the range C5:C9, navigate to the Home tab, and choose Time as the number format.

• • The resulting output will appear as follows:

Case 3 – Convert Seconds to Time

• Select Cell C5: Click on cell C5.
• Enter the Following Formula:

• • This formula converts the numerical value in cell B5 (representing seconds) into a time format.

• Drag the Fill Handle: Drag the Fill Handle icon down to cell C9 to apply the formula to the entire range.

• Format as Time: Select the range C5:C9, go to the Home tab, and choose Time as the number format.

• The resulting output will appear as follows:

How to Convert Time to Hours, Minutes and Seconds in Excel

• Convert Time to Hours:
  • Choose cell C5.
  • Enter the following formula:

• • • This formula converts the time value in cell B5 to decimal hours.

• • Drag the Fill Handle icon down to cell C9.

• Convert Time to Minutes:

• • Choose cell D5.
  • Insert the following formula:

• • • This formula converts the time value in cell B5 to total minutes.

• • Drag the Fill Handle icon down to cell D9.

• Convert Time to Seconds

• • Choose cell E5.
  • Apply the formula below:

• • • This formula converts the time value in cell B5 to total seconds.

• • Drag the Fill Handle icon down to cell E9.

Read More: Convert Number to Military Time in Excel

Download Practice Workbook

You can download the practice workbook from here:

Things to Remember

• Save the workbook as macro-enabled since we’ve used VBA code.
• When converting hours, minutes, and seconds to time, apply the formula first and then format it as General.

Frequently Asked Questions

1. How do you convert numbers into time? To convert numbers into time format, we primarily use the “Format Cells” feature. Additionally, the TEXT function allows us to convert numbers into time format. The syntax for this is: =TEXT(A1, “h:mm AM/PM”).
2. How do I convert a number into hours and minutes in Excel? To convert numbers into hours and minutes:
   • Divide the number by 60 to determine the hours.
   • Take the remainder and use it as the number of minutes. For example, if the number is 135, the converted time would be 2:15, indicating 2 hours and 15 minutes.
3. How do I convert text to time in Excel? We can convert text to time in Excel using the TIMEVALUE function. This function takes the time_text (the text representation of the time) and returns a decimal number representing the time.

Convert Number to Time in Excel: Knowledge Hub

<< Go Back to Time Conversion | Date-Time in Excel | Learn Excel