Formula to auto generate age group based on age

Not great at Excel as I am only learning but I have a list of patients, their DOB and their age which is a formula.

I want to create a formula that will select a pre-determined age group based on the age.
The age groups are as follows 0 - 4, 5 - 17, 18 - 64, 65+
Excel .jpg

Be grateful for any help.

The best way to do this is to create a table of the age groups and use VLOOKUP to look up the group. You did not attach your actual file so I am attaching one showing the technique.

(See yellow banner at top of page. An actual file is almost always better than a picture.)

.
One formula that can be pasted in H2 and copied down Col H :

Please Login or Register  to view this content.

Awesome thanks - couldn't get it to upload. I'll just have to hide it way over where nobody can find the table and delete it.

Is it possible to use the VLOOKUP with dates.

Say for example we had a patient referral come in and we want to calculate wait time (0-12wks, 12-24wks, 24-39wks, 39-52wks, 52+wks) based on either the referral date or contact date (which ever is the latest date).

You can see on the excel sheet the referral date and then a good few cells over Date of contact/appointment and the wait time.

Originally Posted by Karenmc

we want to calculate wait time[...]based on either the referral date or contact date (which ever is the latest date).

What is the start date and what is the end date? It looks like you use the same cell for both contact date and appointment date.

How do you want to account for partial weeks?

The idea is that you will do the same kind of thing. It's more complicated because you have to determine the start and end dates, and then calculate a number of weeks.

As an example, it might look something like this:

=VLOOKUP(INT(MAX(ReferralDate, ContactDate)-StartDate)/7),'Wait Time Table'!$A$1:$B$5,2,TRUE)

This uses the number of whole weeks (ignores partial week).

The start date would be the date the referral comes in.

The end date would be when we have made contact/appointment for them as we either do a virtual or give them an appointment.

Whole weeks are fine. Its just to give an approx. wait time in order to see where we may need to put on more clinics

Originally Posted by Karenmc

The start date would be the date the referral comes in.

The end date would be when we have made contact/appointment for them as we either do a virtual or give them an appointment.

I'll use that definition though it doesn't match the earlier one.

based on either the referral date or contact date (which ever is the latest date).

If the end date is contact or appointment, it doesn't matter which one since the same cell is used for both.

I put the results in AA with some examples.

Thanks a mill - my only issue is that it won't count up the weeks if there is no date in the appointment/contact cell.

So ideally for that I would want to use just the referral date for those so it shows the weeks.