Formula for Late Audits


I ma trying to create a formula to display "Planned", "On TIme" or "Late" depending on the following criteria:

If Date Completed is blank and Date Scheduled is greater than "Today" - Planned

If Date Completed is earlier than or equal to Date Scheduled - On Time

If Date Completed is Blank and Scheduled Date is Less Than "Today" - Late

This is my formula that is not working:

=IF(AND(ISBLANK([Date Completed]@row),([Scheduled Date]@row > TODAY ,"Planned"))), IF([Date Completed]@row <= [Scheduled Date]@row), "On Time", IF(AND(ISBLANK([Date Completed]@row),([Scheduled Date]@row < TODAY, "Late")))

Any suggestions?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!