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

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?

Antinucleon

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

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

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

Ardellardella

February is 28 (or 29!) days long, not 30. Many months are 31 days. – Joslyn

@Joslyn that is true but irrelevant. if you say "2 months ago" you don't usually mean 59 days ago just because of jan31+feb28. you mean 2 months as a standard unit of 30-day average – Ardellardella

The problem with "months ago" and "years ago" is they are inherently ambiguous. "2 months ago" could mean "sometime in january" or "January 18th." So if you are so specific as to also calculate the number of days in your difference, you certainly imply that you are doing the month calculation properly (read: exactly) – Joslyn

Fantastic, beautyfully formated and it works. A bit of a pain to adapt, and my version of sheets wanted semi-colon instead of comas, but it works! – Nonconformance

For me it's not working for values more than a year, @user2274903 version is working as expected instead. – Branch

@Branch see: https://mcmap.net/q/139103/-difference-between-two-dates-in-years-days-hours-minutes-format-duplicate – Ardellardella

I found issue on this formula. If B1 and A1 is same month in difference year it returned 0 day(s) instead of day diff. – Ress

@Ress see: https://mcmap.net/q/138763/-how-can-i-make-correct-formula-for-age-diff-in-google-sheet-duplicate from: https://mcmap.net/q/138155/-ultimate-short-custom-number-formatting-k-m-b-t-etc-q-d-googol where you can do: i.stack.imgur.com/YgxUP.png – Ardellardella

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

Stridulate

Tried it, the day formula does not seem to be working, it returns a negative value. – Antinucleon

Sorry, an obvious typo, the first part of the if statement should have been DAY(B1)-DAY(A1) (edited). – Stridulate

Been finding this answer for a long time. The only answer that considers the number of days per month. Added a time variable in your original formula. Linking my modified answer – Gleam

@TomSharpe I followed the Excel documentation link and tried to reproduce the negative/incorrect bug, but it looks like MD in google sheets works correctly. did I miss something (?) docs.google.com/spreadsheets/d/… – Ardellardella

I'm not sure, for 31/1/19 to 1/3/19 datedif(a1,b1,"MD") in GS and Excel gives -2 which might be regarded as having some sort of logic because of the number of days in February, but I don't think it's very helpful and it doesn't agree with either of the websites quoted. – Stridulate

Answer 3

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

demo spreadsheet

Ardellardella

First formula works, however apparently since it does not return a simple number value it breaks the conditional formatting I've been applying to that column (something that I've omitted to say I'm also doing). I have tried using the second formula (the one I'm replying to) to use separate columns but it does not seem to work - I'm getting errors. Also, I'm not sure if the formula above should be for the Years column (as it looks in the screenshot) or not. If it is only intended for the Years column, what parameters should I be modifying for the month/day columns? – Antinucleon

you dont need to modify anything... its auto-populated. here, I made you a spreadsheet where you can examine it properly: docs.google.com/spreadsheets/d/… – Ardellardella

Answer 4

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

Chamblee

see: https://mcmap.net/q/138763/-how-can-i-make-correct-formula-for-age-diff-in-google-sheet-duplicate – Ardellardella

Answer 5

The 'Recommended Answer' uses manual calculation i.e assumes 365 days in a year & 30 days in a month. To get more precise answer use this formula in Google Sheets:

=ARRAYFORMULA(DATEDIF(B2:B13,C2:C13,"Y")&" years "& DATEDIF(B2:B13,C2:C13,"YM")&" months "& DATEDIF(B2:B13,C2:C13,"MD")&" days")

If you want to use without the Arrayformula:

=DATEDIF(B2,C2,"Y")&" years " & DATEDIF(B2,C2,"YM")&" months " & DATEDIF(B2,C2,"MD")&" days"

Column B: Start Date

Column C: End Date

Thanks

Beverleebeverley

see: https://mcmap.net/q/138763/-how-can-i-make-correct-formula-for-age-diff-in-google-sheet-duplicate – Ardellardella

Answer 6

For my use case, I want to keep track of the tenure of my employees (i.e., the number of years, months, and days since their start date), so my example calculates the diff between their start date (H2 in my example) and today. NOTE: You can adjust the TODAY() call to reference another cell instead.

=CONCATENATE(DATEDIF(H2, TODAY(), "Y"), "y", " ", MOD(DATEDIF(H2, TODAY(), "M"), 12), "m", " ", MOD(DAY(EOMONTH(H2, 0)) - DAY(H2) + DAY(TODAY()), DAY(EOMONTH(H2, 0))), "d")

My solution can be broken down into three parts:

Value	Calculation	Details
Year	DATEDIF(H2, TODAY(), "Y")	Simple DATEDIF of years.
Month	MOD(DATEDIF(H2, TODAY(), "M"), 12)	Since there is a constant 12 months each year, we can do a DATEDIF of months and modulus that number by 12.
Day	MOD(DAY(EOMONTH(H2, 0)) - DAY(H2) + DAY(TODAY()), DAY(EOMONTH(H2, 0)))	First, we want to calculate the number of days for the rest of the month of the start date. Second, we add the number of days into the month of the end date. Lastly, we modulus that value by the number of days in the month of the start date.

The rest is formatting. An example output would be 1y 6m 15d

Polyzoarium

see: https://mcmap.net/q/138763/-how-can-i-make-correct-formula-for-age-diff-in-google-sheet-duplicate – Ardellardella

Answer 7

The answer of player0 have issue when dates are in the same month and difference year. I added the condition for fix it, it may be better.

=IF(
DATEDIF(A11, today(), "D") > 365,
QUOTIENT(DATEDIF(A11, today(), "D"), 365) & " year(s) " &
    IF(
        QUOTIENT(MOD(DATEDIF(A11, today(), "D"), 365), 30) = 0,
        MOD(MOD(DATEDIF(A11, today(), "D"), 365), 30) & " day(s)",
        QUOTIENT(MOD(DATEDIF(A11, today(), "D"), 365), 30) & " month(s) " &
            MOD(QUOTIENT(MOD(DATEDIF(A11, today(), "D"), 365), 30), 30) & " day(s)"
    ),
    IF(
        DATEDIF(A11, today(), "D") > 30,
        QUOTIENT(DATEDIF(A11, today(), "D"), 30) & " month(s) " &
        MOD(DATEDIF(A11, today(), "D"), 30) & " day(s)",
        DATEDIF(A11, today(), "D") & " day(s)"
    )
)

Ress