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.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
How to Convert Timestamp to Date in Google Sheets
How to Sort by Date in Google Sheets
How to Compare Dates in Google Sheets
How to Add Months to Date in Google Sheets