Formula for Late Audits

Duane B

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?

Jeremy C

@Duane B I think this might do it. @David Joyeuse I moved the third If statement in yours to the first statement. For whatever reason the 3rd If wouldnt trigger "Late" even when the criteria were met.

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

Jeremy C

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

David Joyeuse

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!

Jeremy C

https://community.smartsheet.com/discussion/comment/267496#Comment_267496

This works using Today() instead of Today.

Jeremy C

@Duane B I think this might do it. @David Joyeuse I moved the third If statement in yours to the first statement. For whatever reason the 3rd If wouldnt trigger "Late" even when the criteria were met.

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

David Joyeuse

Hi @Jeremy C

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].

Duane B

Thanks Everyone! It is working like a charm!