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 2. Total comments: 393

  1. Hi
    Is there also a formula that compares the Start_Date, End_Date, Start_Time , End_Time (4 columns) to the current Date/time (with =NOW() function) with a return of TRUE or FALSE in a seperate column
    thx

    1. Hi Peter,

      Do you need TRUE if any of the times/dates are the same as current ones? Or if all at the same time? Or only start ones or end ones?
      Also, are you aware of the fact that NOW() is a volatile function? Meaning each time you make edits to your sheet, it recalculates itself and so do other formulas using NOW() returning different results each time.

      1. Hi Natalia,
        I need a TRUE if the current date and time matches the start date/end date & start time/end time. e.g
        Column/Row A1: start date: 06-02-2024
        Column/Row B1: end date: 06-02-2024
        Column/Row C1: start time: 07:00 (AM)
        Column/Row D1: end time: 13:00 (PM)
        Column/Row E1: TRUE (if date/time match)
        I'll do a HTTP GET Request for a lookup into this file. Google sheets sees the current date/time and compares it to the date/time within the sheet. If there is a match, I get a TRUE back

        thx for the reply
        Peter

        1. Hi Peter,

          Sorry, I still don't understand whether you need OR or AND logic :)

          But feel free to try and adjust this formula. It will return true if either start OR end date AND time are the same as the current one.
          =IF(OR(AND(TODAY()=A1,TIMEVALUE(TEXT(NOW(),"hh:mm"))=TIMEVALUE(C1)),AND(TODAY()=B1,TIMEVALUE(TEXT(NOW(),"hh:mm"))=TIMEVALUE(D1))),TRUE,FALSE)

          1. Hi Natalia, thanks for your help, I need a formula that checks if the current date and time is correspondend with the values included in the 4 columns, if so then a TRUE back as a Result e.g:

            Current Date/Time = 07-02-2024:17:00:00 Start_Date | End_Date | Start_Time | End_Time | Result
            07-02-2024 07-02-2024 10:00 19:00:00 True
            07-02-2024 07-02-2024 19:00 23:59:00 False
            thx
            Peter

            1. Thank you for the example of the expected result, Peter.

              So basically you want to see TRUE if the current time falls between start and end time and the today's date also falls between the start/end dates? If so, this formula should do the trick:
              =IF(AND(TODAY() >= A2, TODAY() <= B2, TIMEVALUE(TEXT(NOW(), "HH:mm:ss")) >= C2, TIMEVALUE(TEXT(NOW(), "HH:mm:ss")) <= D2), TRUE, FALSE)

              1. 👍yes this is perfect thank you for the super good help and expertise
                I appreciate this

  2. Is there a formula to find the number of occurrences of interval range of time between two columns.

  3. Dear all
    I would like to make a schedule for production tasks in a weekly production plan. So for every product there is number of working hours
    A-4h
    B-15h
    C-22h.....
    and then I would like to calculate if I work from Monday 8am, and shift is 8 hours when will every product be finished?

    1. Hello Milos,

      Though I can't create a schedule for you, I can suggest functions that may help you with the task:

      • Most likely you will need a helper table where you assign each letter a number of hours. You can then use the IF function along with the VLOOKUP to refer to that table and pull correct time for different letters.
      • Learn how to add or subtract hours in Google Sheets here.
      • And this info is to get the time duration.

      Hope these help!

  4. Hi Natalia,

    I enjoyed reading your blogs. May I ask your help if this is possible:

    Is there a way to to get the sum of hours in google sheet per cell if I the value per cell is not a number but a "text" and that the assumption is each cell with a "text" value equates to 15 mins. Does that make sense?

    Thanks!

    1. Hi Rico,

      Thank you for your feedback!

      Since Google Sheets can't calculate text, I believe you'll still need some helper table showing the time for each text so you refer to it in your formulas. But I'll be able to suggest better if you provide a few examples of the data you have, how you want to calculate it and what should be as a result.

  5. Tracking Number of Minutes Late from Scheduled Time on Sheets

  6. I am trying to create a time sheet in google sheets for multiplue people using rows So I have used the following to find the total time work (duration)
    using this formula =SUMPRODUCT((MOD(COLUMN(F5:N5) - COLUMN(E5), 2) = 1),(F5:N5 - E5:M5), F5:N5)
    with the following info
    E5=5:00 Start time
    F5=13:00 End Time
    G5=6:00 Start Time
    H5=13:00 End Time
    I5=7:00 Start Time
    J5=17:00 End Time
    K5=7:30 Start Time
    L5=14:00 End Time
    M5=6:00 Start Time
    N5=13:00 End Time
    Gives me a total time duration of 22:47:30
    When the total should be 38:30:00

    What have I done wrong?

    1. I have also tried
      =SUMPRODUCT((MOD(COLUMN(F5:N5) - COLUMN(E5), 2) = 1) * (F5:N5 - E5:M5 > 0), F5:N5)

      1. Hello Erik,

        Please try this formula:
        =ARRAYFORMULA(SUM(TIME(HOUR({F5, H5, J5, L5, N5}), MINUTE({F5, H5, J5, L5, N5}), 0) - TIME(HOUR({E5, G5, I5, K5, M5}), MINUTE({E5, G5, I5, K5, M5}), 0)))

        1. That worked. What is the difference between that and what I was doing? Also If I want to expand it out for the entire row I just put the rest of the columns in where they go?

          1. I'm glad it helped!

            I used TIME to tell Google Sheets what time units are these exactly (it literally converts them to time format before summing up). And it's more straightforward.
            In yours, there's no obvious conversion and Google Sheets may interpret some units incorrectly.

            And you're right about adding columns to their places in the formula if you expand the data to the entire row :)

  7. hi first of all im sorry for my broken english but i hope there's someone here who have the answer is this possible to calculate in spreadsheet/excel,
    so, i need to calculate how long someone replying chat, but i have to exclude or ignoring break time and before office hours. for example, we start at 09.00 and have break time at 12.00-13.00, so let's say someone chating me at 11.53 and i reply at 13.03, so i just want to show 10 minutes ( from 7 mins before 12.00+3 mins after 13.00), or any advice how to solve this in spreadsheet/excel??

    1. Hi lita,

      If you work in Google Sheets & your chat start time is in A2, reply time start is in B9, this formula will help:
      =IF(OR(A2>=TIME(13,0,0),B2<=TIME(9,0,0)),0,MAX(MIN(B2,TIME(12,0,0))-MAX(A2,TIME(9,0,0)),0)+MAX(MIN(B2,TIME(17,0,0))-MAX(A2,TIME(13,0,0)),0))

      You will learn more about the IF function in this article.

  8. Hi,

    I am calculating hours and it is showing 9:30 aka 9 hours and 30 minutes. How do I convert 99:30 to be 99.5 hours instead?

  9. I try to calculate the duration between 11pm and the day after 2am, which should be 3 hours, but when I use the duration format, it gives me 19h as it calculate from the same day and not the day after...
    How to do this? Thank you so much for your help

    1. Hello Benoit,

      If you're trying to get the time duration, just make sure to add dates to the time units as well so Google Sheets calculates everything correctly.

  10. Thank you so much Natalia. You are awesome!
    : )

  11. Hi Natalia,

    I'm having an issue where I typed in the "am" and "pm" into my cells and now when I subtract them it is not finding the duration between the times. Instead, it is taking the hours and minutes from the second cell (the one I'm subtracting) and going back in time by that amount. I tried getting rid of the am and pm to do the math instead but it just comes right back after I erase it in the cell.

    Hope I explained it well. Have your encountered this issue? I'm on Mobile by the way.

    1. An update on this, I found out that on the desktop version you can highlight the cells, then go to format -> duration to calculate the duration instead. I'm still not sure how to fix it on Mobile, but I can fix it when I get home. Hope this helps someone!

  12. Somewhere in all this there may be a solution to my issue but I am not seeing it. I need to calculate total elapsed time between values in 2 cells which are in the format of MM/DD/YYYY HH:MM:SS, as it Start Date/Time and End Date/Time. It is not feasible to split this into 4 columns as the data is from a ticketing system and gives one value as the start and one as the end.

    For example, the elapsed time between 08/22/2023 08:00:00 and 08/22/2023 09:00:00 is 0 days 1:00 hour. That calculation works fine. But, the elapsed time when it spans days AND the format in each cell is as noted eludes me. The elapsed time between 08/22/2023 08:00:00 and 08/23/2023 09:00:00 is 25 hours and I can't get it to do both in the same group of 2 columns, with the resulting elapsed time in DD:HH:MM:SS or some similar format, e.g. 1 day 1 hour 0 minutes 0 seconds, etc.

    Suggestions?

    1. Hi David,

      Sorry, could you please specify the formulas you use to calculate your elapsed times? And also specify the exact formats you're applying to your cells.

  13. Hi Natalie,

    I have a sign-in sheet that when you select your name from a drop down menu the computer returns the time you signed in. I want a formula that calculates how many minutes you sign in late beyond 7:30. Also I want any time signed in beyond 7:30 to turn red.

    Many thanks.

  14. putting time data into specific time groups in google sheets
    like 1:00,2:00,13:00,15:00,17:00 in group1 1pm-6Pm = 2, group2 13:00-17:00=3

    is there any formula which can help

  15. Hi

    I am trying to use google sheets to work out the fast time across a number of rows. I have set the format of the time cells as custom: MM:SS.00

    The formula I am trying to use is =MIN(D6:N6) - however the result is coming back as 00:00.00

    I've tweaked the formula to exclude any empty cells, but its still coming back at 00:00.00 - any tips would be massively appreciated!

    1. Hi Maddy,

      The time format is merely what you see in cells. Google Sheets still works with values behind the format.

      For me to be able to help you better, please specify what's in D6:N6 cells.

      You may also share an editable copy of your spreadsheet with me (support@apps4gs.com) so I could look directly into the file. I kindly ask you to shorten the table 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.

  16. Its not working in mobile app

      1. hi @Natalia i need to find the total number of time formats(00:00 - 23:59) updated in the sheet
        Please help me with this formula

        1. Hi Raj,

          Sorry, what do you mean by "updated formats"? Do you need the number of cells with the non-standard time format? And which one you regard as the standard one?

  17. How do I divide two durations? For example.... I have 1,216 hours of available time on my team in a given month. It takes the team 28 minutes to produce a widget. I want to know how many widgets they can produce in the 1,216 hours. The result should be in whole numbers not duration.

    Basically I'm looking to calculate how many times 28 minutes goes into 1,216 hours. Thanks

    1. Hello Jon,

      Assuming the hours are in A2, here's the formula:
      =ROUND(A2*60/28,2)

      1. A2*60 converts hours to minutes.
      2. It's divided by 28 to find out the number of widgets.
      3. ROUND rounds the result to 2 decimals.

  18. what about the negative value ie. PLANT OUT ETA
    10:52:00, 15:52:00 =?
    07:18:00, 12:18:00 =?
    00:46:00, 5:46:00 =?

    1. Hello vikas,

      I'm sorry but your task is not clear. Please describe it in detail including the formulas you're trying to use. I'll do my best to help.

  19. I am working on a competition schedule. Depending on the levels(there could be 30) there is a different time allotted for each competitor. IF(G4=1,"30 MINUTE LESSON" is an example of one of my time formulas. in the next column, I have if(H3="30 MINUTE LESSON","00:30" as an example in my time column. All of this is working fine. However, on my last column I am simply adding my 00:30 from the last formula I have shown to a time that I start at the top of the column. That is a manually entered time depending on when the competition begins. Everything is working, except when we try and move rows, this last column will not update the correct time. Help?

    When scheduling competitors they need to be moved around to ensure they are at the right time and in the right group.

    1. Hello Crissteen,

      For me to be able to help you, please share an editable copy of your sheet with me (support@apps4gs.com) and specify where the formulas that don't update correctly are. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      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 try to help.

  20. I am facing a problem that want to write a function which will return date and time, example below

    function setD(x) {
    if(x != ""){
    const d = new Date();
    return d;
    }
    }

    This function returning a value which correct date which value looks : 4/21/2023

    I want to return my computer time which value should looks like : 04:35 AM

    I'm not able to find that time function which I possible excecated in the function and return a time value only, can you help me ?

    1. Oh thanks a lot I got it. and if anybody need then you can take it from here :

      function setT(y){
      if(y != ""){
      const a = new Date().getHours();
      if( a < 12 ){
      const c = 'AM';
      const b = new Date().getMinutes();
      const Its_Time = a + ':' + b + ' ' + c;
      return Its_Time;
      }else{
      const d = a - 12;
      const c = 'PM';
      const b = new Date().getMinutes();
      const Its_Time = d + ':' + b + ' ' + c;
      return Its_Time;
      }
      }
      }

  21. Hi Natalia,

    I am subtracting the hours accumulated for vacation time remaining. I'm trying to figure out how many days left in my vacation time.
    If I have 72 vacation hours (H3) and have used 3 hours (H4), 3 hours (H5) and 24 hours (H6) and now have 42 hours (H11=H3-H4-H5-H6-H7-H9-H10) remaining, how do I calculate or how do I format cell H12 to reflect the remaining days left if I work 3.5 days/ week (7.5 hours each)?

    Hope this is clear.

    Thank you!

    1. Hi fabio,

      If I understand your task correctly, you can use this function:
      =(H11*24)/7.5

      Multiply H11 by 24 to return hours as a decimal first. Then divide it by the number of hours you work daily. I've got 5.60 days.

      If this is not exactly what you need, please specify what the result should be and elaborate on the last part of your task: "reflect the remaining days left if I work 3.5 days/ week (7.5 hours each)"

  22. Hi Natalia,

    I am currently tracking my task durations in google sheets, which I then add up (or subtract) to calculate the total time duration in the same column.

    I am currently using the format : Elapsed hours (1) h: Minute (1) m
    The end result is as expected, for e.g : 0h:30m , 5h:0m etc

    Is there a way to hide the hours (h) or minutes (m) if they are 0?
    So from the example above, I'd like it to display as 30m and 5h only.
    What would be the right format to use to achieve this?

    1. Hi Drake,

      If you set up the format using the Google Sheets number format, it's going to show minutes/hours even if they're zero units.
      To hide zero values, I believe you need to incorporate the IF function & check if the result has 0 minutes or 0 hours. Then return the result in the required format set up by the TEXT function in the formula itself.

  23. Thanks for the post above on calculating times. I'm struggling to calculate negative times.

    I have a spreadsheet that calculates the duration of a shift from the start and finish time.

    What I'm trying to achieve is a formula that works out the difference,negative or positive, between a standard 8hr shift.

    Any ideas?

    Thanks

    James

    1. Hello James,

      Could you please describe the problems you've faced with negative times in detail?

      Or consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) your sample data with formulas you tried, 2) the result you expect to get. Please shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
      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 to help.

  24. I have a self-made tool to keep track of my work hours, now I want to have it calculate the number of hours over 40.

    I'm using the Time hours:min:sec format for the time cells.

  25. Hello,
    Is there a formula for adding time im a single cell? So if i had 9-5 in a cell, could a formula add the 9-5 to equal 8 in another cell? Im tying to make a schedule using this format oppose to the duration method.
    Thanks in advance.

  26. no TIME function found, this is old

  27. Thank you soooo much for this info. Super helpful and easy to understand.

  28. Thank you very much! Really appreciated!

  29. How can this be applied to a start time before midnight and a end time after midnight? like working an overnight shift?

    1. Hello Jason,

      If you're trying to get the time duration, just make sure to add dates to the time units as well so Google Sheets calculates everything correctly.

  30. I've been looking EVERYWHERE for these simple formulas! Thank you.

  31. Hi Natalia,
    Can you please provide me formula in which I can calculate total hours they picked & packed in a day less the breaks they had. And also if they start for example picking around 2:00pm and they finished the next day around 11:AM. Our working hours starts at 10:00 am until 7:00 pm only. I hope you can help. Thanks.

    1. Hi Ghanshyam jangir,

      For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with your sample data and the result you expect to get. Please include your start/end time units and the breaks (or how you indicate them). I kindly ask you to shorten the table to 10-20 rows.

      I'll look into your task and try to help.

  32. Great place to know more about advanced excel tricks and tips

    I have a question let's suppose I want to add 2 hours to the time and date format columns and also want the date to change were adding 2 hours in the 24-hour clock make it the next day

    example

    Column A = Date + Time (02.11.2021 00:00:00)

    Now by adding 2 hours in the above case date needs to be (03.11.2021 02:00:00)

    1. Hello Amin,

      I'm afraid the Google Sheets way of calculating this differs from your desired result. Since 02/11/2021 00:00:00 is midnight (the very beginning of 02/11/2021), adding 2 hours will result in 02/11/2021 02:00:00.
      You need to have 02/11/2021 23:59:59 in order to switch to the next day with the calculation. Or simply add 26 hours instead: =A2+(26/24)

  33. JJLIVERPOOL28 if you have anything to help help you help or help help me get

  34. Hi Natalia, Can you please provide me simple formula in which I can calculate total hours they picked & packed in a day less the breaks they had. And also if they start for example picking around 2:00pm and they finished the next day around 11:AM. Our working hours starts at 7:30am until 4:00pm only. I hope you can help. Thanks.

    1. Hi Cristine,

      I'm sorry, I'm afraid these pieces of info are not enough to give you a formula. What about the breaks? Are they indicated in your document as well? Can they also start one day and end the next day?

      For me to be able to help you better, please consider sharing 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. 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.

  35. Hi,

    I am trying to put a sheet that works out how many hours a team member has worked between 08:30 - 17:30 (weekdays) and how many hours they have worked outside of this on a shift (all day at weekends). The shifts can finish at any time including after midnight. I have tried multiple formulas with no success.

    The cells that require attention are B27 to B36

    The starting information is:

    B27 = TIME(08,30,0) {start of in hour timeframe - fixed time}
    B28 = TIME(17,30,0) {end of in hour timeframe - fixed time}
    B29 = 00:00 {start time of shift - variable field}
    B30 = F8+B29 {end time of shift - fixed value, F8 takes total shift time from another cell}

    B32 = =IF((B30-F8)>TIME(6,30,0),TIME(6,30,0),"")
    B33 = =IF(B30>TIME(17,30,0),IF(B30<TIME(23,59,59),B30-TIME(17,30,0),B32),0)

    The most recent attempt at making this work came out as this:

    B35 {out of hours worked} -

    =(IF(B291,B30-1,0))-(if((if((B29+F8)>1,B30-1,0))>TIME(8,30,0),(if((B29+F8)>1,B30-1,0))-TIME(8,30,0),0)))+B33

    B36 {in hours worked} -

    =F8-B35

    I have sent a copy of the template to yourselves (named "Work in progress"). I am aware that these calculations do not even begin to factor in the weekend shifts but wanted to establish the timings before moving onto when the date of the shift occurs.

    Thank you for your guidance.

    1. My apologies,

      The formula in B35 is actually this:

      =(IF(B291,B30-1,0))-(if((if((B29+F8)>1,B30-1,0))>TIME(8,30,0),(if((B29+F8)>1,B30-1,0))-TIME(8,30,0),0)))+B33

      1. Hi Adam,

        Thank you for sharing your spreadsheet and for the detailed description.

        I'm afraid there are some moments that I don't understand in your tables. E.g. what those 6:30 hours mean in B32 and whether you'd like to take them into account for calculations as well. I'm also not sure what 'total premium time on shift' should contain. Is it different from B33?

        I've adjusted your formula in B33 (my variant is in C33) as it looks like your formula contains odd 10 minutes.

        For now, based on what I was able to understand, I would suggest to try using the following logic (see very simple demo cells on Sheet2):

        1. calculate the total time spent for the journey: shift + overtime
        2. check if the hours spent for the journey overlap with any shift hours
        3. subtract that overlap from the total time spent for the ride. This way, you will get the total overtime hours

  36. How do. You subtract minutes from time?

  37. Hi Natalia,

    I'm currently creating a work schedule for my new work place and im trying to convert induvial hours logged over 24hrs in one section into reading like a 24 hour clock in the another section of the schedule (eg 10.00 - 18.00) or if the person was off (libre) then "libre" would show up. Do you know if this is possible.

    Thanks !

    1. Hi Conor,

      please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) your source data, (2) the result you expect to get. Please also 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.

      If you have confidential information there, you can replace it with some irrelevant data, just keep the format. I’ll look into it and try to help.

  38. Hi im trying to do Operators Efficency Sheet in Google Spreed Sheets i'v done most of it but hit dead end 2 weeks now
    so the problem is on my sheet. differnce103800 in own column and in diffrent column i'v divied 200 gives me 519 and in diffrent column it's give me 8.65 it's not what im looking for im looking for 08:39:00 which is correct answer i'v tried everything can you help
    please

    1. Hi Matthew,

      For me to be able to help you better, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. 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.

      I'll look into it and try to help.

  39. Hi I was wondering if there is a formula that can calculate the number of minutes after a specific time. I need total number of minutes after 3pm. Thank you!

    1. Hi Kim,

      Assuming your start time (3pm) is in A2, here's the formula to try:
      =HOUR(NOW()-(TODAY()+A2))*60 + MINUTE(NOW()-(TODAY()+A2)) + SECOND(NOW()-(TODAY()+A2))/60

      Make sure to format a cell with this formula as a number.

  40. Hi there! I want to make a column that updates with a new number displayed every 6 hours. For example, it starts with 2, and after 6 hours have passed it displays 4 instead of 2. How would I go about this?

  41. Hi! My hope is that there is a sum formula for adding song lengths that contain hours, minutes, and seconds. I have them set up in this format: 01:04:35 but it doesn't add it as hours, minutes, and seconds... Is there a formula? Do I need to put each quantity in separate cells? hours in one, minutes in one, and seconds in another? thanks!!!!!

  42. Is there a way when subtracting time to automatically convert time format of minutes to hours as my goal is like this:

    On A1 is start time say 8:00 AM and on A2 is end time as 8:40AM, and it returns the duration on A3 as "40 mins".

    What I aim is that if it's more than 60 mins, say A1 is 8:00AM and A2 is 9:20AM, it automatically formats A3 result to "1 hr 20 mins"

    1. Hello John,

      Try this formula:
      =IF(MINUTE(A2-A1)>60, (HOUR(A2-A1)&" hours, "&MINUTE(A2-A1)&" minutes"), A2-A1)

  43. i have to convert data in 3-3 hours conversion ex- if one message is popup on 12:00am then display 12:00am to 3:00am time slot, other message is came on 7:00pm then display 6:00pm to 9:00pm time slot

  44. Please could you help with the following? I am not sure what kind of formula should be used. I wanted to make use of a screenshot of an example of what I want, but it won't allow me to paste a screenshot.

    I need to set up a program for a dancing competition. I have different sections in the dancing competition, with each section have different time limits per performer. So here is an example:

    Section: Solo Start time: 9:00 AM

    Competitor 1 (Maximum time: 3min:30sec) (Grace time: 2min)
    Competitor 2 (Maximum time: 3min:30sec) (Grace time: 2min)
    Competitor 3 (Maximum time: 3min:30sec) (Grace time: 2min)
    Competitor 4 (Maximum time: 3min:30sec) (Grace time: 2min)
    Competitor 5 (Maximum time: 3min:30sec) (Grace time: 2min)
    (Grace time after each section: 2min)

    End time: ?

    Section: Improvisation Start time: SAME AS END TIME - ?

    Competitor 1 (Maximum time: 2min:0sec) (Grace time: 1min)
    Competitor 2 (Maximum time: 2min:0sec) (Grace time: 1min)
    Competitor 3 (Maximum time: 2min:0sec) (Grace time: 1min)

    So I want the condition to be, if it is a solo, then maximum time per competitor is 3min:30sec with 2min grace for each competitor, with 2min grace after section. Start time is 9AM, so what will the end time be after section has ended?

    Hope the above example makes sense.
    Thanks in advance

    1. Hello Antoinette,

      Feel free to share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. 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 and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      1. Thanks so much Natalia. I have shared an editable sheet. It is just an example, as I cannot send the original sheet, because in our country there are laws against sharing people's information (especially children). I hope it makes sense. I do know what I want is quite complicated.

        1. Thank you for sharing your spreadsheet, Antoinette.

          I created a new sheet – Copy of the Result – and entered all formulas there. I use the IF function to return the times per competitors based on the section mentioned in A2. You can add more conditions for other sections with the help of nested IF or IFS to reuse the same formula for other sections as well.

          The total duration is a simple sum of all those time units.

          Hope this helps! Please let me know if this is what you need :)

          1. Thanks so much for your help.
            I was trying to make it very complicated, and it actually is not.
            This really helped a lot.

            Hope you enjoy your day.

  45. Hey Natalia, hope you are well,

    Just making sure you got my message on (May 20, 2022 at 5:14 pm)

    1. Hi Jordan,

      Sorry I couldn't reply earlier. I replied a few minutes back though, please see my comment below.

      If you still need some help with your formulas, please consider checking out the blog post we wrote about the IF function. Or share your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. Please 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.

      1. Hi Natalia,

        I have invited you to my Google Sheets for you, i think it would be the best way to figure it out.

        1. Hi Jordan,

          Thank you for sharing the file. Seeing the whole picture, we've come up with another solution. I entered the formulas on the Copy sheet, please take a look.

  46. Hi Natalia,

    I have been reading through your blog posts and they are really tremendous. Need your support on one of my on-going problems which I am trying to solve through formula in Google-Sheets :

    1. end_date = Available = (Today())
    2. Number of months = Available in decimal = (4.1) months
    3. start_date = ? (how to compute it)

    Your help in this would be much appreciated.

    Best,
    Deep

    1. Hi Deep,

      I'm sorry, I'm afraid I don't fully understand your task. Is that number of months shows the duration from the start time?

  47. if I want to use =TEXT(B2-A2,"h:mm") to subtract time but I want it to display the result as 3h 2m how do I format it to do display it as such?

    1. I ended up figuring it out after days of trying things it was super easy. This is for anyone interested.

      Times:
      D2 - 5:18:00 PM
      E2 - 8:20:00 PM

      =HOUR(E2-D2) & "h " & MINUTE(E2-D2) & "m"

      Result - 3h 2m

      1. Hi Victor,

        Sorry I couldn't reply to you earlier. Happy to see you've got it figured out :) Thanks for sharing your solution!

        1. I ran into an issue today where it won't subtract backwards times like pm to am. Such as

          12:23 AM - 9:50 PM

          It gives an error because it's a negative number and I still haven't found a workaround

          1. Hi Victor,

            The result I'm getting with this set of times is 21h 27m.

            For me to be able to assist you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data with the formulas that don't work (2) the result you expect to get. 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.

            1. Yes but that result is backwards and not correct. 12:23 Am is right after midnight. If you subtract midnight to 9PM that is only 3 hours we are trying to subtract not add.

              I'm building a sheet to track all of my forex trading. I want to enter the time I start a trade and the time I end a trade and it tell me how long that trade went for. So I can keep an idea on how long these trades are lasting. Yesterday I started a trade at 9:50 PM and it went until 12:23 AM which is right after midnight. So I need it to subtract the time.

              The result I'm looking for is 2h 33m

              1. Can you give me a sample sheet that I can take a look at. I'm currently using two cells to collect this data. One for just the date and one for just the time.

                4 cells total. Date, Time Start, Time End, Time completed

              2. Hello Victor,

                If you'd like to take a look at how the formats work, you will still need to go to the Format settings to see the difference. So you can just literally (1) enter 9:50 PM into one cell and both, time & date, e.g. 06/02/2022 9:00 PM, in another, (2) select both these cells, (3) and go to Format > Number a few times and try to apply different formats, including those with the date unit. You'll instantly see what I mean about the dates.

                For me to be able to help you better, I kindly ask you to share your sample sheet with us: support@apps4gs.com. You don't need to provide your entire document. A sheet with a few rows showing your source data and a sheet with the result you expect to get is more than enough. Thank you.

              3. Thank you for the update, Victor.

                If you enter time without a date, Google Sheets treats these times as from 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.

  48. We need formula for if working hours is 6 and 1 person working 6.5 hours and we need to show he worked 0.5 hour extra (fixed time 10:00AM to 04:00PM) (Work time 10:00 AM to 4:30:00PM) actual extra work 0.5 hour

    what is the formula for that

  49. Hello Natalia,

    I use google sheets to do wages and I need a bit of help to sort an issue.

    Regular work hours are 9am to 6pm (important to remember) and I'm trying to subtract overtime hours or minutes that my employees work.

    For example, Start time is (7am) and the finish time is (9pm), I'd then like it to separate in another cell the 2 overtime hours from the starting point (7am-9am) and the 3 overtime hours from the finish point (6pm-9pm)

    Look forward to hearing back from you.

    1. Hello Jordan,

      Assuming your start time is in A2 and end time is in B2, try these IF formulas in C2 and D2:

      • for C2:
        =IFS(OR(A2<TIME(9,0,0),A2>TIME(9,0,0)),TIME(9,0,0)-A2,A2=TIME(9,0,0),"")
      • for D2:
        =IFS(OR(B2>TIME(18,0,0),B2<TIME(18,0,0)),B2-TIME(18,0,0),B2=TIME(18,0,0),"")

      1. Hey Natalia, thank you for getting back to me.

        So far the C2 format is working and I'm assuming that you missed out a comma in this area [A2TIME] but I have replaced it with [A2,TIME] and it's working.

        For the D2 format, I am getting an error message, see the error message below.
        [Wrong number of arguments to IFS. Expected at least 2 arguments, but received 1 arguments.]

        1. Hi Jordan,

          My apologies, the formulas appeared broken due to > and < symbols. I've edited the formulas, please take a look now.

        2. I have also just noticed, that when I add the start time 10am. In C2 I get [23:00:00]. Is there any way to make it 00:00:00 if the start time was anytime after 9am?

          1. At the moment, the formulas calculate the negative number if the start time is after 9am. But if you'd rather see 00:00:00, then you need to write out each condition and the required result in the formula:
            =IFS(A2<TIME(9,0,0),TIME(9,0,0)-A2,A2=TIME(9,0,0),"",A2>TIME(9,0,0),TIME(0,0,0))

            1. Hiya Natalia,

              I'm not sure what I even wrote in the last reply, I'm confused myself lol.

              So at the moment with the new [FORMAT] you provided, whenever I type anything after 9am it says 00:00:00 which is correct and id now like another format to do the same for when I write a time that is before 18:00:00 it should say 00:00:00 and it would only count the time after 18:00:00.

              [FORMAT] you provided earlier:
              =IFS(A2TIME(9,0,0),TIME(0,0,0))

              Another note, not a huge issue, so when using the [FORMAT] and typing a time for example 10:00:00, it says 00:00:00. Is it possible to just have it blank using the '' ''?

              1. I am trying to type the format you sent me on (May 19, 2022 at 6:49 am) Spain time.

              2. Hi Jordan,

                For when the time is before 18, the drill is the same: you need to put that as a pair of "condition, it result" in the formula. Just like I did with 9, but only with 18 this time:
                =IFS(A2<TIME(18,0,0),TIME(0,0,0),...)

                If you prefer empty cells rather than 00:00, yes, feel free to use "" instead.
                =IFS(A2<TIME(18,0,0),TIME(0,0,0),...)

                Btw, I still suggest you look through this blog post about the IF function :) You just may find the answers to your questions there much faster than I can reply :)

              3. Its done it again, let me try another font:
                [=̶I̶F̶S̶(̶A̶2̶̶T̶I̶M̶E̶(̶9̶,̶0̶,̶0̶)̶,̶T̶I̶M̶E̶(̶0̶,̶0̶,̶0̶)̶)̶]

              4. I can see the confusion now, I'm trying to type the full [FORMAT] in the reply but for some reason it changed and doesn't post the hole thing?

                Hopefully this works, this is what I'm trying to type:
                =???(??????(?,?,?),????(?,?,?))

            2. Thank you ever so much for the help you have provided so far.

              This format:
              =IFS(A2TIME(9,0,0),TIME(0,0,0))

              What would the format be for a time before 6pm?

              1. You're most welcome, Jordan :)

                As for time before 6pm – if you'd like to add a condition for when the start time is before 6, just add the same part you quoted to the formula but change 9 to 6. If you mean you'd like to add conditions for the end time as well, please specify what you'd like to return if it's before/after/equals 6.

  50. I typed a start time colum and an end time colum via the 13:25 format (the date is on a separate colum): I don't understand how to calculate the subtraction : meaning the differnce between them.
    How can I do that please?

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