Comments on: Calculate days, months and years between two dates in Google Sheets: DATEDIF formulas

Today's blog post is all about figuring out the difference between two dates in Google Sheets. Come find lots of DATEDIF formulas to count days, months and years, and learn how NETWORKDAYS is used to count only workdays even if your holidays are based on a custom schedule. Continue reading →

Table of contents

Comments page 2. Total comments: 91

  1. Hello, maybe someone can help.
    if I set a start day (let's say 09 Aug 2022).
    In the next column, I set qty of work days = 1
    The end date should be calculated automatically (I use WORKDAY formula)
    WORKDAY return to me the end date is 10 Aug 2022
    But task should be finished in the same date as we started 09 Aug 2022

    On the 10 Aug 2022, the next task should be started and the previous task shouldn't be related to 10 Aug 2022

    1. Hello Vadym,

      Actually, this is the way the WORKDAY function works. It adds additional working days (1 in your case) to the start date. So there will always be this 1 day jump.

      The best way to utilize this function for your task is to set the qty as a number of extra days you'd like to add on top of the first day (0 in your case).

  2. hi. I would like to calculate let say 20 days after the certain date. I am looking to set up a formula. thanks

    1. Hello JES,

      Supposing your date is in A2, the following formula will do the trick:
      =A2+20
      Make sure to format the resulting cell as a Date.

  3. I am receiving different results for the same data range and formula

    I have a start and end date that are the same and I am using this formula
    =if(S16="","",NETWORKDAYS(J16,S16,Holidays!$B$2:$B$14)-1)

    J16 = 1/19/2022
    S16 = 1/19/2022
    Results in 0

    Note: If I remove the -1 at the end, it gives me a 1 but the difference between the dates is 0

    I also have the same formula for another date range and its giving me a -1

    =if(S17="","",NETWORKDAYS(J17,S17,Holidays!$B$2:$B$14)-1)

    J17 = 1/23/2022
    S17 = 1/23/2022
    Results in -1

    Snip of Holiday tab
    Holiday Capital One Observed Date
    New Year's Day 2022 12/31/2021
    Dr. Martin Luther King Jr. Day 1/17/2022
    Presidents' Day 2/21/2022

    Please help, I can't figure out the discrepancy

      1. Hi Natalia,

        I have the same concern, hope you can share the formula. Looking forward to your response.

        1. Hi Leslie,

          Undortunately, Waahida has never shared a spreadsheet so I don't really understand the problem.

          You can share your file with us as well (support@apps4gs.com), identifying the issue so I know where to look. 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.

          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.

  4. How can calculate the AVG days/months between multiple dates?

    1. Hello Perry,

      Have you tried the AVERAGE function? If it doesn't work as you expect, please describe your task in detail and provide examples of your source records and of the expected result.

  5. Hi Natalia,

    Hoping to calculate months between two dates but looking for the ability to count half months. so if we start on Oct. 15, 2021 and end on January, 31, 2021, would like to see 3.5 as the calculation result. Any solutions?

    Thanks!

    1. Hi Dontae,

      You can try something like this to get the more precise fraction results:
      =(end_date - start_date)/(365/12)

  6. Question
    If I have a networkdays formula that shows me the working days and I want to further break that down to show me working days per each month in the date range that is being calculated what formula would I use ?

    Got example if the date range is
    July 14,2021 to March 15,2022 and I use network days to figure out the number of working days based on a Monday to Friday
    How can I break that’d down further to give me the number of days worked in July,August,sept ,oct etc

    1. Hello Gwen,

      The first two arguments of the formula are start_date and end_date. These are the fields where you should indicate the exact periods (months).

  7. I am trying to use DATEDIF to calculate number of months worked . The reference cells for start and end dates are formulas. Even though the formula cells and the input cells are formatted as dates the DATEDIF function returns an error saying they are text....how to resolve this please?

  8. Hello! Which number format does the result cell need to be? I've tried the majority and the output is still 0. Can you help? Thanks!

    1. Hello Lisa,

      The Number format should do the trick. If it doesn't work, consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) and specify the cells to look into. 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.

  9. hello i want to put 2 formulas in one cell e.g

    DUEDATE PAID ON
    10Jan. 15jan

    latefee =(15jan,10jan,”D” now i want to multiplye result with 100 ????
    please help

    1. Hello Ankit,

      If I get it correctly, a formula like this should help:
      =DATEDIF("01/10/2021","01/15/2021","D")*100

  10. Hi Natalia

    I am setting up a Google Sheet designed to track project duration, and need a formula to calculate how long a project took to complete.

    For example:

    Project Start: 30/3/21 at 12:00pm
    Project End: 7/4/21 at 5:00pm

    I would like a connected cell to autofill with the HOURS taken to complete the project (excluding weekends).

    The duration is 148 hours (197- 48)

    Assuming Start Date is B2 and End Date is C2, how can I get D2 to calculate the 148 correctly?

    This article goes some way to explain the process, but not quite all of it.

    Is there a one-line formula that will solve this query?

    Thanks
    Harvey

    1. Hi Harvey,

      Try this formula:
      =(NETWORKDAYS(IF(WEEKDAY(B2,2)>5,WORKDAY(B2,1)+1/3,B2),C2)-1+MOD(C2,1)-MOD(IF(WEEKDAY(B2,2)>5,WORKDAY(B2,1)+1/3,B2),1))*24

      1. This worked perfectly. Thank you!

        One small glitch - cells to which I've embedded the formula autofill to "-56:00" (negative 56 hours)... But it's not a big issue. Thanks so much.

        1. Harvey,

          negative hours may appear if you accidentally change start and end dates places or reference wrong cells. If you'd like, you could share your spreadsheet with us (support@apps4gs.com), we'd look into it as well.

          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.

  11. Hi!
    I'd like to calculate how long a task has been pending, so I have a start date, and I want the "end date" to be today, so I can automatically calculate how many months have been spent on a project. Is there a way to use DATEDIF to do that? Thanks!

      1. This is very Helpful; thank you. When creating this spreadsheet however, any column without a start date shows that the task has been pending for 44,375 days. How can I have the spreadsheet formatted for future entries to calculate without creating this unusable data?

          1. Thank you!!

  12. Hi
    I have invoice date and due date. I can use the formula to calculate the remaining days before due date but how can i stop the calculation or it return as "0" once the column remarks show PAID (manually insert)? Any formula i can combine or do u have some ideas?

    Thanks. Appreciate your help

    1. Hi Hirda,

      If I understand your task correctly, you can use the IF function to check the status first. If it's something other than 'paid', calculate the number of remaining days. If it's paid, return 0 or something else to your liking.

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