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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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......
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top