Age formula: Year / Month / Day

[Bbro1035]

Hello Everyone,

I am working on an excel sheet for a birthday calendar to show how old someone is in Years, Months, and Days.

Right now for testing purposes I am using consecutive days. Photo shows the consecutive days.

This is the Formula I have so far, but I cant seem to get the days to be correct. Is there a way of subtracting all of the days prior to the current month?

=DATEDIF(A1,NOW(),"y") & " Y " & DATEDIF(A1,NOW(),"ym") & " M " & DATEDIF(A1,NOW(),"yd") & " D "

For example today is:
August 13, 2021

I would like:
July 13, 2021 to show "0 Y 1 M 0 D"
July 14, 2021 to show "0 Y 0 M 30 D"

In the end I would like to have a list of birthdays showing Y M D, and Also to be able to take all of the birthday and put them into a calendar automatically if anyone has done this before also.

Thank you,
Bob

 

[rollis13]

Have a try with "md" (Difference in days, ignoring months and years) instead of "yd" (Difference in days, ignoring years):

=DATEDIF(A1;NOW();"y") & " Y " & DATEDIF(A1;NOW();"ym") & " M " & DATEDIF(A1;NOW();"md") & " D "
or even:
=DATEDIF(A1;TODAY();"y") & " Y " & DATEDIF(A1;TODAY();"ym") & " M " & DATEDIF(A1;TODAY();"md") & " D "

 

[Bbro1035]

Hello Rollis13,

Thank you for your help. I did try your formula but I was getting an error with it. But it did help me figure out that I had yd and it should have been md.

original

=DATEDIF(A2,NOW(),"y") & " Y " & DATEDIF(A2,NOW(),"ym") & " M " & DATEDIF(A2,NOW(),"yd") & " D "

new "Works Now" by changing from "yd" to "md"

=DATEDIF(A1,NOW(),"y") & " Y " & DATEDIF(A1,NOW(),"ym") & " M " & DATEDIF(A1,NOW(),"md") & " D "

Example now that it works!!

Oct / 14 / 2014	6 Y 9 M 30 D
Apr / 18 / 2015	6 Y 3 M 26 D
Apr / 23 / 2015	6 Y 3 M 21 D
Aug / 25 / 2016	4 Y 11 M 19 D
Mar / 25 / 2019	2 Y 4 M 19 D
Feb / 27 / 2021	0 Y 5 M 17 D
Jul / 01 / 2021	0 Y 1 M 12 D
Jul / 13 / 2021	0 Y 1 M 0 D
Jul / 14 / 2021	0 Y 0 M 30 D
Aug / 12 / 2021	0 Y 0 M 1 D

 

[rollis13]

Sorry, my mistake, forgot to translate ; with , .
Glad having been of some help.

 

[Bbro1035]

no problem at all. I've been looking at my formula for years and could not figure it, I started a family history sheet a while back and never could figure this out. I just found this forum this morning, and you were super quick to respond, so much appreciated!!