In Google Sheets, there is a special format that you can enter into the cells to represent time, which is called “Duration” format. Duration format gives you the ability to work with time / duration values in a variety of ways, where you can specify how many hours, minutes, and seconds that a given value / time represents.
You can also do things like adding times together and subtracting them, just like you can with dates. This is something that I used to do when I was combining multiple sources of data that displayed student activity time / attendance.
In this lesson I am going to show you how to enter duration values into your cells, how to format the cells in "Duration" format, and I'll also show you some basic ways to use duration calculations.
To enter durations and apply duration format in Google Sheets, follow these steps:
- Select a blank spreadsheet cell
- Type the number of hours, then type a colon (:)
- Type the number of minutes, then type a colon (:)
- Type the number of seconds, then press "Enter". The duration value will look like this: 1:30:15
- To convert an existing value to duration format, select the cell that contains the value, click the "More formats" menu, then click "Duration"
When you enter a duration into a spreadsheet cell by manually typing it, the spreadsheet will automatically display duration format. But numbers that have already been entered into the spreadsheet cell can also be converted into duration format, as mentioned in the steps above. Later in the lesson I'll go over how to convert numbers into duration format where the number represents the number of hours.
How to enter durations into the spreadsheet cells
When entering durations, use the standard format: hh:mm:ss (hours:minutes:seconds).
For example, entering "01:30:15" represents 1 hour, 30 minutes, and 15 seconds.
To enter a duration into a spreadsheet cell, enter a number that represents the number of hours, type of colon (:), enter a number that represents the number of minutes, type of colon (:), enter a number that represents the number of seconds, then press "Enter" on the keyboard. For a duration that represents 2 hours, 11 minutes, and 7 seconds, the duration value will look like this: 2:11:07
hh:mm:ss
07:30:15 = 7 Hours, 30 minutes, and 15 seconds
When the hour value is less than 10 hours
When you specify the number of hours when entering a duration, if you enter a value that is less than 10 / less than 2 digits, then Google Sheets will automatically display only one digit for the hour value.
7:30:15 = 07:30:15
The two durations shown above are the same value, but since the number of hours is less than 10, Google Sheets only needs to display one digit, and the first zero is not needed.
How to apply duration format to the cells
When you enter a duration into the spreadsheet cell as explained in the previous section, Google Sheets will automatically detect the duration format and will display the value that you enter as a duration, as desired.
However, if you have existing numbers in your spreadsheet cells that you want to convert to a duration, this can be done very easily by changing the cell format. This is useful when your durations have been accidentally converted into numbers, or when you import data with durations into cells that are formatted as numbers.
To convert a number to duration format, select the cell or cells that contain the numbers to be converted, then click the "More formats" menu on the top toolbar (Button says "123"), then click "Duration".
After doing this the values in your cells will be converted to duration format.
Duration with minutes and seconds (no hours)
To enter a duration that displays only minutes and seconds (without hours), simply enter a zero for the hours component of the duration value.
Type a zero, then type a colon (:), type the number of minutes, type of colon (:), then type the number of seconds.
Duration with seconds only (no hours or minutes)
To enter a duration that displays only seconds (without hours or minutes), simply enter a zero for the hours and minutes components of the duration value.
Type a zero, then type a colon (:), type another zero, type of colon (:), then type the number of seconds.
Adding and subtracting durations
Google Sheets allows you to perform arithmetic operations directly on duration-formatted cells. For example, if you enter the duration 1:30:00 into cell A1, and enter the duration 1:00:00 into cell A2, you can use the formula =A1+A2 to add the durations together, which would yield a result of 2:30:00.
If you have several duration values in a column, you can use the SUM function to quickly add up a large number of duration values, like this: =SUM(A1:A)
You can also subtract durations from each other, to find how much larger or smaller one duration is than another. For example, if you enter the duration 1:30:00 into cell A1, and enter the duration 1:00:00 into cell A2, you can use the formula =A1-A2 to subtract the durations from each other, which would yield a result of 0:30:00.
Converting decimal to duration:
In this section I am going to briefly go over a basic example of converting decimals to duration, where you can make it so that the number converts to hours in duration format. In other words, if you want the number 1.5 to convert to 1:30:00, you can do this by using a formula.
In a spreadsheet, each increment of the numerical value "1" is equal to 24 hours in duration format. This means that if you want to convert numbers in your spreadsheet to duration where the number represents the number of hours, then you simply divide by 24, like this: =A1/24.
If you want to learn more, check out this in-depth article on converting decimals to duration, which goes over several examples in detail.
Working with Negative Durations:
There may be situations where you encounter negative durations, and these are just like negative numbers. If you subtract one duration value from another duration value that is smaller, you will get a negative value.
For example, let's say that an employee has a goal of reaching 8 hours of training, but has only completed 6 hours of the training. If you wanted, you could subtract the six from the eight which would yield a positive two, and use this number to represent the number of hours remaining. Or you could subtract 8 from 6, which would yield an answer of negative 2, representing the number of hours remaining as a negative value. So 6:00:00 – 8:00:00 = -2:00:00.
If you need to enter a negative duration, simply type A minus symbol before entering the duration.
Or sometimes data reports will display durations with negative values such as when reporting time remaining, and if you want to convert these to a positive value simply multiply by negative 1, like this: =A1*-1
