How to work out age from DOB?

[cheoksoon]

Hi ladies and gents,

I have a huge list (700k) of names with their date of birth attached to it and I want to convert them to an "age". ie. 4/05/1958 => age. How do I do this?

And after converting their DOB to age, I would like to then group them into several age groups ie. age 8 - 12, 13 - 18, 19- 35, etc.

Can anybody show me how ?

Thanks.

[zbor]

Write

Please Login or Register  to view this content.

and extend formula down

[zbor]

After that do something like:

Please Login or Register  to view this content.

Add more criteria only keep it in ascending order

[cheoksoon]

Hi Zbor, that formula didn't work. the formula just returned 12/02/1900, 26/02/1900 etc. They returned dates of all 1900.

[sktneer]

Format the formula cell as General and you will get the desired result. This is all because the Excel stores dates as serial numbers and 1 represent the date 01/01/1900. To see this input 1 in a cell and format that cell as Short Date and cell will display 01/01/1900.

Hope this helps.

[pareshj]

Hi cheoksoon,

Check the attached file and hope it will help you.

cheoksoon.xlsx


Note: Please edit formula for age range as per your requirement.

Regards,
Paresh J

[dubcap01]

and add some age groupings for charting purposes: