Formula

Sonia.DomGon

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

Kelly Moore

Hey @Sonia.DomGon

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

Kelly Moore

Hey @Sonia.DomGon

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

Sonia.DomGon

This works great! Much appreciated your help Keely :)