Difference between two dates expressed as years, months, days (in one column)

4
votes

I have a set of columns set up in Google Sheets that display the difference between two dates using the DATEDIF function:

=DATEDIF (AS2, TODAY(), "D")

(number of days passed today since a certain date)

=DATEDIF (AR11, AS11, "D")

(number of days passed between two certain dates)

The values are represented as a number (of days). This is fine for shorter durations, but for something like 987 days I would like to display the value into something more intuitive, such as:

| 2 Years, 8 months, 17 days |

If that is not possible within the same column, I would at least like to have a set of three columns that display this time duration in three separate value types:

| 2 Years | 8 months | 17 days |

Just changing the value type for each column (from days to months for example) would, of course, be simple enough, but I'm not sure how to proceed in keeping the values displayed in relation to each other (and not just have the same value be displayed in different duration types).

Any suggestions, please?

4
dategoogle-sheetsformattinggoogle-sheets-formulaarray-formulas
Probably you should use an established library to do this, as manual calculations will probably have issues with leap years, timezone shifts, and so on. Especially since months have variable lengths. - tehhowch
Yeah, because months have variable lengths, two periods both professing to be 2 Years, 8 months and 17 days may not be of equal length. Are you okay with that being so? (Unless you apply some other fudge factor like all months being 30 days long) - Damien_The_Unbeliever

4 Answers

4
votes
=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)"))

0

1
votes
=IF(                 DATEDIF(A2, B2, "D")> 365, 
      SPLIT(QUOTIENT(DATEDIF(A2, B2, "D"), 365)&" "&
        QUOTIENT(MOD(DATEDIF(A2, B2, "D"), 365), 30)&" "&
    MOD(QUOTIENT(MOD(DATEDIF(A2, B2, "D"), 365), 30), 30), " "),
 IF(                 DATEDIF(A2, B2, "D")> 30, 
 {"", SPLIT(QUOTIENT(DATEDIF(A2, B2, "D"), 30)&" "&
                 MOD(DATEDIF(A2, B2, "D"), 30), " ")},
            {"", "", DATEDIF(A2, B2, "D")}))

0

demo spreadsheet

1
votes

I will interpret your question as requiring an answer in complete years, complete calendar months and any remaining days. This should be fairly straightforward, except where the month containing the start date has more days then the month before the month containing the end date*.

Example:

Start Date End Date Result
28/1/19    1/3/19   1 month and 1 day
29/1/19    1/3/19   1 month and 1 day
30/1/19    1/3/19   1 month and 1 day
31/1/19    1/3/19   1 month and 1 day

If you accept this, then the following formulas should work:

Year

=datedif(A1,B1,"Y")

Month

=mod(datedif(A1,B1,"m"),12)

Day

=IF(DAY(B1)>=DAY(A1),DAY(B1)-DAY(A1),DAY(EOMONTH(B1,-1))+DAY(B1)-MIN(DAY(A1),DAY(EOMONTH(B1,-1))))

enter image description here

EDIT

*Have checked this on this website and found that it makes the same assumption - you get the same duration (1 month and one day) for 28/1/19 to 1/3/19 as 31/1/19 to 1/3/19 although the total number of days (32 or 29) is different.

Possible workaround is to take the days remaining in the start month

=IF(DAY(B1)>=DAY(A1),DAY(B1)-DAY(A1),DAY(EOMONTH(A1,0))-DAY(A1)+DAY(B1))

which seems to agree with this website

You can also use the MD argument to Datedif:

=datedif(A1,B1,"MD")

But in both Google Sheets and Excel this can produce a negative number as warned in the Excel documentation:

The "MD" argument may result in a negative number, a zero, or an inaccurate result. If you are trying to calculate the remaining days after the last completed month...

enter image description here

0
votes

This is based on already having a field with days difference, in this case field A1 (eg. converts number of days to YMD)

=FLOOR(A1/365)&"y "&FLOOR((A1-(FLOOR(A1/365)*365))/30)&"m "&CEILING(A1-((FLOOR((A1-(FLOOR(A1/365)*365))/30)*30)+((FLOOR(A1/365))*365)))&"d"

It's based on a simplified 365-day year, 30-day month calculation, so not perfectly accurate - however this seems to be the method others have used. It also rounds the days up to full days for my example - this could be rounded down with FLOOR instead of CEILING

enter image description here