Convert seconds to time

An excel formula to convert seconds to time

Sponsored Link

 Convert seconds to time
=seconds/86400

To convert seconds in decimal format to a proper Excel time, divide by 86400.

In the example shown, the formula in C5 is:

 
=B5/86400

To display the result as time, apply a time format.

How this formula works

In Excel, time is stored as days, or fractions of a day. 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.

Because B5 contains 3600 (representing 3600 seconds) the result is 3600/86400 = 0.0417, since 3600 secondsĀ = 1 hour, and 1/24 = 0.0417.

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.

Sponsored Link

0 votes. 0 / 5