# Formula

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

under 20 yrs

20-24

25-29

30-34

34-39

40-44

45-49

50-54

55-59

60 yrs and over

Thank you

• ✭✭✭✭✭✭

A nested IF is one approach

IFs stop at the first true statement the formula encounters so the sequence of the IFs are important

=IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<20, "Under 20 yrs old", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<24, "20-24", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<29, "25-29", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<34, "30-34", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<39, "35-39", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<40, "40-44", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<49, "45-49", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<54, "50-54", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<60, "55-59", "60+")))))))))

I called your column [Date of Birth] in the formula above. If this is not correct, you must change each instance within the formula to match your actual column name (sometimes it's easier to copy the formula into WORD and do a global REPLACEALL then paste that into smartsheet).

I have the formula display a blank cell if the [Date of Birth] causes an error, such as a non-date entered in the date cell. If you want a different action let me know and we can change the action.

Will this work for you?

Kelly

• ✭✭✭✭✭✭

A nested IF is one approach

IFs stop at the first true statement the formula encounters so the sequence of the IFs are important

=IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<20, "Under 20 yrs old", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<24, "20-24", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<29, "25-29", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<34, "30-34", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<39, "35-39", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<40, "40-44", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<49, "45-49", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<54, "50-54", IF(YEAR(TODAY())-IFERROR(YEAR([Date of Birth]@row),"")<60, "55-59", "60+")))))))))

I called your column [Date of Birth] in the formula above. If this is not correct, you must change each instance within the formula to match your actual column name (sometimes it's easier to copy the formula into WORD and do a global REPLACEALL then paste that into smartsheet).

I have the formula display a blank cell if the [Date of Birth] causes an error, such as a non-date entered in the date cell. If you want a different action let me know and we can change the action.

Will this work for you?

Kelly

• This works great! Much appreciated your help Keely :)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!