When you are dealing with time values and durations in Google Sheets, you will often need to convert between duration, number of hours, minutes, and seconds. This is something that I needed to do frequently when I was managing data and building dashboards for an online high school, since the reports that displayed the amount of time that each student worked in a variety of online curriculum each reported time in a different way.
In this lesson I am going to show you how to convert between duration format and numbers that represent hours/minutes/seconds, and I'll also show you how to convert between hours, minutes, and seconds without changing formats. This will allow you to convert time in every way that you might need.
We will start with how to make sure that the cells are in the appropriate format, and then we will go over performing the necessary calculations for the conversions.
Changing the cell format to "Duration" or "Number"
Before we make the calculations, let's go over how to change cells to duration format so that your duration values display in the correct format.
To make sure that the numbers display as plain numbers, and to make sure that the duration values display as a duration format, simply change the format of the cells as described below.
On the top toolbar, click the button that says “123″. This is the "More formats" menu.
Then choose "Number" to display values as numbers, or choose "Duration" to display values as durations like this 24:00:00
For each of the examples in this lesson, we will focus on the calculations that are necessary to convert, since we already went over how to change the cells to the appropriate format. So remember to change the cell format to display your values in the way that you want.
If you have performed the calculation and the number displayed in the cell is a plain number instead of a duration like you want, simply convert the cell to "Duration" format as described above. If there is a situation where you perform a calculation and the cell is displayed as a duration instead of a number like you want, then change the cell format to "Number" as described above.
Number of hours/minutes/seconds to duration
In this section I will show you how to convert back and forth between duration, and numbers that represent hours/minutes/seconds.
Converting duration to number of days (or number of days to duration)
To understand why we divide and multiply by certain numbers in the time conversions, let's start with the most basic example that displays the relationship between duration and plane numbers. In a spreadsheet, the number one is equivalent to one day, which is 24 hours (24:00:00).
1= 24:00:00
As you can see in the image below, cell A2 contains the number one, and cell B2 simply refers to cell A2 with this formula =A2. Then the format of cell B2 is changed to "Duration" format (instructions below). This simply shows you that the duration value 24:00:00 is equivalent to the number "1", and will be the basis for understanding which number to divide or multiply by in each conversion scenario / example in this lesson.
Since numbers in a spreadsheet are equivalent to one day (1 = day = 24 hours), if you are trying to convert duration to number of days or vice versa, there is no need to perform any calculations, and all that you need to do is convert the cell format.
To convert duration to number of days, select the cell that contains the duration, click the "More formats" menu on the top toolbar, and then click "Number".
To convert number of days to duration, select the cell that contains the number which represents the number of days, click the "More formats" menu on the top toolbar, and then click "Duration".
Convert hours to duration
To convert number of hours to duration, divide the number of hours by 24 (And convert the cell with the formula to "Duration" format").
In this example, the number "1" is entered into cell A2 (representing 1 hour), and the formula in cell B2 divides cell A2 by 24 to find the duration value that also displays 1 hour (1:00:00), like this: =A2/24.
24:00:00 is equal to "1", and so we divide by 24 to make numbers represent hours of duration (i.e. where 1:00:00 is equal to "1").
We divide by 24 because: There are 24 hours per day
=A2/24
As you can see in the image above, cell A2 contains the number "1", and cell B2 displays "1:00:00", meaning that the number of hours entered into cell A2 will display as hours of duration in cell B2. In other words we have converted from number of hours (entered as plain numbers), to duration.
Convert minutes to duration
To convert number of minutes to duration, divide the number of minutes by 1440 (And convert the cell with the formula to "Duration" format").
In this example, the number "60" is entered into cell A2 (representing 60 minutes / 1 hour), and the formula in cell B2 divides cell A2 by 1440 to find the duration value that also displays 60 minutes / 1 hour (1:00:00), like this: =A2/1440.
We divide by 1440 to make numbers represent minutes of duration (i.e. where 1:00:00 is equal to "60").
We divide by 1440 because: (24 hours per day x 60 minutes per hour = 1440 minutes per day)
=A2/1440
As you can see in the image above, cell A2 contains the number "60", and cell B2 displays "1:00:00", meaning that the number of minutes entered into cell A2 will display as minutes of duration in cell B2. In other words we have converted from number of minutes (entered as plain numbers), to duration.
Convert seconds to duration
To convert number of seconds to duration, divide the number of seconds by 86400 (And convert the cell with the formula to "Duration" format").
In this example, the number "60" is entered into cell A2 (representing 60 seconds / 1 minute), and the formula in cell B2 divides cell A2 by 86400 to find the duration value that also displays 60 seconds / 1 minute (0:01:00), like this: =A2/86400.
We divide by 86400 to make numbers represent seconds of duration (i.e. where 0:01:00 is equal to "60").
We divide by 86400 because: (24 hours per day x 60 minutes per hour x 60 seconds per minute= 86400 seconds per day)
=A2/86400
As you can see in the image above, cell A2 contains the number "60", and cell B2 displays "0:01:00", meaning that the number of seconds entered into cell A2 will display as seconds of duration in cell B2. In other words we have converted from number of seconds (entered as plain numbers), to duration.
Duration to number of hours/minutes/seconds
Now let's do the opposite of the previous examples, and convert from duration to number of hours/minutes/seconds. Now we will be multiplying by the same numbers that we were dividing by in the previous examples.
Convert duration to hours
To convert duration to number of hours, multiply the number of hours by 24.
In this example, the duration "1:00:00" is entered into cell A2 (representing 1 hour), and the formula in cell B2 multiples cell A2 by 24 to get the value that represents 1 hour as a number (1), like this: =A2*24.
We multiply by 24 to convert duration into hours represented by a plain number (i.e. where 1:00:00 is equal to "1").
We multiply by 24 because: There are 24 hours per day
=A2*24
As you can see in the image above, cell A2 contains the duration "1:00:00", and cell B2 displays "1", meaning that the duration entered into cell A2 will display as number of hours in cell B2. In other words we have converted from duration to number of hours (displayed as plain numbers).
Convert duration to minutes
To convert duration to number of minutes, multiply the number of minutes by 1440.
In this example, the duration "1:00:00" is entered into cell A2 (representing 60 minutes / 1 hour), and the formula in cell B2 multiples cell A2 by 1440 to get the value that represents 60 minutes / 1 hour as a number (60), like this: =A2*1440.
We multiply by 1440 to convert duration into minutes represented by a plain number (i.e. where 1:00:00 is equal to "60").
We multiply by 1440 because: (24 hours per day x 60 minutes per hour = 1440 minutes per day)
=A2*1440
As you can see in the image above, cell A2 contains the duration "1:00:00", and cell B2 displays "60", meaning that the duration entered into cell A2 will display as number of minutes in cell B2. In other words we have converted from duration to number of minutes (displayed as plain numbers).
Convert duration to seconds
To convert duration to number of seconds, multiply the number of seconds by 86400.
In this example, the duration "0:01:00" is entered into cell A2 (representing 60 seconds / 1 minute), and the formula in cell B2 multiples cell A2 by 86400 to get the value that represents 60 seconds / 1 minute as a number (60), like this: =A2*86400.
We multiply by 86400 to convert duration into seconds represented by a plain number (i.e. where 0:01:00 is equal to "60").
We multiply by 86400 because: (24 hours per day x 60 minutes per hour x 60 seconds per minute= 86400 seconds per day)
=A2*86400
As you can see in the image above, cell A2 contains the duration "0:01:00", and cell B2 displays "60", meaning that the duration entered into cell A2 will display as number of seconds in cell B2. In other words we have converted from duration to number of seconds (displayed as plain numbers).
Convert between hours, minutes, and seconds without changing cell format
Now let's go over how to convert between hours/minutes/seconds, when we are not converting from duration to numbers or vice versa.
For example, if we want to convert from seconds to minutes, we simply divide the original number (or duration) by 60. If the original duration is 0:01:00 (1 minute) and we want to convert from minutes to seconds while staying in duration format, we would divide by 60 which would give us a duration of 0:00:01 (1 second).
The same goes for when we want to convert from seconds to minutes by using numbers only. If the original number of minutes is "60", and we want to convert the seconds to minutes while staying in number format, we would divide by 60 which would give us the number "1", representing one minute.
Note that in the example images below, we are using plain numbers, but the calculations would be exactly the same if you are converting between hours/minutes/seconds while staying in duration format the whole time. As long as the original number and the calculated number are both in minutes or both in duration format, the calculations will work for either scenario.
Convert hours to minutes
To convert from hours to minutes, simply multiply the number of hours by 60.
We multiply by 60 because there are 60 minutes in an hour.
In this example, the number of hours is entered into cell A2 (1), and cell B2 multiplies cell A2 by 60, giving us the number of minutes in cell B2 (60).
=A2*60
Convert hours to seconds
To convert from hours to seconds, simply multiply the number of hours by 3600.
We multiply by 3600 because: (60 minutes per hour x 60 seconds per minute= 3600 seconds per hour)
In this example, the number of hours is entered into cell A2 (1), and cell B2 multiplies cell A2 by 3600, giving us the number of seconds in cell B2 (3600).
=A2*3600
Convert minutes to seconds
To convert from minutes to seconds, simply multiply the number of minutes by 60.
We multiply by 60 because there are 60 seconds in a minute.
In this example, the number of minutes is entered into cell A2 (1), and cell B2 multiplies cell A2 by 60, giving us the number of seconds in cell B2 (60).
=A2*60
Convert seconds to minutes
To convert from seconds to minutes, simply divide the number of seconds by 60.
We divide by 60 because there are 60 seconds in a minute.
In this example, the number of seconds is entered into cell A2 (60), and cell B2 divides cell A2 by 60, giving us the number of minutes in cell B2 (1).
=A2/60
Convert seconds to hours
To convert from seconds to hours, simply divide the number of seconds by 3600.
We divide by 3600 because: (60 minutes per hour x 60 seconds per minute= 3600 seconds per hour)
In this example, the number of seconds is entered into cell A2 (3600), and cell B2 divides cell A2 by 3600, giving us the number of hours in cell B2 (1).
=A2/3600
Convert minutes to hours
To convert from minutes to hours, simply divide the number of minutes by 60.
We divide by 60 because there are 60 minutes in an hour.
In this example, the number of minutes is entered into cell A2 (60), and cell B2 divides cell A2 by 60, giving us the number of hours in cell B2 (1).
=A2/60
Now you know all of the different ways to convert time/duration in Google Sheets!
