Formula for DOB and date range

Vinton Douglas

Hello I have a dropdown to select date of birth. How can I create a formula to ensure that it corresponds to a date range?

0-4 years

5-11 years

12-15 years

16-17 years

18-29 years

30-39 years

Aya

I see.. Can you try this instead?

=IFERROR(IF([Age @ Time of Admission]@row >= 80, "80-up", IF([Age @ Time of Admission]@row >= 70, "70-79", IF([Age @ Time of Admission]@row >= 60, "60-69", IF([Age @ Time of Admission]@row >= 50, "50-59", IF([Age @ Time of Admission]@row >= 40, "40-49", IF([Age @ Time of Admission]@row >= 30, "30-39", IF([Age @ Time of Admission]@row >= 18, "18-29", IF([Age @ Time of Admission]@row >= 16, "16-17", IF([Age @ Time of Admission]@row >= 12, "12-15", IF([Age @ Time of Admission]@row >= 5, "5-11", IF([Age @ Time of Admission]@row <= 4, "0-4"))))))))))), "")

I added some ranges which you can freely remove. Just make sure you remove the appropriate closing parenthesis as well. 🙂

Aya

Hi Vinton,

You may try this and let me know if it works for you:

=IFERROR(IF((YEAR(TODAY()) - YEAR([Column7]@row)) >= 40, "40 & up years", IF((YEAR(TODAY()) - YEAR([Column7]@row)) >= 30, "30-39 years", IF((YEAR(TODAY()) - YEAR([Column7]@row)) >= 18, "18-29 years", IF((YEAR(TODAY()) - YEAR([Column7]@row)) >= 16, "16-17 years", IF((YEAR(TODAY()) - YEAR([Column7]@row)) >= 12, "12-15 years", IF((YEAR(TODAY()) - YEAR([Column7]@row)) >= 5, "5-11 years", IF((YEAR(TODAY()) - YEAR([Column7]@row)) <= 4, "0-4 years"))))))),"")

Note: Change [Column7] to your column for the "date of birth" .

Vinton Douglas

Hi Aya,

Thanks much for your assistance. I tried the formula but perhaps I didn't explain well my desired outcome or I may not be inputting it correctly. See attached 2 columns in which I need the formula to be in the green highlighted one, so that when the true age is inputted a range is automatically displayed in the green column

Date Range.png

reflecting the range.

Aya

I see.. Can you try this instead?

=IFERROR(IF([Age @ Time of Admission]@row >= 80, "80-up", IF([Age @ Time of Admission]@row >= 70, "70-79", IF([Age @ Time of Admission]@row >= 60, "60-69", IF([Age @ Time of Admission]@row >= 50, "50-59", IF([Age @ Time of Admission]@row >= 40, "40-49", IF([Age @ Time of Admission]@row >= 30, "30-39", IF([Age @ Time of Admission]@row >= 18, "18-29", IF([Age @ Time of Admission]@row >= 16, "16-17", IF([Age @ Time of Admission]@row >= 12, "12-15", IF([Age @ Time of Admission]@row >= 5, "5-11", IF([Age @ Time of Admission]@row <= 4, "0-4"))))))))))), "")

I added some ranges which you can freely remove. Just make sure you remove the appropriate closing parenthesis as well. 🙂

Vinton Douglas

Thanks much. Works great!

lmed80

Hello @Aya - I found this thread and tried the formula but am getting an UNPARSEABLE error. Can anyone check my formula to see where the error is? Thank you!!

=IFERROR(IF([Client Age]@row) >= 11, "6-10 hours", IF([Client Age]@row) <= 4, "30-40 hours", IF([Client Age]@row = 5, "20-30 hours", IF([Client Age]@row) = 6, "20-30 hours", IF([Client Age]@row) = 7, "12-15 hours", IF([Client Age]@row) = 8, "12-15 hours", IF([Client Age]@row) = 9, "8-12 hours", IF([Client Age]@row) = 10, "8-12 hours")), "")

Also, I have a Client DOB column that is used to populate the Client Age column, so if the Client DOB is blank, the Client Age column shows "INVALID DATA TYPE". How can I adjust this column to just leave it blank if the Client DOB is blank?

=(YEAR(TODAY()) - YEAR([Client DOB]@row) - IF(AND(MONTH(TODAY()) <= MONTH([Client DOB]@row), DAY(TODAY()) <= DAY([Client DOB]@row)), 1))

THANK YOU!

Genevieve P.

Hi @lmed80

It looks like you're closing off the IF statements early - we don't want to have a ) after the cell reference, but instead we want that at the very end of the formula.

For example:

IF([Client Age]@row) >= 11,

should be

IF([Client Age]@row >= 11,

Try:

=IFERROR(IF([Client Age]@row >= 11, "6-10 hours", IF([Client Age]@row <= 4, "30-40 hours", IF([Client Age]@row = 5, "20-30 hours", IF([Client Age]@row = 6, "20-30 hours", IF([Client Age]@row = 7, "12-15 hours", IF([Client Age]@row = 8, "12-15 hours", IF([Client Age]@row = 9, "8-12 hours", IF([Client Age]@row) = 10, "8-12 hours")))))))), "")

Then for your second formula, you can add an IF statement in the beginning:

=IF([Client DOB]@row = "", "", (YEAR(TODAY()) - YEAR([Client DOB]@row) - IF(AND(MONTH(TODAY()) <= MONTH([Client DOB]@row), DAY(TODAY()) <= DAY([Client DOB]@row)), 1)))

Cheers,

Genevieve