Table of Contents
Google Sheets provides a simple way to quickly convert time to decimal by using the multiplication formula. By taking the hour, multiplying it by 60 and then adding the minutes, you can convert any time value to its corresponding decimal value in minutes. This is especially useful for calculating time differences, such as when tracking hours worked or for payroll. You can also use the HOUR, MINUTE, and SECOND functions to extract the individual elements of time, making it even easier to convert time to decimal.
You can use the following formula to convert a time to a decimal in Google Sheets:
=HOUR(B2)+(MINUTE(B2)/60) + (SECOND(B2)/3600)
This particular formula converts the time in cell B2 to a decimal.
For example, if the time is 4:19:34 then the formula will convert this to 4.3261 so that the time is represented as 4.3261 hours.
The following example shows how to use this formula in practice.
Example: Convert Time to Decimal in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the number of hours, minutes, and seconds it took various athletes to finish a competition:
We can use the following formula to convert the durations into a decimal format:
=HOUR(B2)+(MINUTE(B2)/60) + (SECOND(B2)/3600)
We will type this formula into cell C2 and then click and drag the formula down to each remaining cell in column C:
The new Decimal column displays the time in the Duration column as a decimal.
For example:
- A duration of 4 hours, 13 minutes, and 0 seconds is converted to 4.2167 hours.
- A duration of 13 hours, 30 minutes, and 15 seconds is converted to 13.5041 hours.
- A duration of 2 hours, 15 minutes, and 0 seconds is converted to 2.25 hours.
And so on.
If you would instead like to display the durations in terms of number of days, you can divide the entire formula by 24:
=(HOUR(B2)+(MINUTE(B2)/60) + (SECOND(B2)/3600)) / 24
The following screenshot shows how to use this formula in practice:
The new Decimal column displays the time in the Duration column as a number of days.
For example:
- A duration of 4 hours, 13 minutes, and 0 seconds is converted to .1756 days.
- A duration of 13 hours, 30 minutes, and 15 seconds is converted to .5626 days.
- A duration of 2 hours, 15 minutes, and 0 seconds is converted to .09375 days.
And so on.
The following tutorials explain how to perform other common operations in Google Sheets: