Calculating the duration of time between two times is a pretty common task. From measuring how long a process took, to entering the hours you put into a project, to creating a timesheet. You will find it everywhere, so working out duration is a hand skill to have in Google Sheets.

However, there are some gotchas when calculating the duration of time. So it is worth a good looking into.

To get a feel of how calculating durations work out in the wild, you can check out the video below for a great example on how I use it to build a Project Task Google Sheets workbook.

Get your very own copy here: Project Task Manager.

In this tutorial, we will cover how to calculate the duration of time over a single day or multiple days and sum a list of durations.

I encourage you to follow along so you can create your own template copy of the Google Sheet and do a bit of practice and experimenting on your own.

Pretty much everything in this tutorial is beginner-friendly. Don’t be afraid to give it a go.

The total duration of time over a single day

Open up a new Google Sheet.

From A1:C1 add the following headers:

• Start time
• End time
• Duration

How do you want to represent hours and minutes in a day?

Hours and minutes in a day

While Google Sheets is pretty great a predicting what type of data you want to add in a cell, like a date, time, number or text, it is good practice to set the time by manually changing the formatting.

Using 24 hr time is pretty good practice when working in spreadsheets, however, you can also set times to am and pm.

Select both cells A2:B2 (We will do something different for the duration).  Go to Formatting > Number > Custom date and time. 

A pop-up window will appear.

In the example in the image above, we have selected 24 hour time. Note the hours and minutes have been added between a colon. You can tweak these if you wish to add spaces or characters. If you select the ‘1:30 pm’ example you will see the am/pm option as well.

Play around with these to see what combinations you can make and when you are done select the ’13:30′ 24 hour time example again and select Apply. 

Go back to your cels A2 and B2 and you can now add in a time of the day. If a user adds am or pm time, it will still be displayed as 24 hr time.

Duration in hours and minutes

While we are calculating the duration of elapsed time between two times in a single day we could use the same time formatting as our start and end times, however, it is not 100% correct. Plus it could cause problems when we want to get the duration over multiple days.

Let’s do things properly the first time and set our duration formatting for cell C2.

Select cell C2 and then click Formatting > Number > Custom date and time. again.

This time select the weird-looking time with a bunch of numbers (minutes) in front of the colon.

This indicates elapsed time, or duration and allows us to display hours greater than 24 hrs.

Calculating the duration

In cell A2, add your first time of the day. Then in cell B2 add another time after your A2 time.

For example, Start time (A2) = 9:45, End time (B2) = 14:20.

Select cell C2. 

To get the duration we want to subtract ‘End time’ from the ‘Start time’:

= B2 - A2 

The result is 4 hours and  35 minutes.

Validating Start and End times in Google Sheets

Sometimes you may need to have other users entering the start and end time data into your Google Sheet.

In this case, it is probably a good idea to add some data validation to guide the user into entering the correct data.

Here are two examples, one simple and one a little more complex.

The date data validation approach

In this example, we appropriate (maybe misappropriate) the date data validation to use on our times.

Select the cell or range that you want to validate your times.

In our example, this will be cells A2 and B2.

A dialogue box will appear.

1. Ensure the cell range is the one you selected.
2. Select the ‘Date’ criteria and make sure it is set to, ‘is valid date’.
3. I’m being strict here and rejecting input.
4. Select validation help and then…
5. … change the text to “Enter a valid time”.
6. Select Save.

Now go ahead and try adding a time. Yeap, it will allow a time, any time, and format it the way you set things up earlier. If you try and add in text or a number, it will reject the input.

The downside of this approach

The downside is that you can still add just a date here. The date will be formatted as a time, but it will still be displayed as your time formatting which will make things weird.

So maybe we should give our users a further helping hand…

Creating a dropdown menu of times

Another approach is to create a dropdown menu of times. That way users can select a time or start typing in a time and the appropriate one will come up for them to select. No worries here about adding errant dates like the previous example.

So how do we do this?

Create a separate ‘working’ Google Sheets tab called ‘Notes’. Here add the following formula:

=ARRAYFORMULA(SEQUENCE(60*24,1,0,1)*(1/(60*24)))

I go into details on explaining this formula and even how to create a time sequence over custom ranges and set steps in this tutorial:

https://yagisanatode.com/2021/01/05/how-to-create-a-time-sequence-in-google-sheets/

Okay. Time to fess up to a secret. In Google Sheets, times are not recorded like 11:14 am or 16:34. They are recorded as a value from 0 to 0.9999999999.

So when you enter the formula above you will get a list of values in that range.

Ugly, I know.

Select column A and go to Format > Number > Custom date and time again.

Format this list to correspond to your formatting in your data entry cells. In our example, we are using 24hr time.

Your list of times should look like this now:

Head back to your main Google Sheet tab and select all the ranges that you want to apply your time dropdown to.

For our example, that is A2 and B2.  Go to Data > Data validation.

1. Ensure your range is selected properly.
2. Set ‘Criteria’ to ‘List from a range’.
3. Click on the grid icon and then go to your ‘Notes’ sheet tab and select column A (your entire list of times over a whole day).
4. Make sure your dropdown list is checked.
5. We have set to reject input to ensure only times are added.
6. Check show validation help and …
7. Add the following text, ” Enter a valid time.”.
8. Click Save.

All done. You can go ahead and hide the ‘Notes’  sheet tab from your users.

Your users can now select from a list of times or type them in and get the list of helpful prompts.

Total duration of time over multiple days

We can use two approaches here. Both approaches have their pros and cons.

Date and time in one cell

Date and time can be handled easily in one cell with Google Sheets. All you need to do is add the date followed by the time in your start and end time cells.

While Google Sheet will help by predicting your cell format. It is good practice to set up your cell formatting by hand.

Enter in a sample date and time in one or all of the cells you want to format so you can see the changes live.

If you want to use the standard date-time format just go to Format > Number > Date and time. 

Alternatively, if you want to do some fine-tuning go to Format > Number > Custom date and time and make the changes.

Ensure that you have set up your duration cell formatting correctly (See above)

Now add your duration formula to your duration cell. In my example, A2 is the start time and date, and B2 and the end time and date:

= B2 - A2

In my example my cells look like this:

The downside

If you have other not so experienced users entering date and time data into your Google Sheet you may find that you will get quite a lot of errors with this approach (I know, I have tested this with a number of teams and they just don’t like putting date and times in the same cell).

Date and time in separate cells

In this approach, we will use 5 columns with the following headers:

• Start date
• Start time
• End date
• End time
• Duration

Go ahead and format your dates, times and durations.

Hint! You can hold down Ctrl (Cmd on Mac) and click cells you want to format before formatting them and they will all be formatted. 

The formula

To calculate the duration in this example, we need to sum the end date with the end time and the start date with the start time. Then we subtract the end date-time from the start date-time. Like this:

=( C2 + D2) - (A2 + B2)

So our example will look like this:

With this approach, you can now add date data validation (and date picker) and a time dropdown menu to make life easier for your users.

For me, this is definitely my preferred approach.

The downside

The downside of this approach is that it is one extra column that you are using and may cause a little clutter.

Sum the total duration of time from a list

I often need to calculate the total hours that I have worked on a project over a selected period.

Here is an example of a project task timesheet that I use for clients to give you an idea:

Note up in cell A6 that I have calculated the total.

Let’s look at an easier example:

Here you can see we simply used the SUM function on all the duration values in Column D.

Make sure you set the formatting of the cell to duration tool. You can do this quickly by selecting one of your duration values, select the Paint Format button and then select the total duration cell.

Coming up soon we will look at how to calculate your earnings based on an hourly rate in Google Sheets based on the duration data you have created. To get a notification of when this comes out subscribe (top-right sidebar).

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

~Yagi