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

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 time-based data.

By the end of this article, you’ll gain valuable skills and knowledge to effectively work with time-related data. Let’s get started!

Convert number to time in Excel using TEXT function

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

Apply formula before using Format Cells Window

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

Use shortcut to open Format Cells Window

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

Format Cells Window to Convert Number to Time in Excel

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.

Output of using Format Cells Window


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.

Use TEXT Function to convert number to time in excel


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.

Use TIME LEFT RIGHT and LEN Function to convert number to time in excel

Notes

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.

Use shortcut to open VBE

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

Insert intended code in VBE to convert number to time

    • The resulting output will appear as follows:

Output of using Excel VBA


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.

Using TEXT Function to convert number to time units

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.

Insert formula to convert hours to time

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

Convert hours to time from the Number group in the Home tab

    • The resulting output will appear as follows:

Output of converting hours to time

Notes

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.

Insert formula to convert minutes to time

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

Convert minutes to time from the Number group in the Home tab

    • The resulting output will appear as follows:

Output of converting minutes to time

Notes

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.

Insert formula to convert seconds to time

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

Convert seconds to time from the Number group in the Home tab

  • The resulting output will appear as follows:

Output of converting seconds to time

Notes

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.

Convert time to hours in excel

    • 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 minutes in excel

 

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

Convert time to seconds in excel

Read More: Convert Number to Military Time in Excel

 

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

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo