Calculating Age Range

[grifter]

In one column AH2 I have:

=LEFT(YEARFRAC(AG2,TODAY(),1),2)

Which gives me age in years from the date of birth

Then in next column I have:

=IF(AH2>80,"80+",IF(AND(AH2<81,AH2>64),"65-80","Under 65"))

And for every age it says the range it's in is 80+, even though the ages are in various different ranges

Is it something to do with performing a check in a field with a function in it that is causing the wrong age range or am I missing something obvious in my logic?

G

 

[pwbrown]

There is nothing wrong with :

=IF(AH2>80,"80+",IF(AND(AH2<81,AH2>64),"65-80","Under 65"))

What if you remove any formula in AH2 and just type a number does it work?

Edit: It does work if you just enter a number, the problem is with:

=LEFT(YEARFRAC(AG2,TODAY(),1),2)

I propose you use:

=DATEDIF(AG2,TODAY(),"Y")

instead of:
=LEFT(YEARFRAC(AG2,TODAY(),1),2)

 

[grifter]

Found the problem. Should have had quotes round the logic check value:

=IF(AH2>"80","80+",IF(AND(AH2<"81",AH2>"64"),"65-80","Under 65"))

 

[grifter]

There is nothing wrong with :

=IF(AH2>80,"80+",IF(AND(AH2<81,AH2>64),"65-80","Under 65"))

What if you remove any formula in AH2 and just type a number does it work?

I didn't try that but I tried copying the values into another column and all the values age values changed to 11?

Edit: It does work if you just enter a number, the problem is with:

=LEFT(YEARFRAC(AG2,TODAY(),1),2)

I propose you use:

=DATEDIF(AG2,TODAY(),"Y")

instead of:
=LEFT(YEARFRAC(AG2,TODAY(),1),2)

Can you explain why use what you have proposed instead, what is the issue?

 

[pwbrown]

Found the problem. Should have had quotes round the logic check value:

=IF(AH2>"80","80+",IF(AND(AH2<"81",AH2>"64"),"65-80","Under 65"))

Your original formula was correct, this is not. As it's numbers you're dealing with, it should not have quotes. Quotes are mainly used to represent text.

I didn't try that but I tried copying the values into another column and all the values age values changed to 11?




Can you explain why use what you have proposed instead, what is the issue?

Better it's far better having more functionality and flexibility for future use with the data. It's the forumla of choice for most people doing similar tasks as yours. You want the data type to be a number but it's most likely to be general or text now.

 

[Brianwarnock]

Is Datedif documented in the latest release of Excel, I know that it was documented in 95 or 97 but then disappeared although it continued to work. I believe that it existed for compatibility with Lotus 123

Brian

 

[pwbrown]

Is Datedif documented in the latest release of Excel, I know that it was documented in 95 or 97 but then disappeared although it continued to work. I believe that it existed for compatibility with Lotus 123

Brian

Was it not documented in 2000 as well? Perhaps not but yes, it's not documented any more but it still works in every excel version.
Not sure why it's not documented though, seems odd.

 

[Brianwarnock]

I'm guessing that Microsoft don't want us to see how poor their functions are compared to Lotus.

Brian

 

[Brianwarnock]

Grifter

I realised that I had not addressed your issues.
The reason you needed to put " around your logic tests was because Left returns a Text string, not really what you want, you should have used Int instead of Left, and in the bigger picture you would have problems with ages < 10 and >99.

Perhaps more of an issue is that Yearfrac is not accurate around birthdays as it works on a set number of days in the year, you chose 360 for some reason ,but even 365 is not correct, and even the use of 365.25 if it had been available does not always return the correct answer, Datedif does.

If you do not want to use an undocumented function you will be forced to use a more complex formila to calculate the age such as

=IF(ISBLANK(AG2)," ",SUM(YEAR(TODAY())-YEAR(AG2)-IF(MONTH(TODAY())>MONTH(AG2),0,IF(MONTH(TODAY())<MONTH(AG2),1,IF(DAY(TODAY())<DAY(AG2),1,0)))))

Brian

 

[grifter]

Thanks for all the advice I am nowing using datedif() - I can see it doesn't come up in the list in Excel when you type until you put the first bracket in 'datedif(' so initially thought it wasn't in the user functions for Excel but noted it was said that it was still supported.

 

[Brianwarnock]

Grifter

since you have decided to use Datedif you might like to keep this workbook which contains the Excel help entry from Excel 2000 or 97

Brian

 

[grifter]

Grifter

since you have decided to use Datedif you might like to keep this workbook which contains the Excel help entry from Excel 2000 or 97

Brian

Thanks for that Brian very much appreciated.


G