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

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. Continue reading →

Comments page 5. Total comments: 393

  1. 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!

    1. 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.

      1. 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

        1. 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.

  2. 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.

    1. 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.

  3. 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?

    1. 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.

  4. 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.

  5. 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?

    1. 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.

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

        1. 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.

  6. You must get over 800 things

  7. 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?

  8. 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

    1. 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.

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

        1. Hi,

          These are instructions for Google Sheets :) Note that Excel functions may have different arguments.

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

    1. Hello Tom,

      Could you please provide a couple of examples of your source records and of the result you'd like to get?

  10. 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

    1. 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.

  11. 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!

    1. 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).

  12. Can I insert Live Countdown in Google Sheets?

    1. 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.

  13. 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?

  14. 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?

    1. Hello Niklas,

      Try a formula like this:
      =INT(J4-J3)&" days "&TEXT(J4-J3,"h"" hours ""m"" minutes ""s"" seconds""")

      1. 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?

        1. Hello Claudia,
          try to simply subtract times (e.g. B2-A2) first and then apply the custom number format. See the very last tip in the Example 3 in this section.

  15. 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.

    1. hello,

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

    2. 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.

  16. 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

    1. 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.

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

    1. Hi Zalmen,

      I mention decimals in the following section: "Example 3. Time difference in hours, minutes, and seconds.". It's in the first part of the blog post, please take a look.

      Also, here are some comments where I suggested formulas: Also, here are some comments where I suggested formulas: SUM and convert to decimal, format values correctly to see decimals.

      If you're still unable to get the result, please specify (1) the exact time value you're trying to convert, (2) the formula you're using (3) and the result you're getting. I'll do my best to help.

  18. 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

    1. 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.

  19. 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?

    1. 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

  20. 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?

    1. 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.

  21. 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!" ;)

    1. 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.

  22. 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.

    1. 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?

      1. 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

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

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

  23. 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!

  24. 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

    1. 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))

      1. That's great! Thank you!

  25. 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.

    1. 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.

  26. 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

  27. 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.

    1. 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.

  28. 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!

    1. Hi Manuel,

      The date and time format and separators usually depend on your spreadsheet locale, time zone, and display language. I described it in this blog post, please have a look.

  29. 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

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

    1. 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.

  31. 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

  32. what if i want to find the average of time with milliseconds, for example,

    1:23.45
    5:67.89

    1. Hello David,

      If the AVERAGE function returns errors, the problem is in the format of your source data. Google Sheets doesn't recognize the numbers you sent as numbers/time/duration. Also, the record 5:67.89 can't have 67 as seconds, the max can be 59.
      So, please correct your records and go to the Format > Number to pick one of the existing formats. Or proceed to Format > Number > More Formats > More date and time formats to create a custom one.
      Once it's correct, you'll be able to calculate the average even with milliseconds in time units.

  33. I'm trying to count total days between 2 dates, but my time is first and then the date, example: (column k) 15:45 03/23/2020 and (column m) 00:01 3/25/2020.
    Is this even possible? Thanks!

    1. Hello Sarah,

      Google Sheets will recognize and calculate your values only if they correspond to one of the existing formats (Format > Number in the menu). In this particular case, you should switch date and time units places. Such cells will then be formatted as Date time, and you'll be able to subtract one from another. (Otherwise, they are merely text strings and you won't be able to do anything with them.)

  34. EXAMPLE #3 does not work correctly. I cannot get the elapsed time by hours in a decimal format with that formula.

    1. Hello Emily,

      Please specify your start and end time and check how these cells (including the one for the result) are formatted.
      Also, please provide the exact formula you're using and whatever it returns to you.

      I'll try my best to help.

  35. Hi Natalia,
    I have data with multiple times in a day formatted as start and stop times e.g.
    1/1/2020 17:19 1/1/2020 19:22
    1/1/2020 19:35 1/1/2020 20:25
    1/1/2020 21:08 1/2/2020 1:21
    1/2/2020 0:04 1/3/2020 17:30
    1/2/2020 9:05 1/3/2020 6:30
    I am trying to determine how much total time was used EXCLUDING overlaps. Can you suggest how this might be accomplished?

    1. I figured this out by subtracting the previous end time from the next start time. Assuming the example I gave was 2 columns wide and 5 rows long this would be B1-A2.

      1. Hi Tom,

        My apologies I didn't manage to reply right away. Good to know you found an easy solution :)

  36. =HOUR(SUM(E9-D9))+(MINUTE(SUM(E9-D9))/60)

    1. Hi Kris,

      This was the formula for Madden's case.
      I'd advise you to omit the excess SUM parts. Also, if the start and end times are on different days (but the difference doesn't exceed 24 hours), you need to:
      1) introduce the ABS function that turns the negatives into positives
      2) subtract the time difference from 24 hours only when the difference is negative

      Taking all these into account, here's the formula:
      =IF((E9-D9)<0,24-HOUR(ABS(E9-D9))+(MINUTE(ABS(E9-D9))/60),HOUR(E9-D9)+(MINUTE(E9-D9)/60))

  37. Hi natalia.
    I tried your formula it worked but if the time starts for example at 5pm and ends in 1am it gets and error since it shows negative. What should be the right formula?

  38. Hello,
    I am using this template for a weekly timesheet: https://docs.google.com/spreadsheets/d/1qgfcWdcrxVSJu0HoyUZp7w4pGrtHoH7iu-nMe5Qb7To/edit#gid=874047713

    I am trying to change the "Total Hours cells to show a decimal format rather an hour and minute format. I have tried adding the multiplication by 24 to the formula in the "Total Hours" cells and setting them to Automatic formatting, but rather than displaying as a decimal, they show an h:mm am/pm format. For example, when I applied it to a cell displaying a total of 1 and a 1/2 hours (1:30), rather than becoming what I am aiming for (1.5) it becomes (1:30 AM).

    What is the best way to achieve what I am looking for?

    1. Hello Brett,

      You are right by adding the multiplication by 24 to the formula in "Total Hours". If Automatic format doesn't work for you, try Numbers format instead.

  39. Natalia, this is a jewel of an article with enviable commentary and an exemplary response log. By any measure, your piece on the functions of time in Google Sheets sets a high bar for technical articles web wide. It is an archetype of technical journalism that readers are lucky to encounter in any subject. You deliver highly functional content in an effective, economical that demonstrates a profound understanding (whether intuitive or intentional) of your readers' needs and motives. Your deft phrases comprise sufficient flourish to rank in grammatical excellence while avoiding even an occasional minute of waste on superfluous verbiage. Indeed, the syntax and structure of this article create a perfect, invisible corpus which dances in perfect time with the rhythm of your message. This dance of information in symbols carries the reader immediately, effortlessly away into the topic. Attention rapt the reader isn't left to count the steps (moments or words) until the relief of termination but is instead lost in interest of the information until the steps exhaust, their number definite, exact and the article ends with no more than the perfectly shaped phrase nodding to decorum and literary.
    Then to remain in touch with your work and readers for years after original publication, actively responding to questions with distilled expertise...incredible and inspiring.
    Well done, well done. I haven't a question of formula or function to put to you yet but can certainly see a future that includes several germane inquiries. As I encounter those questions, I hope to indeed put them to you and appreciate the subsequent education I know you will provide.
    Keep onward in this most favorable spirit. Thank you for your effort and contribution to a better world.

    1. Seconded. This is the interactive internet at its best! You must make Tim Berners Lee proud!
      Thank you.

  40. Hello Natalia!
    I am kind of going off a couple of these commments.
    First, I am trying to formulate so I can calculate regular hours worked.
    So when I have
    Time In 8:00 AM
    Lunch Out 1:00 PM
    Lunch In 2:00 PM
    Time Out 4:00 PM
    I am getting the right calculation by using =HOUR(SUM(B14-B11))+(MINUTE(SUM(B14-B11))/60) because we get paid for an hour lunch, so Im not worried about the lunch in this case. However, if I take a half hour lunch, and still get paid for the full hour, how do I add a half hour to this time without having to do it manually
    EX
    Time In 8:00 AM
    Lunch Out 1:00 PM
    Lunch In 1:30 PM
    Time Out 4:00 PM
    This should calculate out to 8.5 hours.
    Thank you so much!

    1. Hello Carly,

      Will this workaround help?
      =24*(B14-B11)-24*(B13-B12)+24*(E2)

      E2 contains 1:00:00 which stands for the paid lunch duration. The logic is: work_day minus time_spent_on_lunch plus paid_lunch_hour

  41. =IF(B5=0,"",IF(B5>0,(B8-B5)+(B12-B9),"OUT")) This is the formula I am using to calculate my hours however the sum total shows up with am or pm how can I remove this please?
    thank you,
    IN WORK 9:00 AM 9:00 AM 9:00 AM
    LUNCH IN 2:00 AM 2:00 PM
    LUNCH OUT 2:30 PM 2:30 PM
    OUT WORK 5:30 PM 5:30 PM 1:00 PM
    TIME WORKED 8:00 PM 8:00 AM 4:00 AM 8:00 AM

    1. Hello Rose,

      select the cell with the total, go to Format > Number > More Formats > More date and time formats, and choose the format you'd like to see in the cell.

  42. Hello,
    What is the formula if I need to display the duration in Hours/Min/sec when all I have is the seconds?
    1935.4
    1974.5
    4140.8
    1119
    774
    600.1

    Thank you

    1. Hello Thaylin,

      Do you calculate these numbers or enter manually? Also, are the numbers after the dots milliseconds?

      1. Hello,

        These Numbers are captured from an app that generates them, I then have to paste them into Sheets. the decimal is milliseconds.

        Thank You

        1. Thank you for the details, Thaylin.

          First, you need to make Google Sheets understand that those are seconds. For that, divide each number to 86400 (the number of seconds in a day). Supposing your time is in column A, use formulas like this:
          =A2/86400

          Then select cells with formulas and apply the Duration number format. To see milliseconds as well, you'll need to proceed to More date and time formats, and add them to the format manually. Please check the tip at the very end of the 3rd example in this paragraph to get a better idea of that.

          1. Thank You for the help.

  43. Hi Natilia,
    I've been beating my head against this one today. I have two columns that are recording dates with the calendar function in data validation, how can I calculate how many days have elapsed between the two columns.
    Column A records the first date, and column J records teh second date, and I want to be able to calculate teh total amount of days.

    Thank you

    1. Hi Jeff,

      What if you try this formula:
      =ARRAYFORMULA(DATEDIF(A2:A20,J2:J20,"D"))
      It's enough to enter the formula to the first cell -- it will expand automatically to all rows you specify in the range thanks to Arrayformula.

  44. Great thread so far, thanks. Is there a way to just use one cell to calculate duration? ie.
    05:00 - 15:00
    05:30 - 14:00
    =Total # of hours
    Thanks,
    Mike

    1. Thank you for the feedback, Mike.

      For your task, you'll need to wrap all formulas used to find the time difference with one SUM function, like this:
      =SUM(B1-A1,B2-A2)

      Make sure to format the cell with the formula as Duration, it should work then.

  45. Hi Natalia,
    Thanks for all the help you are giving! My question is, I am trying to average a group of numbers that are minutes and seconds, and I can't figure out the formula to use. as follows;
    :20
    1:02
    1:07
    :52
    :47
    :52
    Thank you!

    1. Hello Rachel,

      First of all, you need to enter seconds along with minutes -- 0:20, 0:52, 0:47, etc -- for Google Sheets to understand the contents as time.
      Then, use the AVERAGE function.

  46. Hi Natalia,

    Im struggling to correctly set up a sleep tracking spreadsheet. My end result is to compare weekly sleep durations but the issue comes with the following example
    Day 1: Time asleep 1AM -> Wake up 7AM => Duration 6h [easy]
    Day 2: Time asleep 11PM -> Wake up 7AM => Duration -+ 16h

    Obviously if I do manually reverse the subtraction I can get it but I'm trying to set it up automatically as I'm using this =ARRAYFORMULA(IF(C3:C370, AY3:AY37 - C3:C37, "")) which does get me the daily correct Duration but when doing the weekly average its incorrect as the numerical values of those dates that I fell asleep before 00:00 is wrong.
    Any ideas on how to go about this?

    1. Hi Ivan,

      If you enter time without a date, Google Sheets treats these times as of belonging to one and the same day. You can check that by changing the format to Date time.
      So I'd advise you to enter both date and time to your cells (just set the format as Time to keep the date hidden) and you should get the correct results.

  47. Hi
    I am just trying to create a staff rota. start time/finish time/total hours worked. For some reason some of the hours are adding correctly like 08.30 - 17:00 is 8.5 hours but 09:30 - 15:00 is coming up at 9 hours? Should my total column be in a different format? I also want to minus 30 minutes from the break, can I do this. Sorry for the really basic question

    1. Hi Donna,

      Could you please provide the formula you use and specify the format of your source and result cells?

  48. Hi Natalia,
    I'm building a spreadsheet to audit the release times of tour buses. I have a cell (A1) that lists the cost of a bus per hour. I have one cell (B1) for the time a driver records the bus released, and another cell (C1) for the time a tour guide records the bus released. Finally, I have a cell for the results (D1)

    First, I need to figure out the difference in reported times. If the difference is less than 15 minutes in either direction (negative or positive), I'd like D1 to return "$0.00." If the difference is more than 15 minutes, I'd like D1 to return the total difference in time multiplied by the cost of the bus (A1). In order to get these returns, I think the differences in time need to be represented as a decimals instead of time formats, but I'm not sure if that's right or how to do that. Any advice would help. Thank you so much!

    1. Hi Adam,

      Thank you very much for the clear description of your task. :)

      Please try the formula below on your data:
      =IF(ABS(C1-B1)<TIME(0,15,0),"$0.00",(MINUTE(ABS(C1-B1))+HOUR(ABS(C1-B1))*70+SECOND(ABS(C1-B1))/60)*A1)

  49. Hi, I'm having a weird problem, and I can only find answers that is seems no one would ever want lol.
    I am using =SUM(E9-D9) to calculate elapsed time on an employee timesheet. The problem I have is I need the answer to be in a decimal format for payroll and I can't find a way of doing it without adding another cell with the conversion.
    9:30 - 1:45, with this formula I get the answer 4:15. I need the answer to say 4.25. Any ideas?

    1. Hi Madden,

      If I get it correctly and you use =SUM(E9-D9) to calculate the elapsed time (though it does look strange), try this formula to do both calculate and convert to decimal:
      =HOUR(SUM(E9-D9))+(MINUTE(SUM(E9-D9))/60)

      1. Thanks Natalia Sharashova. This formula worked for me.

  50. I am new to google spreadsheet, what is the formula for checkbox = show current time
    Ex: check checkbox on A2 then current time will appear on B2

    Thaks so much

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