Convert decimal seconds to Excel time

Convert decimal seconds to Excel time

Generic formula 

=seconds/86400

Related formulas 

Convert decimal hours to Excel time

Convert decimal minutes to Excel time

Add decimal hours to time

Add decimal minutes to time

Calculate number of hours between two times

Explanation

To convert seconds in decimal format to a proper Excel time, pide 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 pide 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.

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.

Convert decimal seconds to Excel time

Generic formula 

=seconds/86400

Related formulas 

Convert decimal hours to Excel time

Convert decimal minutes to Excel time

Add decimal hours to time

Add decimal minutes to time

Calculate number of hours between two times

Explanation

To convert seconds in decimal format to a proper Excel time, pide 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 pide 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.

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.