Google Sheets: Quickly 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:

Google Sheets convert time to decimal

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

2 Replies to “Google Sheets: Quickly Convert Time to Decimal”

  1. This doesn’t work while trying to use times greater than 24 hours. It changes 25:00 to 1:00. But I’m trying to get 25. What can I do?

Leave a Reply

Your email address will not be published. Required fields are marked *