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
-
@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", "")))
Regards - JC
Answers
-
I think you need to change Today to Today() in your IF statement.
Regards - JC
-
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!
-
-
@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", "")))
Regards - JC
-
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].
-
Thanks Everyone! It is working like a charm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!