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 you're working on some projects, it is usually important to control how much time you spend. This is called elapsed time. Google Sheets can help you calculate the time difference in a lot of various ways.

Example 1. Subtract time to get the time duration in Google Sheets

If you have your start time and end time, it's not a problem to find out the time spent:

= End time - Start time

Let's assume the start time is in column A and the end time is in column B. With a simple subtraction formula in C2, you will find how much time this or that task took:

=B2-A2 Time duration in hours and minutes in Google Sheets.

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

To get the results as hours only or as hours, minutes, and seconds, you need to apply a custom format with the corresponding time codes: h and hh:mm:ss. Google even offers a special number format for cases like this - Duration: Elapsed time in Google Sheets.

Tip. To apply the custom time format, go to Format > Number > Custom number format in your spreadsheet menu.

Example 2. Calculate time duration in Google Sheets using the TEXT function

Another trick to calculate the time duration in Google Sheets involves the TEXT function:

=TEXT(B2-A2,"h") - for hours

=TEXT(B2-A2,"h:mm") - for hours and minutes

=TEXT(B2-A2,"h:mm:ss") - for hours, minutes, and seconds Textual time difference.

Note. See how the records are aligned to the left? Because the TEXT function always returns the results formatted as a text. This means these values cannot be used for further calculations.

Example 3. Time difference in hours, minutes, and seconds

You can track the time spent and get the result in one time unit disregarding other units. For example, count the number of only hours, only minutes, or only seconds.

Note. To ensure correct results, your cells should be formatted either as numbers or automatically: Format > Number > Number or Format > Number > Automatic.

  • To get the number of hours spent, subtract your start time from the end time and multiply the result by 24 (since there are 24 hours in one day):

    =(End time - Start time) * 24

    You will get a time difference as a decimal: See the time spent in hours.

    If the start time is greater than the end time, the formula will return a negative number, like in C5 in my example.

    Tip. The INT function will let you see the number of complete hours spent since it rounds numbers down to the nearest integer:

    Count complete hours between two dates.

  • To count minutes, subtract the start time from the end time and multiply whatever you get by 1,440 (since there are 1,440 minutes in one day):

    =(End time - Start time) * 1440
    Calculate the number of minutes between dates.

  • To find out how many seconds passed between two times, the drill is the same: subtract the start time from the end time and multiply the result by 86,400 (the number of seconds in a day):

    =(End time - Start time) * 86400
    Get the time difference in seconds.

Tip. You can avoid multiplying in all these cases. Just subtract times first, and then apply elapsed time format from Format > Number > Custom date and time. If you click the down arrow to the right of the text field, you'll be able to choose between additional date and time units:

Duration formats in Google Sheets - elapsed time units.

Example 4. Functions to get the time difference in a Google spreadsheet

As always, Google Sheets equips you with three particularly useful functions for this purpose.

Note. These functions work only within 24 hours and 60 minutes and seconds. If the time difference exceeds these limits, the formulas will return errors.

  • =HOUR(B2-A2) - to return hours only (without minutes and seconds)
  • =MINUTE(B2-A2) - to return minutes only (without hours and seconds)
  • =SECOND(B2-A2) - to return seconds only (without hours and minutes)
Special functions to calculate hours, minutes, or seconds only.

How to add and subtract time in Google Sheets: hours, minutes, or seconds

These operations can also be achieved with two techniques: one involves basic math calculations, another - functions. While the first way always works, the second one with functions works only when you add or subtract units less than 24 hours, or 60 minutes, or 60 seconds.

Add or subtract hours in Google Sheets

  • Add less than 24 hours:

    =Start time + TIME(N hours, 0, 0)

    Here's how the formula looks on real data:

    =A2+TIME(3,0,0) Add 3 hours using the TIME function.

  • Add more than 24 hours:

    =Start time + (N hours / 24)

    To add 27 hours to the time in A2, I use this formula:

    =A2+(27/24) Add 27 hours with an arithmetic formula.

  • To subtract 24 and more hours, use the formulas above as a basis but change the plus sign (+) to the minus sign (-). Here's what I've got:

    =A2-TIME(3,0,0) - to subtract 3 hours

    =A2-(27/24) - to subtract 27 hours

Add or subtract minutes in Google Sheets

The principle of manipulating minutes is the same as with the hours.

  • There's the TIME function that adds and subtracts up to 60 minutes:

    =Start time + TIME(0, N minutes, 0)

    If you are to add 40 minutes, you can do it like this:

    =A2+TIME(0,40,0)

    If you are to subtract 20 minutes, here's the formula to use:

    =A2-TIME(0,40,0) Add and subtract 40 minutes with the TIME function.

  • And there's a formula based on simple arithmetic to add and subtract over 60 minutes:

    =Start time + (N minutes / 1440)

    Thus, here's how you add 120 minutes:

    =A2+(120/1440)

    Put the minus instead of plus to subtract 120 minutes:

    =A2-(120/1440) Arithmetic formula to add and subtract 120 minutes.

Add or subtract seconds in Google Sheets

Seconds in Google Sheets are calculated in the same manner as hours and minutes.

  • You can use the TIME function to add or subtract up to 60 seconds:

    =Start time + TIME(0, 0, N seconds)

    For example, add 30 seconds:

    =A2+TIME(0,0,30)

    Or subtract 30 seconds:

    =A2-TIME(0,0,30)

  • To calculate over 60 seconds, use simple maths:

    =Start time + (N seconds / 86400)

    Add 700 seconds:

    =A2+(700/86400)

    Or subtract 700 seconds:

    =A2-(700/86400)

How to sum time in Google Sheets

To find the 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.

By default, the result will be formatted as Duration - hh:mm:ss Sum duration.

But most often the default time or duration format won't be enough, and you will need to come up with your own one. Create a custom format for your total time in Google Sheets.

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

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

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

Split Date time using Number format

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

However, if you'd like to use these values for future calculations (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 you do?

Split Date time using formulas

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

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

    =ROUNDDOWN(A2,0) 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 C2:

    =A2-B2

  3. Copy the results into the third row and apply Date format to B3 and Time format to C3: Date and time formatted as Date and Time.

Use the Split Date & Time add-on

You may be surprised but there's one special add-on for this job. It's really small and easy but its contribution to Google Sheets cannot be overstated.

Split Date & Time splits all Date time records in your entire column at once. You control the desired outcome with just 4 simple settings: Split Date & Time add-on.

You tell the add-on:

  1. Whether there's a header row.
  2. If you want to get the Date unit.
  3. If you want to get the Time unit.
  4. And if you'd like to replace your original column with the new data.

It literally takes the burden of splitting date and time units off your shoulders: Split Date & Time result.

The add-on is part of the Power Tools collection so you will have more than 40 other useful add-ons at hand:

Google Workspace Marketplace badge

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

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

Spreadsheet with formula examples

Calculating time in Google Sheets (make yourself a copy to practice)

Table of contents

393 comments

  1. I have a number which is 16.80
    so since there is no 0.80 in minutes, it has to be converted to 0.70+0.20 so time becomes 17.20
    Want to represent the time as 17.20
    How do I do this

  2. I'm having an issue where the formula =(f2-e2,"h:mm") is returning times that are off by a minute. For example cell e2 is 01:18 and cell f2 is 03:38. The above formula completes as 02:19 when it should be 02:20. This is happening on a significant portion of data I am trying to create.

    • Hello Jason,

      You formula doesn't work on my side at all (formula parse error). However a simple =F2-E2 returns 02:20.
      For me to be able to help you, please share an editable copy of your spreadsheet with the formula that returns 02:19 with me: support@apps4gs.com. I'll look into it.

  3. =E10-D10+G10-F10+I10-H10+K10-J10+M10-L10+O10-N10+Q10-P10

    I am trying to calculate wages and I need the spreadsheet to multiply hourly pay by hour worked for multiple employees. Specifically I need a specific cell to multiply and add 6 cells together across 3 rows and it is returning decimal numbers and that cell is affecting another cell i have that is supposed to divide that information with another cell and because i am getting decimal numbers it is throwing off the end result that i am looking for.

    • Hello Adam,

      I'm sorry but your question is not clear.

      For me to be able to help you, please share a small sample spreadsheet with me (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      Thank you.

  4. Hi, I have a sheet to work out staff hours and when time worked is over 24 hours for example 33:20:00 the formula i use to covnert this into a decimal amount of hours does not work and give me the amount of hours worked over 24 hours. I am using this formula below:

    =(HOUR(E59)+MINUTE(E59)/70+SECOND(E59)/3600). I have tried changing the data type of the cell to [hh]:mm:ss but this still doesnt work. Do you have any suggestions?

    Thank you!

    • Hi Declan,

      33:20:00 already shows the duration of hours (33). You can either multiply this by 24 (see Example 3. Time difference in hours, minutes, and seconds; make sure the resulting cell is formatted as Number) or change the time format into elapsed hours only.

  5. Hi, thank you for this info.
    Just FYI, I think there is a typo in the extract the date/time section. Formula 2, says A2=B2, and I to match the image, it should say A2-B2.

  6. Hello,

    How would I check to see if a time is within a time range when the range goes past midnight? (i.e: The time to check is 03:00 and the time range is (21:00 - 5:00). Any help will be appreciated, thanks!

      • Hello Natalia,

        The range is in one cell (i.e 5:00pm - 5:00am). I can parse out the separate times, but I can't get it to realize that a time (i.e 3:00am) is in between the range since the time value is lower than the beginning of the range.

        If it makes it easier to have each time in it's own cell, I can create helper columns (split) to separate them.

        • Hello Jay,

          Assuming that 3:00am is in A2, 5:00pm – in B2, and 5:00am – in C2, the following formula will return TRUE if the time falls within the range, otherwise, you'll see FALSE:
          =OR(AND(B2>A2,C2>A2),AND(B2>A2,C2>A2))

  7. Hi Natalia, I'm sorry if this has already been asked/answered. I wondered if there was a way to add 2 seconds to a time formatted in a 00:00:00 AM/PM format. Ex( start time is 10:05:03 AM, and I want the end time to be 10:05:05 AM) I am trying to get a formula to add markers for video/audio edits quickly. This has been rattling around in my head for a while now, and I can't seem to figure it out! I would be super grateful if you knew how to accomplish this!

  8. Hello! I am having difficulty doing a duration countdown. I have cells with a date & time something is due (call it A1) and another cell with the NOW() function in it (call it B1) , those both work. When I do B1-A1 it returns a duration which counts down, however it's not the correct time. I read that google uses a different reference year and add a +1 at the end which worked for some, but as they get closer to less than 24 hours they then add 31 days on the front. For one that has 17 hours 33 mins remaining it states 31 days 17 hours 33 mins remaining.

    Any ideas?
    best,
    mattkwi

    • Hello mattkwi,

      Can you please specify the exact date time from A1 and the result your B1-A1 returns so I could understand your case better?

  9. Hello, I'm trying to fix so that i can have name down in A, Times in minutes, seconds, and milliseconds in B, and the difference between them in C

    Example
    A B C
    Name Time Difference
    Rodney 1m:25s:754ms 0.000
    Peter 1m:26s:516ms ??????

    please help! :D Thank you :D

  10. Is it possible in Google Sheets to subtract 2 durations that are not linked to an AM/PM time? I am a swimming coach and I am hoping to use Sheets to calculate the difference between two race times. For example, I have a swimmer who swims the 50 yard butterfly. Her current time is 1:05.39 (mm, ss , ms) and her goal time is 35.83 (mm, ss, ms). Is there a formula I can use for this? One of the challenges is that I am subtracting different values (minutes and seconds capping out at 60 and then converting, milliseconds going all the way up to 100 before converting)

    Thanks for any feedback or guidance you can give!

    • Hello Else-Marie,

      For Google Sheets to understand the exact format of the entered values, try the following:

      1. Enter your time records as a full duration, including zeros for hours: 00:01:05.39 and 00:00:35.83
      2. Go to Format > Number > Custom number format, and apply the following format to such numbers: hh:mm:ss.000

      Then try calculate these durations, it should work.

      • Hi, I am also a coach that uses race times. I am having a very hard time as well.

        I have many athletes that have times in (mm, ss , ms) ONLY.

        I tried to use the format of hh:mm:ss.000 however it calculates to a 24 hour model. I am not using any hours at all in my splits for racing.

        I need something like this
        Athelte A- 1:04.75
        Goal time- 1:03.09
        I need the differences so it would be -1.66 so that my athlete knows that they need to drop 1 seconds and 66 mil seconds.

        Thanks for all your help

        • Hi Andrew,

          If setting the custom number format doesn't work for you, try the custom date and time format instead with the following:
          Elapsed minutes (01):Elapsed seconds (01).Miliseconds (01)

          Make sure to apply the same format to the resulting cells.

  11. Hi,

    I think I read most of the comments and unfortunately I cannot find a solution to my problem.

    I need to add 52 hours to a date (08/11/2021 12:02:36) my problem is that I want to exclude the weekends.

    In this case A1 = start date and my formula is =A1+time(52,0,0), I tried using =WORKDAY.INTL but it doesn't work.

    Any help would be great and I love the your work.

    • Hi Vytaute,

      Your formula to add 52 hours is incorrect. Here I mention the correct way (how to add more than 24 hours).

      And here's a formula to add those 52 hours ignoring the weekends:
      =IF(OR(WEEKDAY(A1+(52/24))=1,WEEKDAY(A1+(52/24))=7),IF(WEEKDAY(A1+(52/24))=1,A1+DAY(1)+(52/24),A1+DAY(2)+(52/24)),A1+(52/24))

      It adds 52 hours first and checks if the resulting date is Saturday or Sunday. If so, it adds 2 or 1 day correspondingly.

  12. Is it possible to calculate elapsed time in Sheets using a defined start date/time, and the CURRENT date/time as the end point? For example, if I specified a start date and time, is there a way for it to output the number of days, or preferably hours that have elapsed since then?

    • Hi BJ,

      You can use NOW() and TODAY() functions instead of the specified end date/time, but these functions are volatile meaning they update themselves each time you open the spreadsheet or make edits anywhere in the file. Hence, your result will constantly update based on today's date/time.

      But you can also use these functions to get the required results first and then turn them into values to stop further recalculations.

  13. I am banging my head about this. I have a daily datetime stamps of a typical day at work starting at "10/22/2021 08:30:00 A" and ending at "10/22/2021 04:15:00 P". How can I filter out employees based on certain times of the day? Say 7 out of 10 employees start at 10/22/2021 12:15:00 P and end at 10/22/2021 03:15:00 P. How can I filter out those employees?

    Any help is greatly appreciated.

  14. Hi! I've used the following formula to find the duration of daily writing sprints:

    =IF(INT(E3-D3)>0, INT(E3-D3) & " days, ","") & IF(HOUR(E3-D3)>0, HOUR(E3-D3) & " hour(s), ","") & IF(MINUTE(E3-D3)>0, MINUTE(E3-D3) & " minutes ","") & IF(SECOND(E3-D3)>0, SECOND(E3-D3) & " seconds","")

    Now I want the sum of the duration times to find the total for the week. How can I do this?

    Once I do that, I'd like to sum across multiple pages/sheets within the document so I can find the total for the month. Is this possible?

    • Hi Liz,

      For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance and often gives a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows. I'll look into it.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment. I'll look into it.

      • I have shared my spreadsheet example with you as requested.

        • Hi Liz,

          I'm sorry for the delay in my reply, I was on sick leave. Please let me know if you're still looking for a solution, I'll look into your file then.

  15. You must get over 800 things

  16. Hi,
    I've got a starting date and a number of shift, If i sum the starting date and the shift it will find a delviery date, but I would love to avoid Holidays and week ends.
    Could you help me?

  17. Hi, is there a way to make it so it can calculate the time between two time frames if they're in the same cell?

    i.e

    8:00-13:00

    • Hi Ryan,

      Yes, RIGHT & LEFT + SEARCH will help you out:
      =RIGHT(A2, SEARCH("-", A2))-LEFT(A2, SEARCH("-", A2)-1)

      Don't forget to apply the Duration format to the result.

      • Thank you so much! I was starting to think that excel has skipped this part!!

  18. hey, I was just wondering how to calculate elapsed time from velocity and distance in google sheets.

  19. Hi!

    This is article is pretty helpful. Do you have any idea of how to calculate time within/outside of business hours? For instance, if Column A's data is 1/1/2021 5:20AM and Column B's data is 1/6/2021 2:30PM, I'd like the total time elapsed minus the time we're unavailable (I can provide the business hours - but in this example let's say we're completely closed Sat/Sun and are open from 10am-5pm Mon-Friday)?

    Any help would be greatly appreciated.

    Thanks

    • Hi AC,

      Thanks for stopping by our blog! :)

      Assuming your start time is in A2, end time – in B2, opening time – F2, closing time – G2, the following formula will do the trick:
      =(NETWORKDAYS(A2,B2)-1)*($G$2-$F$2)+MOD(B2,1)-MOD(A2,1)

      If there are any special holidays you'd like to take into account, you can set them right in the NETWORKDAYS function. You'll find a tutorial about it in this blog post.

  20. Hi! First I absolutely love this post and all of your kind responses. This has been so helpful to me.
    Second, I am trying to work out a formula I am hoping you can help with. I want a formula to calculate the duration of time (in decimal form such as 1.25 for an hour and 15 minutes) if the time start/end is entered manually. I can do this perfectly using your above suggestions IF I ENTER THE TIME IN MILITARY TIME ONLY.

    I want this to calculate the exact same way by being able to enter the time as you normally such as start at 11:00 and end at 2:00 without it resulting in a negative number. Is there any way to do this?

    THANK YOU SO MUCH IN ADVANCE!

    • Hi Cait,
      Appreciate your lovely feedback!

      I described how to calculate the time difference in decimals in the 3rd example here.

      However, if you use timestamps like this: start at 11:00 and end at 02:00, your spreadsheet won't understand that they belong to different days. You need to add days as well (19/08/2021 02:00:00) and then format cells to display the timestamp only. Then the time difference will be calculated correctly (a positive number).

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)