if/and/or formula help
I have a set of training records data. Some have required due dates, some do not. I need a formula that set s flag (for items needing attention) if:
- the complete date is blank
- the complete date is > the due date (if there is a due date)
- for this condition, i need it to ignore if the due date is blank
When I use this formula, it works, except it does not handle the blank due dates:
=IF(OR([Complete Date]@row = "", [Due Date]@row < TODAY()), 1, 0))
how should i modify this to accommodate 2.a scenario?
Best Answer
-
Hi @SRaben2,
The write-up you have and the formula you show do not line up. In the formula you are checking the Due Date against the current date, this is not mentioned in your write-up.
Going strictly by what you stated, the following should work.
=IF(OR([Complete Date]@row = "", AND([Complete Date]@row > [Due Date]@row, NOT(ISBLANK([Due Date]@row)))), 1, 0)
Hope this helps,
Dave
Answers
-
Hi @SRaben2,
The write-up you have and the formula you show do not line up. In the formula you are checking the Due Date against the current date, this is not mentioned in your write-up.
Going strictly by what you stated, the following should work.
=IF(OR([Complete Date]@row = "", AND([Complete Date]@row > [Due Date]@row, NOT(ISBLANK([Due Date]@row)))), 1, 0)
Hope this helps,
Dave
-
THANK YOU DAVE!!! You saved the day!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!