Excel formula wanted please

SexyGreyFox · 10 Feb 2009 at 16:38

C'mon lads make me look good in front of my bosses again.

I want to take somebody's birthday eg 29/05/1958 and turn that into showing their age from the date when I open up the spreadsheet (perhaps in years & months but years is OK).

If there is a certain way to input the DOB then also mention that please.

Thanks

swinnie · 10 Feb 2009 at 16:39

This what you're after? http://www.pcreview.co.uk/forums/thread-1748534.php

ricky1981 · 10 Feb 2009 at 17:19

here you go mate

=DATEDIF(A1,TODAY(),"Y") & " years and " & DATEDIF(A1,TODAY(),"YM") & " months"

just enter DOB as dd/mm/yy and excel should format it correctly automatically

SexyGreyFox · 10 Feb 2009 at 17:36

ricky1981 said:
here you go mate

=DATEDIF(A1,TODAY(),"Y") & " years and " & DATEDIF(A1,TODAY(),"YM") & " months"

just enter DOB as dd/mm/yy and excel should format it correctly automatically

I've entered the date into A1 as 29/05/58 and up came up as 29/05/1958.
(I then changed the format so it read like the first one)

I've put the formula into A2 and B1 but it doesn't want to work for me.

Help

ricky1981 · 10 Feb 2009 at 17:40

what does it come up with? (error or just the wrong thing)

I just did this in excel 2k7 and it worked, the only thing I did differently was not changing the date format although that shouldnt make a difference as excel will just convert to numbers anyway

suarve · 10 Feb 2009 at 17:44

dmpoole said:
I've entered the date into A1 as 29/05/58 and up came up as 29/05/1958.
(I then changed the format so it read like the first one)

I've put the formula into A2 and B1 but it doesn't want to work for me.

Help

Don't see how it can't work as its a very very simple forumula (A quick google shows the googled page he got the forumula from: http://www.ozgrid.com/Excel/calculate-age.htm).

It's most likely your date format (i left mine as default - *14/03/2001 in excel 2007, although having the format as *14/03/01 works just the same).

ricky1981 · 10 Feb 2009 at 17:46

I didn't find that link, found a similar one and had to mess about with it! Oh well, it killed 5 minutes at work, only 15 to go

mark66 · 10 Feb 2009 at 17:46

I have just tested here and it works fine, nice formula

One thing I found that may be your problem... On my first attempt I copied the formula from above and pasted directly into B1. This made the cell blank and did not work. I then deleted the column and tried again, but first pressed F2 so I was editing the cell B1 and then pasted the formula in. Then it worked fine.

SexyGreyFox · 10 Feb 2009 at 22:43

mark66 said:
I have just tested here and it works fine, nice formula

One thing I found that may be your problem... On my first attempt I copied the formula from above and pasted directly into B1. This made the cell blank and did not work. I then deleted the column and tried again, but first pressed F2 so I was editing the cell B1 and then pasted the formula in. Then it worked fine.

Since I was copy and pasting from the above it was in white.
I just changed the font to black and it was there

THANK YOU VERY MUCH

SexyGreyFox · 11 Feb 2009 at 11:20

More help please.

Once again a massive thank you for your input because this will totally enhance my spreadsheets and others in the Skills Academy.
eg I deal with mainly 14 to 16 year olds and their age depends on where I can place them and when.

Next question -
My column with the formula shows 109y 1m if I don't input any data.
How do I hide that cell until I input data?

Thanks

mark66 · 11 Feb 2009 at 11:37

Hi,

I am not sure if this is the best way, but you could place the existing formula into another IF statement, checking that the value of your date if greater than zero. (Dates in Excel are stored as numbers starting from 01/01/1900, so this date is 1)

=IF(A1> 0, DATEDIF(A1,TODAY(),"Y") & " years and " & DATEDIF(A1,TODAY(),"YM") & " months","")

SexyGreyFox · 11 Feb 2009 at 16:43

mark66 said:
Hi,

I am not sure if this is the best way, but you could place the existing formula into another IF statement, checking that the value of your date if greater than zero. (Dates in Excel are stored as numbers starting from 01/01/1900, so this date is 1)

=IF(A1> 0, DATEDIF(A1,TODAY(),"Y") & " years and " & DATEDIF(A1,TODAY(),"YM") & " months","")

Brilliant - works a treat but I have shortened it a bit so it reads eg 22y 7m

Thanks once again

mark66 · 11 Feb 2009 at 17:04

Glad you have sorted it and the thanks should be directed at Ricky1981, he came up with the original sound formula