To convert seconds in decimal format to a proper Excel time, divide by 86400. In the example shown, the formula in C6 is:
To display the result as time, apply a time format. Column D shows the same result formatted with [h]:mm.
How this formula works
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below:
Since there are 24 hours in a day, 60 minutes in each hour, and 60 seconds in each minute, you need to divide by 24 * 60 * 60 = 86400 in order to convert decimal seconds to a value that Excel will recognize as time. After dividing by 86400, you can apply a time format of your choice, or use the result in a math operation with other dates or times.
In the example, since B11 contains 43200 (representing 43200 seconds, or a half day) the result is 43200/86400 = 0.5. Once a time format like h:mm or [h]:mm is applied, Excel will display 12:00.
Displaying a time duration
To display hours that represent a duration longer than 24 hours, minutes in durations longer than 60 minutes, or seconds in durations over 60 seconds, you'll need to adjust the number format by adding square brackets.
[h]// for hours greater than 24[m]// for minutes greater than 60[s]// for seconds greater than 60
The brackets tell to Excel the time is a duration, and not a time of day.
Note: to use square brackets, you'll need to create and apply a custom number format. Select cells, then go to Format Cells (Control + 1) > Number > Custom.
To convert hours in decimal format to a value Excel recognizes as time, divide by 24. In the example shown the formula in C5 is: = B5 / 24 which returns 0.04167, the equivalent of 1 hours. Cell D6 shows the same result formatted as time, which...
To convert minutes in decimal format to a proper Excel time, divide by 1440. In the example shown, the formula in C6 is: = B6 / 1440 Because B6 contains 60 (representing 360 minutes) the result is 60/1440 = 0.04167, since 60 minutes = 1 hour = 1/24...
To add a given number of hours to a time, you can add hours divided by 24, or use the TIME function. In the example shown, the formula in D5 is: = B5 + ( C5 / 24 ) Note: make sure results are formatted as time. How this formula works Times in Excel...
To add a given number of minutes to a time, you can add minutes divided by 1440, or use the TIME function. In the example shown, the formula in D5 is: = B5 + ( C5 / 1440 ) Note: make sure results are formatted as time. How this formula works Times...
To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.