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

Method 1 – Use the Format Cells Window

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

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

• 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.

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

• In the Format Cells window, navigate to the Number tab. Select Time as the category.
• Choose the desired time format (e.g., h:mm:ss for hours, minutes, and seconds).
• 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

• Click on cell C5 in your Excel worksheet.
• 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.

• 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

• 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 icon down to cell C9 to apply the formula to the entire range.

Method 4 – Running Excel VBA Code

• Navigate to the sheet where you want to perform the conversion.
• Press Alt+F11 to open the VBA Editor.

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

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

• Click on cell C5 in your Excel worksheet.
• 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.

• 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

• 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 icon down to cell C9 to apply the formula to the entire range.

• 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

• 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 icon down to cell C9 to apply the formula to the entire range.
• 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

• 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 icon down to cell C9 to apply the formula to the entire range.

• 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

• 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”).

• 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.

• 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