Calculating Age in years vs months!

[MyxB2]

I have used the below formula to calculate the age in years in my current spreadsheet form. However, I would like to display the age properly in months IF the person is less than 1 year old.

=ROUNDDOWN((B11-B8)/365,0)

B8 = DOB
B11= current date

The above result returns 0 for the age if someone is less than 1 yr old!

I have searched & tried extensively but cannot get any of the DateIF formulas that I have seen to work.

Any help is appreciated

 

[Dave Patton]

try the following

=DATEDIF(B8,B11,"y")& " years and "&DATEDIF(B8,B11,"ym")&" months"

or

=IF(B11-B8>365,INT((B11-B8)/365),INT((B11-B8)/30)&" months")

N.B. The above gives approximate results.

You did not advise what you wish to do with leap years and how exact the month calculation should be.

 

[Gerald Higgins]

Hi, welcome to the board.

Try this

=if((B11-B8)< 365,ROUNDDOWN((B11-B8)/30,0),ROUNDDOWN((B11-B8)/365,0))

Comments
1) May not deal correctly with leap years
2) Uses 30 days as an approximation of a month, which is obviously not completely correct.

 

[MyxB2]

WOW, that was fast & really worked pretty well.

However this is a form & I would like to show after the number, only "Yr" if >= 1 & show only "M" if < 1 year old.

The whole word is OK if that is not possible BUT having just the number of years OR the number of months is the priority.

I have been working on an If, then version of my orginal formula & dividing by 12 instead of 365 to show months because the simplified Rounddown version works but I cannot get the syntax to work & I know it will not display the words "Yr" or "M" in my attempts!

I have used many of the ideas on this form for a couple of years & I truly appreciate the efforts & results I see on this site!!

 

[MyxB2]

Just fantastic from both Dave & Gerald. I modified the DateIF & abbreviated the YR & M so that will probably work.

If the database manager whom uses the form doesn't like that, I will just change the form over to the If,THEN version & just add the input requirement via a list to delineate YR vs M. Which MAY make the form more database friendly.

THANKS ALL!!