This context will cover many essential topics on converting numbers to time in Excel. We will walk you through applying format cells and using various functions. You will also learn to use VBA to convert number to time in Excel.
Converting numbers to time lets you track time by changing hours, minutes, or seconds into a time format. Converting numbers to time is particularly useful for scheduling, planning, and analyzing time-based data.
After reviewing the article, you will have valuable skills and knowledge to work with time-related data. So, let’s dive in.
Download Practice Workbook
You can download the practice workbook by clicking the link below.
How to Convert Number to Time Format in Excel
Method 1: Use Format Cells Window
Select cell C5 >> apply the formula below >> drag the Fill Handle icon to C9.
=B5/24
Select range C5:C9 >> press Ctrl+1 to open the Format Cells window.
Later, go to Number >> choose Time as category >> select intended Type >> click on OK.
As a result, the output will look like the below one.
Method 2: Use TEXT Function
Choose cell C5 >> insert the following formula >> drag the Fill Handle icon to C9.
=TEXT(B5/24,"hh:mm:ss AM/PM")
Method 3: Use TIME, LEFT, RIGHT and LEN Functions
Select the C5 cell >> apply the following formula >> drag the Fill Handle icon to C9.
=TIME(LEFT(B5,LEN(B5)-2),RIGHT(B5,2),)
If you try to convert an integer number to time, you may get an #VALUE error.
Method 4: Run Excel VBA Code
Choose the intended sheet >> press Alt+F11 to open VBA Editor.
Later, choose 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
Thus, the output will be the following.
How to Convert Numbers to Time Units in Excel
Select cell C5 >> insert the formula below >> drag the Fill Handle icon to C9.
=TEXT(B5/24,"[h] ""hours,"" m ""minutes, "" s ""seconds""")
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 the C5 cell >> input the following formula >> drag the Fill Handle icon to C9.
=B5/24
Later, select range C5:C9 >> go to Home >> extend the Down-arrow >> choose Time.
As a result, the output will look like the following.
If the Number means hours, you must divide by 24.
Read More: Convert Number to Hours and Minutes in Excel
Case 2: Convert Minutes to Time
Choose C5 >> apply the formula below >> drag the Fill Handle icon to C9.
=B5/1440
Next, select C5:C9 >> navigate to tab Home >> choose Time as number format.
Finally, the output will be below one.
If the Number means minutes, you must divide by 1440.
Case 3: Convert Seconds to Time
Select the C5 cell >> input the following formula >> drag the Fill Handle icon to C9.
=B5/86400
Next, select range C5:C9 >> go to Home >> type Time as number format.
Finally, we will see the output like the following.
If the Number means seconds, you must divide by 86400.
How to Convert Time to Hours, Minutes and Seconds in Excel
Choose cell C5 >> input the following formula >> drag the Fill Handle icon to C9.
=B5*24
Next, select the D5 cell >> insert the following formula >> drag the Fill Handle icon to D9.
=B5*1440
Now, choose E5 >> apply the formula below >> drag the Fill Handle icon to C9.
=B5*86400
To convert Time to decimals (Hours, Minutes, and Seconds), multiply each unit by 24 for hours, 1440 for minutes, and 86400 for seconds.
Read More: Convert Number to Military Time in Excel
Things to Remember
- You must save the workbook as macro-enabled, as we have used the VBA code.
- When converting Hours, Minutes and Seconds to Time, apply the formula first, then format it as General.
Frequently Asked Questions
- How do you convert numbers into time?
To convert numbers into time format, we mostly use Format Cells. The TEXT function also allows us to convert numbers into time format. The syntax 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. Then, take the remainder and use it as the number of minutes. For instance, 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. It takes time_text (the text representation of the time) and returns a decimal number representing the time.
Convert Number to Time in Excel: Knowledge Hub
Conclusion
At this point, you have learned to convert number to time in Excel. We tried to guide you through basic to advanced topics. You have found here various practical aspects related to converting numbers to time. Visit our ExcelDemy site to learn more about the multiple Excel usages. You can also submit your Excel problem to our ExcelDemy Forum.
<< Go Back to Time Conversion | Date-Time in Excel | Learn Excel