Formula for DOB and date range

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

Best Answer

  • Aya
    Aya ✭✭✭
    Answer ✓

    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. 🙂

Answers

  • Aya
    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
    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

    reflecting the range.

  • Aya
    Aya ✭✭✭
    Answer ✓

    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
    Vinton Douglas ✭✭✭

    Thanks much. Works great!

  • lmed80
    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!