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

Question

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?

Answer 1

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

Answer 2

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

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