Need Help with Approaching Due Date Formula
I have an Approaching Due Date column with the Star checkbox and I want to create a formula that will show the Star if today is between 7 working days in advance of the End Date and the End Date, and the Status is NOT Complete. I also have a column for Past Due with a Red Flag checkbox. I want the Approaching Due Date formula to exclude any tasks that are already marked as Past Due.
Currently, I have this formula in place (which is close to what I'm trying to do, but doesn't have the exclusion of Past Due line items) -
=IF(AND([End Date]@row <= TODAY(7), NOT(Status@row = "Complete")), 1, 0)
How can I write this to achieve the outcome described above?
Thanks!
Marcy
Best Answer
-
Hi Marcy! You're missing a few arguments in your formula. Give this one a shot:
=IF(AND([End Date]@row <= TODAY(7), [End Date]@row >= TODAY(), [Past Due Flag]@row = 0, NOT(Status@row = "Complete")), 1, 0)
Stay Agile,
agilizeconsulting.com
Answers
-
Hi Marcy! You're missing a few arguments in your formula. Give this one a shot:
=IF(AND([End Date]@row <= TODAY(7), [End Date]@row >= TODAY(), [Past Due Flag]@row = 0, NOT(Status@row = "Complete")), 1, 0)
Stay Agile,
agilizeconsulting.com
-
Hi Rochelle! Thank you SO much!! With one small edit, this worked like a charm. So appreciate your help. 😀
=IF(AND([End Date]@row <= TODAY(7), [End Date]@row >= TODAY(), [Past Due]@row = 0, NOT(Status@row = "Complete")), 1, 0)
-
Good deal!! I'm glad it worked out!
Stay Agile,
agilizeconsulting.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!