I’m a programmer, and I always say that as long as we’ll have to work with time, timezones, and time conversions, we’ll never be unemployed!
Oftentimes there is a need to turn times into their equivalent decimals or vice versa, and this is certainly possible within Google Sheets, and I’ll help you do so in just a few steps. There are a few ways to do this, and I’ll show a few of them, using some built-in functions that are provided within Google Sheets.
Table of Contents
- Why do you need to convert time?
- Converting Time to seconds
- Converting Seconds to Minutes
- Converting Minutes to Hours
- Alternative Methods
Why do you need to convert time?
Because time has many different representations. The actual time of day can be represented using the 24-hour time format or the 12-hour one. Also, time can be represented as days, hours, minutes, seconds, and so on.
Google Spreadsheets are widely used to keep track of working and non-working time and being able to make a time calculation is for sure a great resource.
Converting Time to seconds
As a first step, we’ll convert a time from the hh:mm:ss (hours:minutes:seconds) format to seconds. After this first time conversion, we’ll convert seconds to minutes, minutes into hours, and so on.
1. Select the cell in your spreadsheet that contains the time value. For instance, B4 in the following example.
2. Go to Insert > Function > All > Hour at the top of your spreadsheet
You can repeat this search for each of the other time components (minutes and seconds) or you can enter them in manually. The formula should look like this to get seconds:
The formula we entered for seconds was:
=hour(A2) * 3600 + minute(A2) * 60 + second(A2)What we did here was to convert the time format into seconds by multiplying hours by 3600 seconds per hour and minutes by 60 seconds per hour.
Note that the cell format of the seconds, minutes, and hours cells should be set as numbers. If this is not your case, you can edit it through the Format -> Number menu.
Converting Seconds to Minutes
Now that we have our time converted into its smallest unit (seconds) we can convert our seconds into minutes, and minutes into hours. This time, it’s even easier to convert from seconds to minutes, because all we have to do is divide by 60, like so:
In fact, Google Sheets anticipates what we’re trying to do by refilling our cell with the correct conversion. There’s really no other explanation than magic, I guess.
Converting Minutes to Hours
In the D column, we take the final step of converting minutes into hours. Again, Google anticipates what we’re trying to do by prefilling the cell with the correct formula, which is to divide the minutes again by 60.
This article is part of our productivity tips for Google Sheets series. You can find them all on our Tips and tricks for Google Sheets page.
Alternative Methods
Google Sheets has some convenient time functions, HOUR, MINUTE, and SECOND, which come in handy when dealing with time formats. We used them while converting time into seconds, but let’s explain a little better how these functions work.
HOUR: Extracts the hour component from a time value.
MINUTE: Extracts the minute component from a time value.
SECOND: Extracts the hour component from a time value.
TIME: Converts a time format into its separate hour, minute, and second components
So, if you entered the value “04:30:18” into a cell and applied the HOUR formula, it would output “4”. The same concept applies to the MINUTE and SECOND functions.
To use these functions in a cell, enter:
=HOUR(time)Note that the first two numbers refer to the hours, the next two to the minutes, and the last pair refers to seconds.
Converting Time to Hours
Converting your hours, minutes, and seconds into their respective hour-based decimal components can be accomplished by using this formula:
=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/3600In this example, we’re converting a time in cell A2 to its decimal equivalent in hours. To do this we extract each of the three components, transform it into the decimal hours equivalent, and then sum up the components. Let’s see how we manage each component:
- HOUR(A2): This is the hour component, so it doesn’t need conversion.
- MINUTE(A2)/60: Each hour has 60 minutes, so we have to divide the number of minutes by 60 to get the fractional time equivalent in hours. For example, if we have 30 minutes, 30/60 is 0.5, half an hour.
- SECOND(A2)/3600: In an hour there are 60 minutes, and in a minute there are 60 seconds, so in an hour there are 60*60=3600 seconds. In this piece of the convert function, we take the seconds component, and by dividing it by 3600, we get the fractional time equivalent in hours. From our example above, we have 18 seconds, if we divide 18 by 3600 we get 0.005. So 18 seconds id 0.005 hours.
If we sum all these components like on our custom formula, we get 4+0.5+0.005 = 4.505. So 04:30:18 is equivalent to 4.505 hours.
Converting Time to Minutes
To convert your input time value into all minutes, you can use this custom formula:
=HOUR(A2)*60 + MINUTE(A2) + SECOND(A2)/60As you can see, we’re using a strategy similar to the previous example to create our convert function, but using different multipliers/dividers, as now we need the minute time and not hours.
Let’s analyze each component as we did in the previous example, using 4:30:18 as our demo time:
- HOUR(A2)*60: In an hour we have 60 minutes, so to get how many minutes we have in x hours we have to multiply x by 60. In our example, we have 4 hours, so this will give 4*60 = 240.
- MINUTE(A2): Minutes are our base component, so it doesn’t need conversion. In our example, this will remain 30.
- SECOND(A2)/60: In a minute we have 60 seconds, so by dividing the seconds by 60 we can get the minutes time equivalent. In our example this will be 18/60 = 0.3.
If we sum all the components, we get 240+30+0.3 = 270.3.
Using the TIMEVALUE function with a multiplication
With this multiplication method, we use an alternative, built-in TIMEVALUE function to convert HH:MM:SS time into its decimal equivalent.
The time values that you review can come from a variety of sources including the Google Spreadsheet mentioned in the first method (the one with the Hours, Minutes, and Seconds values listed) or several other time/date/event formats. The TIMEVALUE function would take an input like 12:00:00 and convert it to 0.5, which corresponds to 12 hours being 1/2 of a full day. So it directly converts a time to its equivalent fraction of a 24-hour day. Its syntax is the following:
=TIMEVALUE(time_string)Once we get the fraction of a day from this formula, we can convert this value to hour, minutes or seconds with just a multiplication.
If you wanted to then convert the output of this formula into hours, you could just multiply by 24 as there are 24 hours in a day.
= TIMEVALUE(cell) * 24Accordingly, to convert the TIMEVALUE output into just minutes, you have to multiply by 1440 as there are 24*60=1440 minutes in a day.
=TIMEVALUE(cell) * 1440And if you wanted to get just seconds, you have to multiply by 86400 as there are 24*60*60 = 86400 seconds in a day.
=TIMEVALUE(cell)*86400