How to find the difference between two dates, and display it as (**y ***d)

[PistachioPedro]

Hello, is there a way to do this in one cell? For example I have two dates which I want to calculate the days between: 23/07/2012 and 16/11/2014.

Subtracting the later date from the earlier date gives me 846 days, is there a way that I could then format this to show the result as "2y 116d"?

The only way I can imagine with my excel skills is to create a column for Years, and a column for Days, subtracting X years * 365. Even with that process it will not work due to leap years occurring, and it would be ugly to have them in 2 separate columns. Does anybody have any ideas on this one?

Thanks!

[SDCh]

Read this:
Supressing Zero Value Components
http://www.cpearson.com/excel/datedif.aspx

[PistachioPedro]

Thank you, that link is great, I'd never heard of DatedIF before. However it doesn't seem to be working correctly..

I have the two dates listed in my previous post, and the formula returns 2y 115d which seems correct, however I've put the date I was born in and today's date, and it's showing 55d. My birthday is this month so it should show 335+ days. Could this be the reason it is not documented, because it is buggy?

This is the formula I am using:

=DATEDIF(A1,B1,"y")&"y "&DATEDIF(A1,B1,"yd")&"d "

[SDCh]

Did you read this part and try it?

Calculating Age
You can use the DATEDIF to calculate a person's age. For example, the formula below will calculate the age of a person as of the current date, where BirthDate is the person's date of birth.
=DATEDIF(BirthDate,TODAY(),"y")&" years "&DATEDIF(BirthDate,TODAY(),"ym")&" months "&DATEDIF(BirthDate,TODAY(),"md")&" days"

Note: Because you not upload the example, I can't check your calculation.

[SDCh]

Sorry, may be your calculation not correct because the leap year as explain on this part "DATEDIF And Leap Years"

[sktneer]

Alternatively you can use

Please Login or Register  to view this content.

[PistachioPedro]

=DATEDIF(BirthDate,TODAY(),"y")&" years "&DATEDIF(BirthDate,TODAY(),"ym")&" months "&DATEDIF(BirthDate,TODAY(),"md")&" days"

I did try this already and I got a #REF! error but I really can't figure out why.

Alternatively you can use

Please Login or Register  to view this content.

This one almost works, however it adds in separately all the leap years I have lived through, making me 24y 6d on my upcoming birthday.


I have attached a sample sheet to show my formulas. My original dates in this thread are showing up perfectly, but with the same formula my age isn't. I am truly baffled here.


EDIT: I've just noticed that if you change the date in B3 to 01/02/2014, it shows 1y 190d. If you change it to 01/05/2014, it also shows 1y 190d. I really think this function doesn't work.. Or maybe my formula is somehow wrong?

[FDibbins]

With your 1st (earlier) date in A1 and the later date in B1, try this...

=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

[PistachioPedro]

FDibbins, thank you, that works however I'm wanting to eliminate the months. I would like it to only count the years and days.

[sktneer]

More precisely you can use this too......

Please Login or Register  to view this content.

[PistachioPedro]

Ahhh! Thank you so much, this revision works perfectly.

[FDibbins]

Ok try this then...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&DATEDIF(A1,B1,"yd")&" days"

[sktneer]

Glad to help you. Thanks for the feedback.

[PistachioPedro]

Ok try this then...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&DATEDIF(A1,B1,"yd")&" days"

This one gave me the exact same result as I had posted I was using. I am convinced that DATEDIF is poorly coded or something and that's why it's not used. It seems to work when you use the year and month portion, but not the days portion.

[FDibbins]

A21=7/23/2012
A22= 11/16/2014
2 years 116 days
=IF(DATEDIF(A21,A22,"y")=0,"",DATEDIF(A21,A22,"y")&" years ")&DATEDIF(A21,A22,"yd")&" days"