Convert Number to Time hhmmss in Excel (2 Suitable Ways)

Time calculation is a frequent need in Excel for several purposes. When importing a dataset or inputting data, it is very disturbing that we get the time values in decimal values. These values must be converted before further calculation and before representing the data for visualization. Else, there might be a ton of misinterpretation. In this regard, you have come to the perfect place. In this article, I will show you 2 suitable ways to convert number to time in hhmmss format in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


2 Ways to Convert Number to Time hhmmss in Excel

Suppose, you have 8 numbers given as different dates. But, you need to convert these numbers to time in hhmmss format. Follow any of these ways to accomplish this.

Given Numbers to Convert to Time in hhmmss Format in Excel


1. Use TEXT Function to Convert Number to Time in hhmmss Format

You can use the TEXT function to convert a number into time in hhmmss format. Follow the steps below to do this.

📌 Steps:

  • First and foremost, click on the C5 cell. Subsequently, insert the following formula. Next, press the Enter button.
=TEXT(B5,"00\:00\:00")

Using TEXT Function to Convert Number to Time hhmmss

  • As a result, you will get the time value in hhmmss format for the B5 cell’s number. Following, place your cursor in the bottom right position of the cell. When the fill handle appears, drag it downward.

Drag Fill Handle Below to Copy Same Formula

Consequently, you will get all the time in hhmmss time values for all the given numbers. For example, the result should look like this.

Converted Numbers

Note:

Here, the TEXT function returns the number in the exact format as given in the nd argument. So, the first two digits can not form a number greater than 24, the next two digits should not form a number greater than 60, and the last two digits can’t form a number greater than 60. It would result in confusion and error.

Read More: How to Convert 4 Digit Number to Time in Excel (3 Methods)


2. Utilize Excel Multiplication Functionality

You can also use the Excel multiplication functionality for numbers in hours value or minutes value or seconds value.

2.1 If the Number Means Hours

If the numbers are in hours value, follow the steps below to convert these numbers into time in hhmmss format.

📌 Steps:

  • Now, suppose you have numbers in hours value. For doing this, you have to set the number format as Time.
  • At the very beginning, select all the outcome cells that are C5:C12 here. Afterward, right-click on your mouse. Subsequently, choose the Format Cells… option from the context menu.

Access the Format Cells Dialogue Box

  • As a result, the Format Cells window will appear. Subsequently, go to the Number tab >> choose Time from the Category: options >> choose the 13:30:55 option from the Type: options >> click on the OK button.

Change Number Format to Time

  • At this time, click on the C5 cell and put the following formula inside the formula bar, and press the Enter button.
=B5/24

Formula to Convert Number to Time hhmmss

  • As a result, the B5 cell number will convert into time into hhmmss format. Afterward, place your cursor in the bottom right position of this cell and drag the fill handle below upon its appearance.

Drag Fill Handle Below

Thus, you can see all your B column cell numbers are converted into time in hhmmss format. And, the outcome should look like the following.

Converted Numbers

Read More: Convert Number to Hours and Minutes in Excel (2 Easy Methods)


2.2 If the Number Means Minutes

Similarly, if you are given the numbers in minutes value, you can also convert them into time in hhmmss format. Go through the steps below to achieve this.

📌 Steps:

  • Initially, follow the 2nd and 3rd steps from the previous way to set the format of the outcome cells as Time format.
  • Afterward, click on the C5 cell and insert the following formula. Subsequently, press the Enter button.
=B5/1440

Formula to Convert Minutes Number Value to Time hhmmss

  • Consequently, you will convert the number of the B5 cell into time in hhmmsss format.
  • Afterward, place your cursor in the bottom right position of your cell. Subsequently, a black fill handle will appear. Following, drag it down to copy the formula for all the cells.

Drag Fill Handle Downward to Copy Same Formula

And finally, you will have all the numbers converted into time in hhmmss format. For instance, the conversion should look like this.

Converted Numbers

Read More: How to Convert Number to Minutes in Excel (2 Easy Methods)


2.3 If the Number Means Seconds

Just like the previous two ways, you can also convert the numbers in seconds value to time in hhmmss format by following the steps below.

📌 Steps:

  • First, you have to customize your number format for the outcome cells. Follow the steps 2 and 3 from the 2.1 section to accomplish this.
  • At this time, click on the C5 cell and write the following formula in the formula bar. Subsequently, press the Enter button.
=B5/86400

Formula to Convert Seconds Value Number to Time hhmmss

  • As a result, you will get the time in hhmmss for the B5 cell’s value.
  • Afterward, place your cursor in the bottom right position of your cell. Now, a black fill handle will appear. Drag it downward to copy the formula for all the cells below.

Drag Fill Handle Downward

Finally, you can see that you will have all numbers converted into time in hhmmss format and it looks like the following.

Converted Numbers

Read More: How to Convert Fraction into Hours and Minutes in Excel (3 Ways)


Conclusion

To sum up, in this article, I have shown you 2 easy and quick ways to convert a number into time in hhmmss format in Excel. I would suggest you go through the full article carefully and practice each of the ways on your own. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo