Convert Birthday from MM/DD/YY to Age 2

[JockVSJock]

Doing a report for my boss and right now we are showing the
age of a person like this:

8/17/73

My boss informed me legally we can't do this, it has to be in a format like this:

3005

30 means the person is 30 years old and 5 months.

Is there anyway in Excel to create some sort of formual that will convert from 8/17/73 to 3005?

thanks

 

[Chopstik]

There is a datedif function in Excel that should enable you to get the formula you need. An example:

in cell A1, you input the birthday.
in cell B1, you can calculate the number of years first by datedif(A1,Now(),"Y&quot
in cell C1, you can calculate the number of months using datedif(A1,Now(),"YM&quot.
in cell D1, you concatenate the results from B1 and C1.

This is just a simplified example but it should give you an idea of how to best accomplish what you need.

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[Bowers74]

To understand more about the DATEDIF() function, take a look at the below FAQ, and look for the DATEDIF() syntax and examples:

faq68-4037 - What are some of Excel's date functions?

I would use Chopsick's solution, but I would put it all into one formula instead of using 3 cells:

=DATEDIF(A1,NOW(),"Y&quot&IF(LEN(DATEDIF(A1,NOW(),"YM&quot=2),"0"&DATEDIF(A1,NOW(),"YM&quot,DATEDIF(A1,NOW(),"YM&quot)

Will get you what you need! ;-)





Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Chopstik]

Bowers74,

Chopsick?

Much more elegant solution! A star for you! And I'll have to remember to look in the FAQ section more often... Thanks for the assist.

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

 

[Bowers74]

Thanks for the

!

I'm glad I could help!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[TonyJollans]

Rather than checking the length of the "YM" element (and invoking the function twice), I would do this ..

=DATEDIF(A1,NOW(),"Y")&RIGHT("0" & DATEDIF(A1,NOW(),"YM"),2)

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Bowers74]

Tony - KUDOS for the logic!

Have a

from me, because I would have never though of that! ;-)



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[TonyJollans]

Thanks, Mike,

It's something I do quite a lot to pad values with zeroes .. and every little bit helps with performance

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[JockVSJock]

Thanks everyone for your help.

All of your answers are right on target!!!

THANKS!!!