When working with time values in Google Sheets, there may be times when you want to convert time to decimals.
Working with decimals is often required when you need to make calculations in your spreadsheet with your time data.
In this tutorial, I will show you how to convert time to decimal in Google Sheets.
Table of Contents
Using HOUR, MINUTE, and SECOND Functions to Convert Time to Decimal
One way that we can convert a time to a decimal in our spreadsheet is to combine the HOUR, MINUTE, and SECOND functions together.
Here is what these functions do:
- HOUR: returns the hour portion of a time in numeric format
- MINUTE: returns the minute portion of a time in numeric format
- SECOND: returns the second portion of a time in numeric format
The syntax of these three functions is essentially the same:
=HOUR(time)
=MINUTE(time)
=SECOND(time)
- time – this is the time for which the specified portion will be returned. It has to be a reference to a cell containing a date/time, a function returning a date/time, or a number
Here is an example of how all three functions are used in a spreadsheet:
As you can see in the example above, the HOUR function returns the hour part of the specified time, the MINUTE function returns the minute part of the specified time and the SECOND function returns the seconds part of the specified time.
Once we understand how to use these functions, we can combine them to convert a time to a decimal.
This is the syntax of the formula to convert a time to a decimal:
=HOUR(time)+MINUTE(time)/60+SECOND(time)/3600
- time – this is the time that you want to be converted to a decimal. It has to be a reference to a cell containing a date/time, a function returning a date/time, or a number
Here is an example of how this formula will be used in a spreadsheet:
You can see in the example above that I have my time in cell A2, so my formula is set up to reference A2 to convert the time to a decimal.
The reason this formula works is because to convert a minute to a decimal you would need to divide the minutes by 60 since there are 60 minutes in an hour.
To convert seconds to a decimal, you will need to divide the seconds by 3600, since there are 3600 seconds in an hour.
This formula will take a time and do this calculation for you and add the results together to get your final decimal value.
Using TIMEVALUE to Convert Time to Decimal
Basics of the TIMEVALUE Function
Another option that we have is to use the TIMEVALUE function.
The TIMEVALUE function will take a time and convert it to a fraction of a 24-hour day that the time represents.
The syntax of the TIMEVALUE function is:
=TIMEVALUE(time_string)
- time_string – a string that holds the time
Here is an example of this function can be used:
As you can see in the example above, this function converts a time to the fraction of 24 hour day that the time represents.
So in the first example, 2:00 PM is the 14th hour in a day. 14/24 = .58333333. This is why this is the result when using this function.
Converting to Number of Hours
If you want to take a time and return a decimal that represents the number of hours of that time, then you would use this formula:
=TIMEVALUE(time_string) * 24
Because there are 24 hours in a day, we use TIMEVALUE and then multiply the result by 24.
Here is how this will look in a spreadsheet:
Converting to Number of Minutes
We can also use a very similar formula to convert a time to the number of minutes.
The formula will be very similar to the previous one. There are 1440 minutes in a day (60 x 24). So our formula will be:
=TIMEVALUE(time_string) * 1440
Converting to Number of Seconds
If you want to convert a time to number of seconds you will need to use the TIMEVALUE function and multiply the result by 86,400.
This is because there are 86,400 seconds in a day (24 x 60 x 60).
Here is the syntax:
=TIMEVALUE(time_string) * 86400
Closing Thoughts
As you can see there are a few ways to convert times to decimal values in your spreadsheet.
We recommend that you take the time to learn both ways because it is always useful to know more about Google Sheets.
However the TIMEVALUE function methods are much easier to learn, so if you just want something quick, learn that method
More Google Sheets Tutorials:
How to Calculate Time
Convert Timestamp to Date