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. Can I insert Live Countdown in Google Sheets?

    • Hello Rezu,

      Yes, but we don't have a tutorial on our website. If you Google a bit, you'll find plenty of instructions, even demo videos, along with templates to use.

  2. In my Spreadsheet, Whenever I input a data in a row Timestamp is auto added in this format 8/17/2021 20.05.31. I want that a countdown of 48 hours from the inputted Date and time. Can you help me? What would be the formula for subtracting 48 hour from the timestamp?

  3. Hello there!
    I´m trying to figure out how to get a timespan from a list of dates/times but cant seem to figure it out.
    For example:

    10/06/2020 19:28:55
    15/06/2020 02:39:43
    15/06/2020 06:30:04
    16/06/2020 23:50:49

    I want to see the elapsed timeframe between these different times.

    10/06/2020 19:28:55 - 4 days, 5 hours, 10 minutes, 52 seconds till below time
    15/06/2020 02:39:43 - 0 days, 3 hours, 50 minutes, 21 seconds, etc....
    15/06/2020 06:30:04 - etc.etc.

    Anyone know if this is possible and how i can make this appliable to a long list of dates and times?

      • how can I get the "days" to just show up as 24 hours - for example,
        Start time: 09/21/2022 08:00:00
        End time 09/22/2022 13:00:00 with formula =INT(J4-J3)&" days "&TEXT(J4-J3,"h"" hours ""m"" minutes ""s"" seconds""") it would display 1 days 5 hours 00 minutes 00 seconds....
        how can it show results as: 29 hours 00 minutes 00 seconds?

  4. Good night!

    My goal is to add up all the hours worked in a month in a single cell.

    That is, if I work:
    07/04: 4:30 hours
    5/07: 9 hours
    06/07: 15 hours
    07/10: 8 hours
    07/15: 3 hours
    07/20: 16 hours
    I would like to get a cell that sums everything (= 55:30 hours)

    I have tried with SUM and changing the format to "hours elapsed: minutes elapsed" or "hours: minutes" but I get absurd results (for example 2:30 or 26, never 55 and a half hours)

    Is there a simple way to solve this? The fewer cells you use, the better.

    • Ok, I found the reason why SUMA was not working for me.

      In the working range from 18h to 24h, the "24" interprets it as 00: 00h but NOT the 00: 00h at the end of the day and yes as 00: 00h at the beginning of the day (that is, -18 hours instead of +6 hours)

      I managed to change that and it already works fine for me.

    • hello,

      how you solved it ?
      what is formula of this problem ?
      please tell me

  5. Hi!
    I am trying to use a formula to determine hours worked in decimal form when we have shifts that often go past 12am. I need a way to do this where the outcome is not negative and can be all done in one formula, is that possible?

    For example:
    I have tried using both:
    =TEXT(B2-A2, "h.m")-0.3 to try and get hours worked minus break time but this will not convert to decimal
    = ((B2-A2)*24)-0.5 but this one causes negative numbers for any shifts that go past 12am

    • Hi Alexa,

      You can turn negative numbers into positive by embedding your formula into the ABS function.

      If this still doesn't solve your task, please consider sharing a short sample table with us – support@apps4gs.com. I'll look into it.
      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.

  6. hi can anyone help
    How To Convert Time To Decimal Over 24 Hours In Google sheet?
    =HOUR(A1)+MINUTE(A1)/60

  7. Hello,
    I am trying to create a google chart that automatically populates the 24 hour mark from the date and time I input in column A.

    Example:
    A
    6/17/2021 8:09 pm

    B- will automatically populate the date and time 24 hours later
    6/19/2021 8:09 pm

    Please assist

    • Hello Kim,

      Just make sure the Data range that is used by the chart includes all cells (even empty ones) that will be filled in future. For example, B2:B100. Once they are filled, they will be shown in the chart as well.

      If that's not really the problem, consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com).
      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.

      I'll look into it and try my best to help.

  8. if I want to multiply time?

    For example I have a base time of 0:08 minutes and I want to multiply it by for eaxmple "10", in which the result would be 1:20 hours?

    I've tried =0:08*10 but it gives me #ERROR!

    What should I do?

    • Hello Mike,

      You're getting the error because when you enter =0:08*10 that 0:08 is treated as text. This happens because Google Sheets doesn't see date and time the same way you see them. Please read about it here.

      For the calculation to work correctly, I'd advise you to enter 0:08 in a separate cell. This will let Google Sheets recognize the record (you'll see that the format in a cell will change to 00:08 while the formula bar will show 00:08:00). Then reference this cell in a formula from another cell: =A2*10
      Or use a formula like this: =TIME(0,8,0)*10

  9. Hi Natalia,

    First of all, thank you for the amazing article and responses in the comments!
    I'm evaluating the time it takes to do certain tasks, and I was able to build a table with the correct duration values. This table includes values over 24 hours of duration, for example, "25:04:00".

    In the table, everything works great, the conditional formatting for values over 24 hours works, and everything is perfect. However, I created a chart from this table, and the chart doesn't work as well.

    It's a combo chart with bars representing the duration over time, and lines to represent the objectives and targets. Taking the example above, instead of 25 hours, the bar represents only 1 hour. It resets at 24 hours.

    Even worse, another chart that is a copy of the first but feeds on different values, will reset values at 12 hours.

    I can't seem to find any setting to control this. Can you help me?

    • Hi Tari,

      Appreciate your kind feedback :)

      For me to be able to help you with the chart, please share an editable copy of your spreadsheet with your table and the chart with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data, just keep the formats and durations intact.

      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.

  10. I'm trying to tally up 2 different columns of times and determine the difference between them. Both sets of columns have an end time, subtracted by the start time to determine duration. I have the result of the formula formatted as "[h]:mm." Most of the time, the value is positive, more time was spent rather than what was estimated. However, there are some times when the actual hours worked were less than what was expected. I'm trying to keep a running tally of hours 'owed' as opposed to extra.

    I am working on an old computer with an old version of Excel. It doesn't have as many of the formatting possibilities as you have listed in your examples above. The formula works when it returns a positive time duration, but the negative time is expressed as "####" because it says that there is no 'negative' time? Is there a workaround for this? Do I need to convert the time value to "real" numbers, keep a running sum, then put them back into time format for a total number of hours minutes? Could I incorporate an if/then statement?

    I'm not as familiar with Goggle Sheets. I am going to be sharing this data with someone via Sheets and if there is a better/easier way to use it directly I can just avoid Excel altogether.

    Thanks for your "time!" ;)

    • Hello Susan,

      The instructions in this blog post are for Google Sheets. For Excel, you will find them in this article. There Svetlana mentioned how to make Excel display negative time. Hope this helps!

      But if you're okay working with Google Sheets, there's only one version of Sheets and it shows negative time by default. Feel free to calculate everything in Google Sheets and let me know if it doesn't work as you expect there.

  11. Hello, I am trying to add up all the time in one column and have it represented as dd:hh:mm but I just can't seem to do it. Im wondering if you could help. I will give an example directly from the sheet. Also, they are all represented as hh:mm. So c1, 0:41 c2, 0:53 c3, 2:22 c4, 3:28 c5, 9:17 c6, 6:49 c7, 1:56 c8, 3:25 c9, 2:43 c10, 6:28. Like asked how would I add the whole column with said outcome? (So if I update it time will change) Also, could these be averaged but with an outcome of hh:mm so I could find average hours per day? (Each c1, c2 ect is a day). Thank you so much this would be very appreciated.

    • Hello Jesse,

      I answered the same question just a few days ago, please have a look :) The same formula will work for you. If you have your SUM in C11 (showing 38:02 as a result), reference C11 in the formula from that comment of mine, and you'll get 1 day 14 hours 2 minutes.

      As for the average, it is usually calculated from a few values. I'm not sure how you want it to apply to each cell separately. Perhaps, you mean to round the values to full hours?

      • OH MY GOODNESS. Thank you this is awesome. ;)

      • Oh for the average just have it as it is hh:mm. I assume you naturally find the average then convert it with the formula you showed me. Hmmmm

        • Wait nevermind I figured out how to do the average.

  12. Sorry if this is a silly question. I want my time differences displayed as a decimal of an hour (1.5 hours instead of 1:30:00) How can I do this? Thanks!

  13. Hi Natalia,

    Firstly, thank you for the work you have done here, it has helped me a lot. However, I am stuck at the final hurdle.

    I am calculating the total duration spent across a month and need to display the result in dd:hh:mm. I have the hh:mm for the month summed, so it's just this final part of the puzzle that has me stuck. Currently, I have a calculation that sums to 29:09 (so 29 hours and 9 minutes) which is correct and when I try to format that into dd:hh:mm it returns 31:05:09, however, it should be 01:05:09 (1 day, 5 hours and 9 minutes).

    Hope you can help.

    Thanks
    John

    • Hi John,

      Thank you for your feedback!
      Please try the following formula instead (assuming your duration is in A2):
      =INT(A2)&":"&IF(Hour(A2)<10,"0"&Hour(A2),Hour(A2))&":"&IF(Minute(A2)<10,"0"&Minute(A2),MINUTE(A2))

      • That's great! Thank you!

  14. In regards to google sheets and changing the format. I imported a spreadsheet which has call log times for my employees. I am trying to standardize and find out the daily time they spend on the phones helping patients so I can track who is pulling the heaviest call volume. My report that exports from my phone company only puts the format as 10mins or 56 secs, is there a way or formula to convert that into the standard 00:00:00 timing format so that I can add up their total time? I am not seeing a way to do this and it is looking like this may be a manual process. Any help would be greatly appreciated.

    • Hello Mark,

      There is a formula for your task :)

      You can check if your cells contain 'mins' or 'secs' and extract the numbers already converted to minutes or seconds accordingly. Here's a formula:
      =IF(ISNUMBER(SEARCH("mins",A2)),TIME(0,LEFT(A2,SEARCH("mins",A2)-1),0),IF(ISNUMBER(SEARCH("secs",A2)),TIME(0,0,LEFT(A2,SEARCH("secs",A2)-1)),""))

      Paste this formula where you want to see the result, change A2 to a cell you're checking, and make sure to change the number format of the resulting cells to Duration.

  15. Hy! I need to help to solve problem. When employee atrandece time is greater than office time than cell is colour red . How can it possible plz

  16. I may be missing something, but I cannot find the solution to how to avoid a negative calculation of time when they're on two different days. I'm trying to create a very simple time sheet, but if someone has a start time of 11pm and end time of 4am this shows a total of -19 hours. How do we fix this within the formula? Thanks.

    • Hello Maria,

      Please look at Example 1 in this part of the blog post. There's a similar case in row 3 and the subtraction works. Also, make sure you format your result cell correctly.

      If you still can't get the required result, please tell me what you use as the start and end time exactly and specify your formula.

  17. Thank you very much for the guide!
    But I still experience an error that is testing my patient very hard...
    Basically when i put 13:00 in a cell (after applying the hh:mm format) google sheet doesn't recognize that as a number or time and it says that it is text...
    And no formulas works...
    Only way that i found is to imput the time using . instead of : but it's a bad workaround

    Anyone got this problem too?
    Thanks!

  18. hello All, I have tried with formula, But giving the result as below, Please suggest what's wrong.
    Eg : 05/06/2020 18:22:51 - 04/06/2020 16:42:24 = 721.6741667
    Please suggest how to solve this
    Thanks
    Kantharaju

  19. I've two cells formatted as Date Time and i cannot do any calculation..
    why? :(

    • Hello Mauro,

      For me to be able to help you, please specify the formula that doesn't work and describe what lies in the ranges your formula refers to.

  20. Hi,
    This is a great website to learn excel from. I have a question, please reply me.
    I have two columns, in one of the column there is duration and in the other one I want to add only the hours and not the minutes and seconds. For example: 1:45:30, 2:26:47 and 0:35:17 in column A, should appear as 1:00:00, 2:00:00 and 0:00:00 respectively in column B.

    Is there any way

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 :)