MULTIPLE IF STATEMENT

Hi - Could anyone help me again please?

I got help with the formula above and it worked fine but then i've tried to adapt it to cover another scenario in a different date column - so it first looks at the start date column and if that date is not yet due then the column says not yet valid - if the date is today or past due then the formula above kicks in.

I have formulas for bothn working but when i stick them together (as i would have worked them out in Excel) then i get an error message again

Below is both fomulas and how i have tried to join them.


=IF([VALID FROM DATE]@row > TODAY(), "NOT YET VALID", "ADD NEXT PART OF FORMULA HERE")

=IF(AND([VALID TO DATE]@row < TODAY(30), [VALID TO DATE]@row > TODAY()), "EXPIRING", IF([VALID TO DATE]@row >= TODAY(), "CURRENT", "EXPIRED"))

=IF([VALID FROM DATE]@row > TODAY(), "NOT YET VALID”, IF(AND([VALID TO DATE]@row < TODAY(30), [VALID TO DATE]@row > TODAY()), "EXPIRING", IF([VALID TO DATE]@row >= TODAY(), "CURRENT", "EXPIRED")))

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    The error looks to be caused by the ” at the end of the NOT YET VALID portion, if you change this over to " it should work:

    =IF([VALID FROM DATE]@row > TODAY(), "NOT YET VALID", IF(AND([VALID TO DATE]@row < TODAY(30), [VALID TO DATE]@row > TODAY()), "EXPIRING", IF([VALID TO DATE]@row >= TODAY(), "CURRENT", "EXPIRED")))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!