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 > More Formats > 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, substitute 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: substitute 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 > More Formats > More date and time formats. 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 substitute 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 substitute 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.

Table of contents

353 comments

  1. Hi Natalia, hope you're doing well!!
    I have a cell with a data just like 18/08/2023 11:12:00 (Cell FM3) and another cell with the numer of working hours = 48 (Cell FN3). In another sheet i have a list with the holidays troughout the year, and i consider 08h and 30 minutos as working hours per day.
    Is there any how to calculate de date + 48 working hours, considering 8h and 30 minutes per day? I've tried everything but i couldn't get by....

    Thanks!!

      • Hello, Natalia! Sorry if I couldn't be clear enough. I would like a formula for Google Sheets that adds 48 working hours to the FM3 cell that contains "18/08/2023 11:12:00". For example, if we consider 48 running hours, the formula would return "20/08/2023 11:12:00", but I would like to specify working hours, where working hours for me are from 08:00 to 12:00, and from 1:30 pm until 6:00 pm. (12:00 - 13:00pm lunch time). In other words, the total number of working hours per day, from 8:00 am to 6 pm, is 8:30 (8h 30min). I've done a lot of research, but you're the only person on the entire internet who seems to be able to accomplish this formula! Thanks for replying!

        • Thank you for additional explanation, John! Could you please share a sample table with me (support@apps4gs.com)? Understanding how your data is arranged will help me find the most suitable solution for you.

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

  2. Hi just want to ask what to do here because I have tried the formula for the other cells but it wont get the total number of hours. for example a total number of worked hours in one month is 240 hours, I have input 12 hours each day, for example I put 12 hours for 5 days and one day for 9 hours, and when you total it it will show negative sum. together with other. but the other cells are accurate

    • Hi claire,

      In what format do you enter those hours? How is your cell with the resulting formula formatted? What formula exactly are you trying to use?

  3. Hello, I am wanting to create a time sheet for some fellow employees but I cannot find a formula that shows how to get the total hours worked from doing the clock in, out, in, out for their lunch breaks (example: in 6:45 am, out 1:20 pm, in 2:05 pm, out 5:20 pm = 9 hours and 50 minutes worked) I cannot get it to equal the time total worked with any formula I have tried. Please let me know if there is a way to do that.

  4. Hi there,
    I'm trying to efficiently figure out my employees hours worked and pay for those hours. So I have figured out how to subtract time started from time ended and then how do I translate that time- back to hours so I can multiply that by their hourly pay. For example:
    Employee 1: Clock In- 16:15 Clock Out- 21:30 and get 5:15 then I try to multiply this number by $18/hr and am getting a wacky number. any advice??

  5. Natalia, firstly you are a wizard when it comes to this! Thanks for your help in advance!

    Here's the layout of my timesheet (that gets fed from the form)
    Timestamp Name Email Your Position Date Completed Time Started Time Completed Hours worked
    2/6/2024 17:06:06 sample sample 2/5/2024 3:45:00 PM 5:00:00 PM 1.15

    I'm trying to have the sheet calculate the hours worked working with the AM PM time - is that possible?

  6. Hi Natalia, I am building a google sheets that allow me to arrange schedule for employees.

    After we key in time in and time out, I already make it to be able to auto calculation for us to count the total working hours. But besides that, I need your help on this:

    If the working hours is more than 7.5hours, need to minus one hour break time.
    If the working hours is less than 7.5hours, no need to minus any break time.

    How do I do that? Kindly assist.

    Thank you so much.

  7. Hi There,

    I am building a sheet that will allow me to calculate a "premium" on an employees hourly rate based on the time of day. For example, depending on the time of day, the employee may make an additional 10% or 20% premium. The goal of the grid I am building, is to allow a user to enter someone's start time, have the remaining hours auto populate (using the time function to add an hour to each previous hour) and then use an IF AND function to check whether each hour is within a certain "premium" window and then apply said premium to their hourly.

    I am using the following formula to auto populate the hours, adding one hour to the previous hour: =B5+TIMEVALUE("01:00:00") Note: I have also used the TIME function interchangeably to no avail.

    I am using the following formula to evaluate the hour and determine which premium, if any, to apply to the hourly rate: =IF(AND($B6=TIMEVALUE("07:00:00")),$B$3*1,IF(AND($B6>=TIMEVALUE("20:00:00"),$B6=TIMEVALUE("00:00:00"),$B6<TIMEVALUE("07:00:00")),$B$3*1.2,"FALSE")))

    Unfortunately, when the hours cross over from prior to midnight to after midnight (i.e. 11pm to 1am), the time evaluation formula does not detect any of the hours as falling between the 12:00am to 7:00am window, which should apply a 20% premium.

    Any idea's how to adjust either formula to allow for this?

    • Hi Zach,

      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 with your formula (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. Do not email there. Once you share the file, just confirm by replying to this comment.

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

      • Thanks Natalia! I have shared a sample sheet.

        • Thank you for creating a sample, Zach!

          I entered another formula that works in the copy of the sheet, please take a look:
          =IF(AND(HOUR($B5)<20,HOUR($B5)>=7),$B$3*1,IF(AND(HOUR($B5)>=20,HOUR($B5)<=23),$B$3*1.1,IF(AND(HOUR($B5)>=0,HOUR($B5)<7),$B$3*1.2,"FALSE")))

          It looks like TIMEVALUE still accounts for dates (though its stated in its Help that dates are ignored) because your formula works as soon as I clear B5 and no time units seem to fall to "another day" (with 00:00) anymore. Anyways, the HOUR function solves this problem.

  8. Hi Natalia

    I'm trying to get the duration of 2 cells showing an event's start time and end time, but I want the format to be in days, hours, and minutes. for example the duration should be displayed as follows: 2 days 4 hours 34 minutes. What formula can I use?

    • Hi Rose,
      Please try this formula:
      =IF(B2>A2,CONCATENATE(DATEDIF(A2,B2,"D")&" days ",TEXT(MOD(B2-A2,1),"hh")," hours ",TEXT(MOD(B2-A2,1/24),"mm")," minutes"),"")

      • I have tried to use the formula you gave above, however, the formula keeps giving me 12 minutes for all the differences in time. I've tried to copy the example below so you can understand.

        17/01/2024 12:20 18/01/2024 08:11 1 days 19 hours 12 minutes
        18/01/2024 09:34 18/01/2024 10:49 0 days 01 hours 12 minutes
        18/01/2024 11:24 18/01/2024 11:29 0 days 00 hours 12 minutes
        18/01/2024 11:42 18/01/2024 12:04 0 days 00 hours 12 minutes
        18/01/2024 12:06 18/01/2024 13:59 0 days 01 hours 12 minutes
        18/01/2024 11:59 18/01/2024 14:01 0 days 02 hours 12 minutes
        18/01/2024 14:35 18/01/2024 14:39 0 days 00 hours 12 minutes
        18/01/2024 16:36 18/01/2024 16:38 0 days 00 hours 12 minutes
        18/01/2024 00:06 19/01/2024 09:07 1 days 09 hours 12 minutes

        for the example above, I used the formula below. I'm not sure if I'm writing it wrongly or otherwise. Please kindly advise

        =IF(H60>G60,CONCATENATE(DATEDIF(G60,H60,"D")&" days ",TEXT(MOD(H60-G60,1),"hh")," hours ",TEXT(MOD(H4-G4,1/24),"mm")," minutes"),"")

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

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

      • 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

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

          • 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

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

              • πŸ‘yes this is perfect thank you for the super good help and expertise
                I appreciate this

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

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

    • 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!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    • 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!

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

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

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

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

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

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

  24. Its not working in mobile app

      • 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

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

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

    • 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.
  26. 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 =?

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

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

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

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

    • 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;
      }
      }
      }

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

    • 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)"

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

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

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

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

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

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

  34. no TIME function found, this is old

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

  36. Thank you very much! Really appreciated!

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

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

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

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

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

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