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: