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

or smarter way:

=INDEX(IF(ISDATE_STRICT(A2:A), TRIM(FLATTEN(QUERY(TRANSPOSE(
 IFERROR(LAMBDA(a, LAMBDA(x, IF(x=0,,IF(x>1, x&a&"s", x&a)))
 (DATEDIF(A2:A, B2:B, {"Y", "YM", "MD"})))({" year", " month", " day"}))),,9^9))), ))