IF formula to compare due dates to show completion schedule
Hello all!
I have a formula that is stumping me - I am looking to compare a target due date versus the actual completion date. I have a formula that is partially working, but am running into issues when I use the term "ongoing" on the target due date column (see Project 4 in the below example).
This is what I'm trying to solve for:
- If the Target Due Date is "Ongoing" and the Completion Date is blank, I'd like the Completion on Time (4th column) column to say On Time
Here is the formula that I am working with:
=IF(AND(ISBLANK([Completion Date]@row), [Target Due Date]@row >= TODAY()), "On Time", IF([Completion Date]@row > [Target Due Date]@row, "Late", IF([Completion Date]@row = [Target Due Date]@row, "On Time", IF([Completion Date]@row < [Target Due Date]@row, "Early"))))
Any chance someone could help incorporate the ongoing section into the formula?
Thanks in advance!!
Best Answers
-
Try this; it works for me:
=IF(AND(NOT(ISDATE([Target Due Date]@row)), ISBLANK([Completion Date]@row)), "On Time", IF(AND(ISBLANK([Completion Date]@row), [Target Due Date]@row >= TODAY()), "On Time", IF([Completion Date]@row > [Target Due Date]@row, "Late", IF([Completion Date]@row = [Target Due Date]@row, "On Time", IF([Completion Date]@row < [Target Due Date]@row, "Early")))))
Hope this helps!
-
Hello!
I updated your formula to check if Target Due Date is a date first.
=IF(ISDATE([target due date]@row), IF(ISBLANK([completion date]@row), IF([target due date]@row >= TODAY(), "On Time", "Late"), IF([completion date]@row > [target due date]@row, "Late", IF([completion date]@row = [target due date]@row, "On Time", IF([completion date]@row < [target due date]@row, "Early")))), "On Time")
Commented below to break it down...
=IF(ISDATE([target due date]@row), // check if date
IF(ISBLANK([completion date]@row), // if target is a date, then check if completion is blank
IF([target due date]@row >= TODAY(), "On Time", "Late"), // when target is a date + completion is blank, check target. If Target is greater or equal than today On Time, if less than it's Late.
IF([completion date]@row > [target due date]@row, "Late", // when target is date but completion is not blank, compare Completion to Target. If Greater = Late
IF([completion date]@row = [target due date]@row, "On Time", // If Equal = On Time
IF([completion date]@row < [target due date]@row, "Early")))), // If Less = Early
"On Time") //Target is not a date so it is On Time. NOTE: This assumes you would never have a Completion without having a Target but if it does, it's considered On Time)
Hope this works for you! Let us know.
Answers
-
Try this; it works for me:
=IF(AND(NOT(ISDATE([Target Due Date]@row)), ISBLANK([Completion Date]@row)), "On Time", IF(AND(ISBLANK([Completion Date]@row), [Target Due Date]@row >= TODAY()), "On Time", IF([Completion Date]@row > [Target Due Date]@row, "Late", IF([Completion Date]@row = [Target Due Date]@row, "On Time", IF([Completion Date]@row < [Target Due Date]@row, "Early")))))
Hope this helps!
-
Hello!
I updated your formula to check if Target Due Date is a date first.
=IF(ISDATE([target due date]@row), IF(ISBLANK([completion date]@row), IF([target due date]@row >= TODAY(), "On Time", "Late"), IF([completion date]@row > [target due date]@row, "Late", IF([completion date]@row = [target due date]@row, "On Time", IF([completion date]@row < [target due date]@row, "Early")))), "On Time")
Commented below to break it down...
=IF(ISDATE([target due date]@row), // check if date
IF(ISBLANK([completion date]@row), // if target is a date, then check if completion is blank
IF([target due date]@row >= TODAY(), "On Time", "Late"), // when target is a date + completion is blank, check target. If Target is greater or equal than today On Time, if less than it's Late.
IF([completion date]@row > [target due date]@row, "Late", // when target is date but completion is not blank, compare Completion to Target. If Greater = Late
IF([completion date]@row = [target due date]@row, "On Time", // If Equal = On Time
IF([completion date]@row < [target due date]@row, "Early")))), // If Less = Early
"On Time") //Target is not a date so it is On Time. NOTE: This assumes you would never have a Completion without having a Target but if it does, it's considered On Time)
Hope this works for you! Let us know.
-
Thanks Kelly and Victoria!! These both worked for what I'm looking to accomplish. Much appreciated
-
Hello I'm applying this to course completion dates and it works if I omit the is this a date check.
Some of our courses weren't assigned a due date in error - the check if the due date is a date in the first query returns #NAME?
Not sure what I've missed as copied exact just changed the return values:
=IF(ISDATE(M780),IF(ISBLANK(N780),IF(M780>=TODAY(),"Due","Late"),IF(N780>M780,"Late",IF(N780=M780,"Due",IF(N780<M780,"On Time")))),"No due date")
-
Hi @Whibley
Can you post a screen capture showing your columns, but block out sensitive data?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!