=IFERROR formula


Within my project plan sheets I have 2 columns , On Time/Late and RAG.

On Time / Late Column Formula

=IFERROR(IF(AND([Original vs Current (days diff)]@row > 8, Status@row = "Complete", Milestones@row = "true"), "Late", IF(AND([Original vs Current (days diff)]@row <= 7, Status@row = "Complete", Milestones@row = "true"), "On Time")), "")

RAG Formula

=IFERROR(IF(Hierarchy3 > 1, IF([Current Due Date]3 - [Original Due Date]3 < 8, "Green", IF([Current Due Date]3 - [Original Due Date]3 <= 14, "Yellow", "Red"))), "")

Green with 7 days of original due date when task marked completed = On Time

Amber within 8-14 days of original due date task marked completed= Late

Red 15 days over original due date task marked completed = Late

I can't seem to get the Amber to show 'Late' in the On Time / Late column, when it's between 8-14 days over, and the task is marked completed. Any obvious reasons in my formula, tried to another another if statement.



Best Answer

  • Lindsay Wilson
    Answer ✓

    @Paul Newcome Thanks for your reply.

    I was about to solve it now - switched the 8 and 7 in the first formula for 'On Time / Late' to be the other way about and it's worked!


    =IFERROR(IF(AND([Original vs Current (days diff)]@row > 7, Status@row = "Complete", Milestones@row = "true"), "Late", IF(AND([Original vs Current (days diff)]@row <= 8, Status@row = "Complete", Milestones@row = "true"), "On Time")), "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!