In this guide, we’ll explore essential topics related to converting numerical values into time format within Microsoft Excel. Whether you’re tracking hours, minutes, or seconds, understanding how to convert numbers to time can be incredibly useful for scheduling, planning, and analyzing timebased data.
By the end of this article, you’ll gain valuable skills and knowledge to effectively work with timerelated data. Let’s get started!
Download Practice Workbook
You can download the practice workbook from here:
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:
=B5/24

 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 bottomright 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 rightclick 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:
=TEXT(B5/24,"hh:mm:ss AM/PM")

 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:
=TIME(LEFT(B5,LEN(B5)2),RIGHT(B5,2),)

 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.
If you try to convert an integer number to time, you may get an #VALUE error.
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:
=TEXT(B5/24,"[h] ""hours,"" m ""minutes, "" s ""seconds""")

 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.
 Input the Following Formula:
=B5/24

 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 Downarrow, and choose Time as the number format.

 The resulting output will appear as follows:
If the Number represents hours, you must divide by 24.
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:
=B5/1440

 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:
If the Number represents minutes, you must divide by 1440.
Case 3: Convert Seconds to Time
 Select Cell C5: Click on cell C5.
 Input the Following Formula:
=B5/86400

 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:
If the Number represents seconds, you must divide by 86400.
How to Convert Time to Hours, Minutes and Seconds in Excel
 Convert Time to Hours:
 Choose cell C5.
 Input the following formula:
=B5*24


 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:
=B5*1440


 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:
=B5*86400


 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
Things to Remember
 Save the workbook as macroenabled 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  DateTime in Excel  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!