Convert Number to Time hhmmss in Excel: 2 Suitable Ways

Method 1 – Use TEXT Function to Convert Number to Time in hhmmss Format

Steps:

First and foremost, click on the C5 cell. Insert the following formula. Press the Enter button.

=TEXT(B5,"00\:00\:00")

Using TEXT Function to Convert Number to Time hhmmss

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

You will get all the time values for all the given numbers. The result should look like this.

Converted Numbers

Note:

The TEXT function returns the number in the exact format as given in the nd argument. 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.

Method 2 – Utilize Excel Multiplication Functionality

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:

Suppose you have numbers in hours value. You have to set the number format as Time.
Select all the outcome cells that are C5:C12 here. Right-click on your mouse. Choose the Format Cells option from the context menu.

Access the Format Cells Dialogue Box

The Format Cells window will appear. 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

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

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

See all your B column cell numbers are converted into time in hhmmss format, and the outcome should look like the following.

Converted Numbers

2.2 If the Number Means Minutes

Steps:

Follow the 2nd and 3rd steps from the previous way to set the format of the outcome cells as Time format.
Click on the C5 cell and insert the following formula. Press the Enter button.

=B5/1440

Formula to Convert Minutes Number Value to Time hhmmss

Convert the number of the B5 cell into time in hhmmsss format.
Place your cursor in the bottom right position of your cell. A black Fill Handle will appear. Drag it down to copy the formula for all the cells.

Drag Fill Handle Downward to Copy Same Formula

You will have all the numbers converted into time in hhmmss format. The conversion should look like this.

Converted Numbers

2.3 If the Number Means Seconds

Steps:

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.
Click on the C5 cell and write the following formula in the formula bar. Press the Enter button.

=B5/86400

Formula to Convert Seconds Value Number to Time hhmmss

Get the time in hhmmss for the B5 cell’s value.
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