If your dataset contains date and time values, there might be situations where you need to convert them to decimal numbers.
For example, you might want to convert the difference between the start and end time of a particular task, into a decimal number.
This can act as an indicator of the number of hours worked. Sometimes it is more intuitive to say that one worked for 4.25 hours, rather than 4:15 hours. It also helps if you need to subsequently use the difference value in other calculations.
As such, it is quite useful to know how to convert time values into decimal numbers representing the number of hours, minutes, or seconds.
In this tutorial we will show you two ways to convert time to decimal values in Google Sheets:
- Using Google Sheets time functions (HOUR, MINUTE & SECOND)
- Using simple multiplication with the TIMEVALUE function
This Article Covers:
Two Ways of Converting Time to Decimal in Google Sheets:
Here are two ways to convert time to decimals.
For each method, we will show you how to convert time values to the number of hours, minutes as well as seconds, so you can apply the method as you need to.
Method 1: Using Google Sheets Time functions (HOUR, MINUTE & SECOND) to Convert Time to Decimal
Google Sheets lets you use three-time functions, HOUR, MINUTE, and SECOND. Here’s what each of these functions does:
The HOUR Function
This function takes in a time value and returns the hour component of that value. For example, if you pass the time value “04:15:30” to this function, it will return 4, because there are 4 hours in the time. The syntax for the function is:
=HOUR(time)
Here, time is a time value or a reference to a cell containing a time value. Interestingly, you can also pass the numeric representation of the time and you will still get the correct number of hours with this function.
The MINUTE Function
This function takes in a time value and returns the minute component of that value. For example, if you pass the time value “04:15:30” to this function, it will return 15, because there are 15 minutes in the time. The syntax for the function is:
=MINUTE(time)
Here, time is a time value or a reference to a cell containing a time value. You can also pass the numeric representation of the time.
The SECOND Function
This function takes in a time value and returns the second component of that value. For example, if you pass the time value “04:15:30” to this function, it will return 30, because there are 30 seconds in the time. The syntax for the function is:
=SECOND(time)
Here, time is a time value or a reference to a cell containing a time value. You can also pass the numeric representation of the time.
Converting Time to Number of Hours
Once you have the number of hours, minutes, and seconds in a given time value, you can easily convert it to its decimal equivalent (in the number of hours) by using the following formula:
=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/3600
Here we are assuming that the time value you want to convert is in cell A2. So, applying this formula to the time value in cell A2 shown below will give the result 4.258333333
Explanation of the formula:
In the above formula, we want to convert the number of minutes (15 in our case) to the number of hours. Since 60 minutes make an hour, 15 minutes will make 15/60 th of an hour. In other words, it will make ¼ of an hour or 0.25 hours.
Similarly, 3600 seconds make an hour. So, 30 seconds will make 30/3600 th of an hour. In other words, it will make around 0.0083333 hours.
If we sum up all three values, we will get the total number of hours in the time value. So:
2 hours + 0.25 hours + 0.008333 hours = 4.258333333
Converting Time to Number of Minutes
To convert a time value to its decimal equivalent (in the number of minutes) you can use the following formula:
=HOUR(A2)*60 + MINUTE(A2) + SECOND(A2)/60
Here we are assuming that the time value you want to convert is in cell A2. So, applying this formula to the time value in cell A2 shown below will give the result 255.5.
Explanation of the formula:
In the above formula, we want to convert the number of hours (4 in our case) to the number of minutes. Since an hour has 60 minutes, 4 hours will make 4 * 60 minutes. In other words, it makes 240 minutes.
Similarly, 60 seconds make a minute. So, 30 seconds will make 30/60th of a minute. In other words, it makes around ½ a minute or 0.5 minutes.
If we sum up all three values, we will get the total number of minutes in the time value. So:
240 minutes + 15 minutes + 0.5 minutes = 255.5
Converting Time to Number of Seconds
To convert a time value to its decimal equivalent (in the number of seconds) you can use the following formula:
=HOUR(A2) * 3600 + MINUTE(A2) * 60 + SECOND(A2)
Here we are assuming that the time value you want to convert is in cell A2. So, applying this formula to the time value in cell A2 shown below will give the result 15330.
Explanation of the formula:
In the above formula, we want to convert the number of hours (4 in our case) to the number of seconds. Since an hour has 3600 seconds, 4 hours will make 4 * 3600 seconds. In other words, it will make 14,400 seconds.
Similarly, 60 seconds make a minute. So, 15 minutes will make 15 * 60 seconds. In other words, it will take around 900 seconds.
If we sum up all three values, we will get the total number of seconds in the time value. So:
14,400 seconds + 900 seconds + 30 seconds = 15330
Method 2: Using Simple Multiplication with TIMEVALUE to Convert Time to Decimal in Google Sheets
Another method uses simple multiplication of the time value with the number of hours, minutes, and/or seconds in a day. Time values in Google Sheets are stored internally as decimal values, where 24 hours are represented with the value, 1.0. Therefore to represent one hour, we use the value 1/24. So, to convert the time value to a decimal using this method, we need to first get hold of its internal value using the TIMEVALUE function.
The TIMEVALUE Function
This function takes in a time value and returns it as a fraction of a 24-hour day. For example, if you pass the time value “12:00:00” to this function, it will return 0.5 because 12 hours is half a day.
Similarly, if you pass the time value “06:16:00” to this function, it will return 0.2611111111, which is close to 0.25, because 6 hours 16 minutes is almost a quarter of a day.
The syntax for the function is:
=TIMEVALUE(time_string)
Here, time_string is a string that holds the time value or a reference to a cell containing a time value.
Converting Time to Number of Hours
The TIMEVALUE function automatically converts the given time value to a number. You can then use this number and simply multiply it with the total number of hours in a day. This will give you a decimal representation of the number of hours in the given time value.
The total number of hours in a day is 24, so we need to multiply the results of the TIMEVALUE function by 24. Here’s the formula to convert time value to its decimal equivalent (in the number of hours):
= TIMEVALUE(A3) * 24
Here we are assuming that the time value you want to convert is in cell A3. So, applying this formula to the time value in cell A3 shown below will give the result 4.258333333.
Explanation of the formula:
In the above formula, we want to convert the time value (04:15:30 in our case) to the number of hours.
Since the TIMEVALUE function gives a representation of the given time value as a fraction of a 24-hour day, we need to simply multiply the time value by 24 to get its correct decimal representation in hours.
Converting Time to Number of Minutes
The TIMEVALUE function automatically converts the given time value to a number.
You can then use this number and simply multiply it by the total number of minutes in a day. This will give you a decimal representation of the number of minutes in the given time value.
The total number of minutes in a day is 1440, so we need to multiply the results of the TIMEVALUE function by 1440. Here’s the formula to convert time value to its decimal equivalent (in the number of minutes):
= TIMEVALUE(A3) * 1440
Here we are assuming that the time value you want to convert is in cell A3. So, applying this formula to the time value in cell A3 shown below will give the result 255.5.
Explanation of the formula:
In the above formula, we want to convert the time value (04:15:30 in our case) to the number of minutes.
Since the TIMEVALUE function gives a representation of the given time value as a fraction of a 24-hour day, we need to multiply the time value by 24 * 60 = 1440 minutes to get its correct decimal representation in minutes (one day has 24*60 minutes).
Converting Time to Number of Seconds
The TIMEVALUE function automatically converts the given time value to a number. You can then use this number and simply multiply it by the total number of seconds in a day. This will give you a decimal representation of the number of seconds in the given time value.
The total number of seconds in a day is 86400, so we need to multiply the results of the TIMEVALUE function by 86400. Here’s the formula to convert time value to its decimal equivalent (in a number of seconds):
= TIMEVALUE(A3) * 86400
Here we are assuming that the time value you want to convert is in cell A3. So, applying this formula to the time value in cell A3 shown below will give the result 15330.
Explanation of the formula:
In the above formula, we want to convert the time value (04:15:30 in our case) to the number of seconds.
Since the TIMEVALUE function gives a representation of the given time value as a fraction of a 24-hour day, we need to multiply the time value by 24 * 60 * 60 = 86400 seconds to get its correct decimal representation in seconds (one day has 24*60 * 60 seconds).
These were two ways to convert a time value to its decimal equivalent in Google Sheets. Both methods are really easy to implement once you understand the math and logic behind them.
We hope our explanation and examples were clear and easy for you to understand.
Related:
