# Formula for Late Audits

Options
✭✭✭

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?

• ✭✭✭✭✭
Options

I think you need to change Today to Today() in your IF statement.

Regards - JC

• ✭✭✭✭✭
Options

Hi @Duane B

Problem seems to come from your parenthesis. Try this:

=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")))

Hope it helped!

• ✭✭✭✭✭
Options

This works using Today() instead of Today.

Regards - JC

• ✭✭✭✭✭
Options

Thanks for the update. Obviously the "Late" cannot trigger in my formula because of the ISBLANK function in the first statement. I just went straight with @Duane B wishes and didn't pay attention.

A better way to write up the formula would be this then:

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

I added the "Missed" to be more speaking instead of just showing a blank cell if [Date Complete]>[Scheduled Date].

• ✭✭✭
Options

Thanks Everyone! It is working like a charm!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!