3 Ways to Convert Time to Decimal and Vice Versa
3 ways to convert time to decimal and vice versa: Sometimes you need to convert times to decimals (and vice versa) to perform calculations in Excel. To convert time to hours, minutes, and seconds, you can use arithmetic operations to convert the time value to decimal, or use the appropriate functions to perform the conversion. Follow the article below for detailed instructions.
1. Using arithmetic operations
The simplest way to convert time to decimal in Excel is to multiply the original time value by the number of hours, minutes, or seconds in a day:
- To convert time to hours, multiply the time value by 24 (the number of hours in a day).
- To convert time to minutes, multiply it by 1440 (minutes in a day = 24 * 60).
- To convert time to seconds, multiply it by 86400 (seconds in a day = 24 * 60 * 60).
For example, suppose we have the following time table:
In column B, to calculate the hours, use the formula B2 = A2 * 24 and copy it to the rest of the cells in column B. You will get the number of decimal hours as the result.
Similarly, for minutes, use the formula C2 = A2 * 24 * 60.
For seconds, use the formula D2 = A2 * 24 * 60 * 60.
2. Using the CONVERT . function
Another way to convert time to decimal is to use the CONVERT function.
Function syntax: =CONVERT(number; from_unit; to_unit)
Where:
- Number is the original number to be converted.
- From_unit is the unit of the original number.
- To_unit is the unit for which the conversion is desired.
The values for the units are:
- “day” = if the unit is days.
- “hr” = if the unit is hours.
- “mn” = if the unit is minutes.
- “seconds” = if the unit is seconds.
In the above example, from_unit defaults to “date” and if you want to convert it to hours you can use the formula:
=CONVERT(A2; “date”; “hour”)
To convert it to minutes, use the formula:
=CONVERT(A2; “date”; “mn”) or =CONVERT(B2; “hour”; “mn”)
To convert it to seconds, use the formula:
=CONVERT(A2; “day”; “second”) or =CONVERT(B2; “hour”; “second”) or =CONVERT(C2; “mn”; “second”)
3. Using HOUR, MINUTE and SECOND . Functions
Finally, you can use more complex formulas. Extract time units using the HOUR, MINUTE, and SECOND time functions, then perform arithmetic operations to combine them.
Function syntax:
= HOUR(serial_number)
= MIN(serial_number)
= SECOND(serial_number)
Where: serial_number is the number to convert.
In the example above, enter the following formulas:
B2 = HOUR(A2)
C2 = MIN(A2)
D2 = SECOND(A2)
To calculate the total number of hours in column E, the formula would be hours + minutes/60 + seconds/3600.
Formula: =B2+C2/60+D2/3600. Copy the formula to the remaining cells in the column. You will get the result:
To calculate the total number of minutes in column F, the formula would be hours*60 + minutes + seconds/60.
Formula: =B2*60+C2+D2/60. Copy the formula to the remaining cells in the column. You get the following results:
3 Ways to Convert Time to Decimal and Vice Versa
In conclusion, the above article has guided you through three methods to convert time to hours, minutes, and seconds in Excel. We hope you find these instructions helpful. Good luck!
