Age formula

davidfn

New Member
Joined
Mar 14, 2010
Messages
8
Hi

I'm trying to find a formula to work out someones Age, based on 2 dates - dob and date case opened. I've found a number of options but they all seem to have a limitation, so want to check if there are any straight forward solutions. The formula needs to work out ages that include children less than 1 years old by returning a zero, and generaly round down.

I also need to find a formula that does not return a zero (or value 110) when a row is blank (i.e. does not have dob and case opened dates entered) or return an error message such as #value. This is because I have a summary page which summarises numbers of people between age bands and these results stop this from happening. I can manage a returned value of 110 as I can alter the adding up formula (e.g. count ages between 50 and 99, instead of ages >50), but I cannot manage the other results. However, I would rather not have to change the adding up formula at all, unless I really have to.

Thank you in advance for any help offered.

Nick
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you have DOB in A2 and case open date in B2 try like this

=IF(COUNT(A2,B2)=2,DATEDIF(A2,B2,"y"),"")
 

davidfn

New Member
Joined
Mar 14, 2010
Messages
8
Hi

thanks for your help. Works fine. I'm working at home on excel 2007 but need to use it on excel 2003 - by the look of the equation, albeit with limit knowledge of these things, it appears it would work on this too.

All the best and thanks again

Nick
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Yes, it'll work in both versions. DATEDIF isn't very well documented (it's not in the help files or the functions list for those versions), but you can use it nonetheless......
 

davidfn

New Member
Joined
Mar 14, 2010
Messages
8
Hi

testing the formula above in excel 2007. Found that if I cut and paste a DOB into another cell, the cell reference numbers in the age formula follow the DOB. This happens even if I password protect the cells containing the formula. Its not a problem in that I can give instructions not to cut and paste cells! And its a small price to pay to over come the other problems. However, would be good to know why this happens, especially if there is an easy way to stop it. I've not tested it in windows 2003 - so it might not happen in that version.

I looked in the excel options menu to see if its a case of changing the way excel works, but cannot find anything. I'm used to this happening when formula move, but not the cells that formula use to perform a calculation!

Any suggestions?

Thanks

Nick
 

Forum statistics

Threads
1,085,333
Messages
5,383,021
Members
401,812
Latest member
topherj09

Some videos you may like

This Week's Hot Topics

Top