In Google Sheets, duration values are simply normal numbers displayed in a special format, and if you want you can convert the duration into a decimal / number value. In this lesson I am going to show you how to change cell format to display durations as a decimal, and I will also show you several methods on how to convert a duration into hours (represented as numbers & decimals).
To convert durations to decimals in Google Sheets, follow these steps:
- Select the cell or cells that you want to convert to number / decimal format
- Click the "More formats" menu on the top toolbar
- Click "Number"
- If you want to convert duration to hours, multiply the number by 24
After following the steps above, the duration values will be converted to decimals (numbers).
This lesson focuses on converting durations to decimals, but you can click here if you want to do the opposite and convert decimals to durations.
Or if you want, click here to learn how to convert between duration, and seconds / minutes / hours.
Converting to number format
First let's start with the basic example of converting duration format to number format, without doing any mathematics. Then in the next example I will show you how to convert durations to decimals where the resulting number represents the number of hours.
Sometimes you may simply want to see what number value represents a certain duration, and you can do this easily by simply changing the cell format to "Number".
To convert cells from duration format to decimal / number format, select the cell or cells that contain the durations to be converted, click the "More formats" menu on the top toolbar, and then click "Number".
Converting durations to decimals with simple division (Duration to number of hours)
When converting from duration to decimals, many people will want the number / decimal to represent the number of hours that the duration displayed. For example, if the duration is 2:30:00 (2 hours and 30 minutes), and you want to convert it into the number 2.5 (as in 2.5 hours), simply multiply the number by 24 to convert durations to number of hours. Then change the cell format to "Number".
To convert durations into number of hours in Google Sheets, enter a formula like this into the cell that multiplies the number by 24 (=A1*24). Then change the cell format to "Number" (click the "More formats" menu and then click "Number").
In a spreadsheet, each numerical increment of "1" is equal to 1 day, or 24 hours. So 24:00:00 = 1
24:00:00 = 1 = 1 day
This means that if we multiply a duration by 24, and then convert it to a number, we will get the number of hours that the duration displayed, represented as a number / decimal value.
Duration * 24 = Number of hours
(Use an asterisk (*) to multiply in a spreadsheet)
Make sure that you convert the cell itself to "Number" format so that the value actually displays as a number / decimal.
As you can see in the image above, cell A2 contains the duration 1:30:00, and cell B2 multiplies cell A2 by 24. After cell B2 is converted to "Number" format, it displays 1.5 (1.5 hours), which is equivalent to an hour and a half or 1 hour and 30 minutes.
Converting durations to decimals with the HOUR, MINUTE and SECOND functions
Now let's go over another method that you can use to convert durations into decimal values (where the decimal represents the number of hours from the duration). This method is a little more complex, but at the same time it might make more intuitive sense to some people.
In this example we are going to use the HOUR, MINUTE and SECOND functions to convert durations into numbers / decimals. These functions will allow us to refer to each individual component of the duration value (hours, minutes, seconds) so that we can convert each individual component into number of hours. We add together the number values from each function / component, and then we will have a number / decimal that represents the number of hours that the duration value displayed.
Remember that the goal is to convert the duration into number of hours.
First, we enter the duration 1:30:30 into cell A2.
Then we refer to cell A2 with the HOUR function, which returns the hour component of a specific time, in numeric format, like this =HOUR(A2). This will give a result of "1" when referring to the duration 1:30:30, because there is 1 hour in the component that displays the number of hours. We do not need to divide in this case, like we do for the Minute / Second components, because we are converting to hours, and the HOUR function already displays the number of hours.
Then we type a plus sign (+), to add the MINUTE function, which returns the minute component of a specific time, in numeric format. We refer to cell A2, and then divide by 60, so that the number of minutes returned by the function are converted into hours (Since there are 60 minutes in an hour). At this point the formula will look like this: =HOUR(A2)+MINUTE(A2)/60
Then we type a plus sign (+), to add the SECOND function, which returns the second component of a specific time, in numeric format. We refer to cell A2, and then divide by 3600, so that the number of seconds returned by the function are converted into hours (Since there are 3600 seconds in an hour). The final formula will look like this: =HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/3600
=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/3600
As you can see in the image above, cell A2 contains the duration 1:30:30, and the formula in cell B2 results in the number 1.508 (decimal continues). The number 1.508 represents the number of hours for the duration 1:30:30 (1.5 hours and 30 seconds).
Converting durations to decimals with the SPLIT function
Now let's go over one more method that you can use to convert durations to decimals where the decimal represents the number of hours that the duration displayed.
In this example, we are going to use the SPLIT function to split the duration where the individual components of the duration (hours, minutes, seconds) are split into individual cells. This will allow us to control each individual component, so we can convert each component into hours, similar to the previous example but with a different method.
With the SPLIT function, we will tell Google Sheets to split the duration value in cell B1, and we will specify that we want it to split the value where there is a colon (:). Being that the components of the duration value are separated by colons, this will work perfectly to separate / split the hours, minutes, and seconds like we want. To do this, we will use the formula below.
=SPLIT(B1,":")
Then after splitting the duration into individual cells, we will use the formula below to refer to those individual cells that contain the hours, minutes, and seconds.
=B2+C2/60+D2/3600
Notice that just like in the previous example, we are dividing the minutes by 60 (C2/60) to convert minutes to hours, and we are dividing the seconds by 3600 (D2/3600) to convert seconds to hours. So we take the hour value and cell B2, then add it to the minutes (converted to hours) in cell C2, and then add it to the seconds (converted to hours) in cell D2.
As you can see in the image above, the SPLIT function in cell B2 splits the duration so that the hours are in cell B2, the minutes are in cell C2, and the seconds are in cell D2.
Then cell B3 displays the number 1.5, which represents 1.5 hours / 1:30:00. This was done by adding together the individual components that were each converted into number of hours.
Now you know how to convert durations to decimals in Google Sheets, in a variety of ways!
