Calculating age in Excel (1 Viewer)

[BobNTN]

Could some tell me why I get 26,000+ years from this formula using a date of 11/25/1947 in cell a14 ?
Months and days 'seem' to work. I am trying to calculate years, months, and days from a birthdate and using Today(). I am trying the long way to use in both Excel and Open Office and OO does not recognize Datedif.
Thanks

=(TODAY()-A14)-IF(OR(MONTH(TODAY())<MONTH(A14),AND(MONTH(TODAY())=MONTH(A14), DAY(TODAY())<DAY(A14))),1,0)&" years, "&MONTH(TODAY())-MONTH(A14)+IF(AND(MONTH(TODAY())<=MONTH(A14),DAY(TODAY())<DAY(A14)),11,IF(AND(MONTH(TODAY())<MONTH(A14),DAY(TODAY())>=DAY(A14)),12,IF(AND(MONTH(TODAY())>MONTH(A14),DAY(TODAY())<DAY(A14)),-1)))&" months,"&TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY())-IF(DAY(TODAY())<DAY(A14),1,0),DAY(A14))&" days"

 

[Brianwarnock]

Why do you want to use the same technique in both software, I've never used OO but in Access I would have written a UDF, and as you know Datedif I would use that in Excel.

Brian

 

[Brianwarnock]

Oh BTW the error in your formula is it should start

=(YEAR(TODAY())-YEAR(A14))-

Plus when I copied it over I got a typo flagged up, I couldn't see it, not surprised really, but accepted the unseen correction and it was ok.

Brian

 

[Rx_]

If there is one not working, be sure to set the Excel Range to Date data type.

There is no perfect formula becaue of zeros, the need for comma or other interesting aspects of converting numeric data (dates are numeric) into a string. This interesting formula will display the commas in some cases where they shouldn't be. In general it should work
A8 is the Birth Day B8 holds the formula = NOW()

=IF(DATEDIF(A8,B8,"y")=0,"",IF(DATEDIF(A8,B8,"y")=1, DATEDIF(A8,B8,"y")&"year",DATEDIF(A8,B8,"y")&" years")) &IF(AND(DATEDIF(A8,B8,"y")<>0,DATEDIF(A8,B8,"ym")<>0),", ","") &IF(DATEDIF(A8,B8,"ym")=0,"",IF(DATEDIF(A8,B8,"ym")=1, DATEDIF(A8,B8,"ym")&" month",DATEDIF(A8,B8,"ym")&" months")) &IF(AND(OR(DATEDIF(A8,B8,"y")<>0,DATEDIF(A8,B8,"ym")<>0), DATEDIF(A8,B8,"md")<>0),", ","")&IF(DATEDIF(A8,B8,"md")=0,"", IF(DATEDIF(A8,B8,"md")=1,DATEDIF(A8,B8,"md")&" day", DATEDIF(A8,B8,"md")&" days"))

My date returnsin this format 71 years, 10 months, 16 days

Sorry, didn't see that it was suppose to be one forumula for both Excel and Open Office. That makes me wonder if Open Office might reference the Excel object model? Nope, evidently it isn't open enough to reference other object models.
That will make it dificult for sure.

 

[Brianwarnock]

It is a long time since I did this, 10 years retired, but don't you just need three Datedif using "Y" "YM" "MD" although I may need to check the codes.

I will switch to my laptop from my iPad after I have finished watching the Tour De France.

Brian

 

[Brianwarnock]

=DATEDIF(A4,TODAY(),"Y") &" Years, "&DATEDIF(A4,TODAY(),"ym") &" Months, " &DATEDIF(A4,TODAY(),"md") &" Days"

Produces the same results as Bob's code.

Don't see how you can use the identical formula in both Excel and a DB , definitely in Access the functions and addressing will be different.

Brian

 

[BobNTN]

Open Office does NOT recognize DateDif.
I have the Excel sheet working with DateDiff but will not work in Open Office. Since I am temporarily using both plus I share some spreadsheets with friends and family using OO, I was trying to change it to the long drawn out way but kept getting 26635 years and could not figure out why.

The missing YEAR part at the front was the problem.

Thanks guys.
You are the best.

 

[Brianwarnock]

You were getting the number of days not years. It pays to do a test with a short date span , say 1 year, 2 months 3 days as things can be easily calculated manually and errors spotted.

Brian

 

[Brianwarnock]

Datedif is an old Lotus function Microsoft provided for compatibility ages ago but stopped showing it in help after Excel 97 but still permit it to work. It is superior to Datediff.
I was saying that other functions also are different, therefore the actual formula code will differ, so why not use the appropriate technique which I believe is a UDF. for Access and suspect the same for OO.

But perhaps I misunderstand your requirements.

Brian

 

[BobNTN]

Yeah, I thought I had it and dumb me copied it over all my original formulas with DATEDIF then realized I had something wrong. The YEAR was the missing part.

Thanks so much.

 

[BobNTN]

Datedif is an old Lotus function Microsoft provided for compatibility ages ago but stopped showing it in help after Excel 97 but still permit it to work. It is superior to Datediff.
I was saying that other functions also are different, therefore the actual formula code will differ, so why not use the appropriate technique which I believe is a UDF. for Access and suspect the same for OO.

But perhaps I misunderstand your requirements.

Brian

I am converting some of my excels to Open Office CALC (spreadsheet) part.
Sorry I failed to state that.

 

[Brianwarnock]

A simple mistake and they can be the hardest to spot, happy to have helped.

Brian