How to combine IF, TODAY, OR, ISBLANK functions?



I am having trouble putting together a formula for showing when my employees have been certified or not. Here is the formula I am using

=IF((ISBLANK([Completion Date ]@row), "šŸ”“ Not Certified"), IF((TODAY() < [SH Next Training Due Date]@row, "šŸŸ¢ Certified", "šŸ”“ Expired")))

When an employee completes their training I enter the Completion Date, that date is also used for another formula to calculate when the certification expires SH Next Training Due Date (in 2 years from completion date).

What I want is when I enter the Completion Date, which in turn tells me when the Next Training Due Date is, that as long as it is not past the next training due date/expiration date it returns "šŸŸ¢ Certified". When the Next Training Due Date is today or in the past, have the formula return "šŸ”“ Expired". But when the Completion Date cell is blank, I want it to return as "šŸ”“ Not Certified".

I dont know which formulas to use in order to achieve those results. I have already tried multiple ways. Currently i am getting an error message that says "the column formula syntax is not quite right"

Any help would be very appreciated thank you!

Best Answer

  • Dan W
    Dan W āœ­āœ­āœ­āœ­āœ­
    Answer āœ“

    Give this a try

    =IF(ISBLANK([Completion Date]@row), "šŸ”“ Not Certified", IF(TODAY() < [SH Next Training Due Date]@row, "šŸŸ¢ Certified", "šŸ”“ Expired"))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!