3 ways to convert time to decimal numbers in Excel and how to convert it back

How to convert time to decimal numbers in Excel

1. Use math

The simplest way to convert time to decimal in Excel is to multiply the original time value by the number of hours, minutes, and seconds in the day:

To convert time to hours, multiply the time by 24 (the number of hours in a day).
To convert time to minutes, multiply the time by 1440 (minutes in a day = 24 * 60).
To convert time to seconds, multiply the time by 86400 (number of seconds in a day = 24 * 60 * 60).

For example, we have the following time table:

In column B to calculate the time, use the formula B2=A2 *24, copy it to the remaining cells in column B, you will get the number of hours in decimal form.

Similarly, convert to minutes according to the formula: C2 =A2*24*60

Convert to seconds according to the formula: D2 =A2*24*60*60

2. Use the CONVERT function

Another way to perform time conversion to decimal is to use the CONVERT function

Function syntax: =CONVERT(number;from_unit;to_unit)

In there:

Number is the original number to be converted.
from_unit is the unit of the original number.
to_unit is the unit to convert.

Unit values:

'day' = if unit is day.
'hr' = unit is hour.
'mn' = unit is minute.
'sec' = unit is seconds.

With the above example, the default from_unit value is 'day', and you want to convert to hours, we have the formula:

=CONVERT(A2;'day';'hr')

To convert to minutes we have the formula:

=CONVERT(A2;'day';'mn') or: =CONVERT(B2;'hr';'mn')

To convert to seconds we have the formula:

=CONVERT(A2;'day';'sec') or =CONVERT(B2;'hr';'sec') or =CONVERT(C2;'mn';'sec')

3. Use the functions HOUR, MINUTE, SECOND

Finally, you can use a more complex formula. Export time units using the time functions HOUR, MINUTE, SECOND then use the summation operation.

Function syntax:

= HOUR(serial_number)
= MINUTE(serial_number)
= SECOND(serial_number)

In which: serial_number is the number to convert.

With the example above, you type the formula as follows:

B2 = HOUR (A2)
C2 = MINUTE (A2)
D2 = SECOND (A2)

To calculate the total number of hours in column E, the formula will be hours + minutes/60 + seconds/360

Formula: =B2+C2/60+D2/3600 . Copy the formula to the remaining rows in the column. You get the following results:

To calculate the total number of minutes in column F, the formula will be hours*60 + minutes + seconds/60

Formula: =B2*60+C2+D2/60 . Copy the formula to the remaining rows in the column. You get the following results:

To calculate the total number of seconds in column G, the formula will be hours*3600 + minutes*60 + seconds

Formula: =B2*3600+C2*60+D2 . Copy the formula to the remaining rows in the column. You get the following results:

Marvin Fry

Update 04 April 2024