3

I have two dates: 29/05/2020 and today.

How can I get a date diff of these 2 dates, in a meaningful format such as "Z years, X months, Y days" separating these dates?

Ideally this should also take into account the number of days in each months (for example May has 31 days but June has only 30 days) and an additional requirement: I need to subtract 21 days to this duration, because there were 21 days between these 2 dates that "don't count".

Improve this question

2 Answers 2

Reset to default
1

I would suggest that you look into the NETWORKDAYS.INTL function, which allows you to find the difference between two dates accounting for standard, customized weekend days off and for any additional list of holidays or days off. For instance, assuming your past date were in cell A1:

=NETWORKDAYS.INTL(A1,TODAY(),"0000001", DaysOff!A2:A)

The plain-English translation here is "Find the workdays that happened between the two dates, given that every Sunday was a day off and additionally excluding any dates listed in DaysOff!A2:A."

The string "0000001" defines what your regular weekend-days-off are, starting with Monday.

0 = "not counted as a weekly day off" 1 = "counted as a weekly day off"

Then you'd just setup a sheet (I suggest DaysOff! here) where you list any additional holidays and such that should not be counted in the total day count between the dates but which are not regular weekly occurrences.

Improve this answer
1

The process starts with:

=DATEDIF(start_date, end_date, unit)

Reference

Given my locale (France), I use semicolons not commas to define the fields so:

enter image description here

which as you see returns a value of 142.

So you then need to extend the DATEDIF to include the expression below using nested DATEDIF's to achieve your objective (using cell references for extend usability).

=IF(DATEDIF(A1, B1, "D")>365, QUOTIENT(DATEDIF(A1, B1, "D"), 365)&" year(s) "&
                      QUOTIENT(MOD(DATEDIF(A1, B1, "D"), 365), 30)&" month(s) "&
                  MOD(QUOTIENT(MOD(DATEDIF(A1, B1, "D"), 365), 30), 30)&" day(s)",
 IF(DATEDIF(A1, B1, "D")>30,  QUOTIENT(DATEDIF(A1, B1, "D"), 30)&" month(s) "&
                               MOD(DATEDIF(A1, B1, "D"), 30)&" day(s)", 
                                   DATEDIF(A1, B1, "D")&" day(s)"))`

Reference 2

Improve this answer
2
  • Thanks so much! If I may, there's another requirement that I forgot about. I need to substract 21 days to this duration, because there were 21 days between these 2 dates that "don't count". So essentially the end result shouldn't be "4 months and 23 days" but "4 months and 2 days". Is there a way to add a field like "Days to discount" and change the end result by subtracting that?
    – user256515
    Oct 19, 2020 at 10:55
  • 1
    Yes, of course... if you set the dates in two fields as suggested, =date(2020;5;29)-21 which gives a date of 8th May 2020 in the second field (ie the date to) would do it. If this addresses your question, please consider upvoting the answer and marking it as the accepted answer (grey tick). This makes it easier for others with a similar question to find the answer. Thanks
    – Low Rep Mods are bad
    Oct 19, 2020 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.