Calculating time in Google Sheets: subtract, sum and extract date and time units

Now, that we've learnt how to enter dates and time to your spreadsheet, it's time to talk about the ways of calculating time in Google Sheets. We'll discuss the ways of finding time difference in detail, see how to sum dates and time together, and learn to display only date or time units and set them apart completely.

How to calculate time difference in Google Sheets

When we're working on some projects, it's important to control how much time we spend. Google Sheets can help calculate time difference.

In other words, if we have start time and end time in our table, it's not a problem to find out the time spent. Google even offers a special number format for cases like this - Duration.

Let's try and calculate the duration in various time units: hours, minutes, and days.

  1. We'll start with the easiest part. Input start time to column A and end time to column B. Using a simple subtraction formula in C2, we will find how much time this or that task took:

    Calculate duration in hours

    The time is formatted as "hh:mm" by default.

  2. We have a few options of getting the result in minutes.
    • Try putting the following formula into D2:


      The formula looks correct, but as soon as you copy it down the column, you'll see the #NUM! error.The thing is that we can calculate the difference in minutes only when both, start and end time, belong to one 24-hour period. If the 12:00 AM point is crossed (a new day begins), the results will be inconsistent.

      Our example shows the correct values in C2:C4, but we can't perform any arithmetic calculations with them since they are stored as negative time values. That's why we get an error when trying to get the result in minutes.

      So, I suggest using this method only when time difference is calculated from two units within 1 day.

    • Enter full date and time to A2:B4. The formulas remain the same in other cells:
      Calculate duration in minutes

      And again, we get the incorrect result. If the difference between time stamps exceeds 24 hours, you won't get the needed result.To avoid such failures, select column C and go to Format > Number > Duration. You will get a completely different result:
      Apply Duration format to see correct results

      Tip. Apply custom date and time format to column D as well. Otherwise, the results in minutes will also be incorrect. More on how to do that right below.

Sum time in Google spreadsheet

To find total time in your table in Google Sheets, you can use the SUM function. The trick here is to choose the correct format to display the result.

Most often the default time or duration format won't be enough, and you will need to come up with your own one.

To create a custom time format, select your cells and go to Format > Number > More Formats > More date and time formats. Choose a necessary format from the list or enter your own. If you click the down arrow in the right corner of the text field, you'll be able to choose between additional date and time units:
Create various custom date and time formats

C6:C9 cells contain the same time values. They are just displayed differently. And you can actually perform calculations with them: subtract, sum, convert to decimal, etc.

Note.If you subtract the latest time point from the earliest, you will get a negative value. That's not a problem for Google, since it recognizes and works with negative time. To make cells return negative time, set the Duration format or a custom one using elapsed time units:
Negative time in Google Sheets
Note.Google Sheets doesn't support negative day format and you won't be able to create one.

Extract date and time from the full "date-time" record

Let's say that one cell in Google Sheets contains both, date and time. We want to set them apart: extract only date to one cell and only time to another.

In order to display date or time in one cell on your screen or to print it, just select this cell, go to Format > Number and choose Date or Time.

However, if you'd like to do math with these values in the future (subtract, sum, etc.), this won't be enough. If you don't see the time unit in a cell, it doesn't necessarily mean that it's absent, and vice versa.

So, what do we do?

Google stores dates and time as numbers. For example, it sees the date "8/24/2017 11:40:03" as number 42971,4861458. The integer part represents the date, the fractional - time. So, our task is to separate integer from fractional.

  1. To extract date (integer part), use the ROUNDDOWN function in cell B2:

    ROUNDDOWN function to separate the units

    The formula rounds the value down and casts the fractional part away.

  2. To extract time, place the following subtraction formula into cell C2:


Copy the results into the third row and apply Date format to B3 and Time format to C3.

These are the ways to not only display dates or time only, but to separate them to different cells. And you can perform various calculations with them.

I hope these examples will help you solve your task when working with dates and time in Google Sheets.

You may also like:

9 Responses to "Calculating time in Google Sheets: subtract, sum and extract date and time units"

  1. Rachael says:

    Thank you! This just saved me so much time!

  2. G says:

    HI, When the "duration, h" column is added to get a total hours the formula SUM(E3:E100) returns less than 24 hours. It restarts the summation each time it hits 24 hours. So if the total hours worked is 53 hours and 45 minutes, the return is 5:45 which is incorrect. How can I correct that?

    • Doug says:

      How is the cell duration h formatted?
      The issue may be one of formatting the cell the way you want it to display.

  3. Ola says:


    Thanks for a super interesting topic. I'm trying to build a countdown displaying Days, Hours and Minutes and yours is the only subject I can find on the matter.

    Though I have some major problems getting it to work correctly and was wondering: Would you mind sharing your Sheet for us slow people to have a poke at?

    Thanks in advance and thanks for a super post!

  4. Dave Bacon says:

    Hi, I'm trying to sort out how to sum time for a time trial event on a racing game. There are several weeks being added together for a total combined time of all events. The issue I'm having is Sheets is rounding the time up. I need it to show exact times, so if a lap time is 1 minute 33 seconds and .742 so, 0:01:33.742, I need it to show that time in the cell, and to represent that time in the total time cell, which I've used the sum function for. Drivers' times are often too close for them to be rounded up.

    Any help appreciated! Thanks!

  5. Mike Wurlitzer says:

    Looking to set a series of times based upon a single "operator entered START time" and about 10 subsequent times where I know the number of minutes between each step.

    It would be quite possible these would go over midnight to the next day.

    Example: Start time = 11:00 AM
    Step 1 [15 min] = 11:15 AM
    Step 2 [20 min] = 11:35 AM
    Step 3 [4 hours] = 3:35 PM


  6. Peter says:

    Hi thank you for the above time calculation.

    There is one more help I need about the above formula. Is there a way to know the time spent on a particular task submitted per employee.

    Example : An employee used google form to submit a completed task. That showed 8/10/2018 6:30:00 timestamp, then claimed a new task which ended at 8/10/2018 7:15:00, then claimed another task which ended at 8/10/2018 7:35:00. Employees let’s say are required to complete 3 task per hour. In a situation where by one has more than hundreds of people using the form to submit their task completion per hour in one google tracker . What is the easiest way to calculate the hr spent by each employee and the completed task per hour. 2 is there a way to add a formula on a separate column that auto calculate hours spent by each employee when 3task is completed. ? The logic here is I would like to know the most product employee in an hour thanks.

  7. Ilham says:

    Halima’s slept at 2200h if she woke up at 0600h for how long had she slept.

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools -