I am trying to do multiple if statements in a formula but can't seem to get it right!

Options

Here is my formula.

=IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, "[Assessment Date Booked]@row+730", IF([Assessment Date Booked]@row < [Deadline 2]@row, "[Assessment Date Booked]@row+365")))

What it is returning is [Assessment date booked]@row + 730 but I want it to add to years to the date in assessment date booked!

Can someone help?

Best Answers

  • AaronAtWork
    AaronAtWork ✭✭
    Answer ✓
    Options

    Try this and be sure that your column types are Dates:

    =IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, [Assessment Date Booked]@row + 730, IF([Assessment Date Booked]@row < [Deadline 2]@row, [Assessment Date Booked]@row + 365, "")))

    This revised formula checks:

    1. If [Assessment Date Booked]@row is greater than [Deadline 2]@row, it returns "N/A".
    2. If [Assessment Date Booked]@row is less than [Deadline 1]@row, it adds 730 days to [Assessment Date Booked]@row.
    3. If [Assessment Date Booked]@row is less than [Deadline 2]@row but not less than [Deadline 1]@row, it adds 365 days to [Assessment Date Booked]@row.

    If none of these conditions are met, it returns an empty string by default.

    Shoutout to ChaptGPT for the assist~

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @ionam,

    Looks like you removed the wrong character here:

    "Assessment Date Booked]@row+730

    Remove the " at the beginning and put the [ in its place.

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • AaronAtWork
    Options

    The quotes around "[Assessment date booked]@row + 730" makes it a string of text. Simply remove the quotes.

  • ionam
    Options

    That leaves me with

    =IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, "Assessment Date Booked]@row+730, IF([Assessment Date Booked]@row < [Deadline 2]@row, [Assessment Date Booked]@row+365)))

    which results in #UNPARSEABLE

  • AaronAtWork
    AaronAtWork ✭✭
    Answer ✓
    Options

    Try this and be sure that your column types are Dates:

    =IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, [Assessment Date Booked]@row + 730, IF([Assessment Date Booked]@row < [Deadline 2]@row, [Assessment Date Booked]@row + 365, "")))

    This revised formula checks:

    1. If [Assessment Date Booked]@row is greater than [Deadline 2]@row, it returns "N/A".
    2. If [Assessment Date Booked]@row is less than [Deadline 1]@row, it adds 730 days to [Assessment Date Booked]@row.
    3. If [Assessment Date Booked]@row is less than [Deadline 2]@row but not less than [Deadline 1]@row, it adds 365 days to [Assessment Date Booked]@row.

    If none of these conditions are met, it returns an empty string by default.

    Shoutout to ChaptGPT for the assist~

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @ionam,

    Looks like you removed the wrong character here:

    "Assessment Date Booked]@row+730

    Remove the " at the beginning and put the [ in its place.

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • ionam
    Options

    @AaronAtWork and @bisaacs

    Thanks for this.

    I have been at this tracker all day and you have really helped me out here. Time to hang it up for the weekend I think.

    Have a good one and much appreciation for your help here.

    ☺️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!