Tanjim Reza
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.
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")
- 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.
Consequently, you will get all the time in hhmmss time values for all the given numbers. For example, the result should look like this.
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.
- 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.
- At this time, click on the C5 cell and put the following formula inside the formula bar, and press the Enter button.
=B5/24
- 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.
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.
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
- 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.
And finally, you will have all the numbers converted into time in hhmmss format. For instance, the conversion should look like this.
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
- 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.
Finally, you can see that you will have all numbers converted into time in hhmmss format and it looks like the following.
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!
