Need checkbox formula to check if one date is 5 days or more prior to another date
Hello!
I have created checkbox formulas to provide a team's SLA metrics for several key dates. One of the formulas is not working well - the SLA is expected to be 5 days prior to another date. Here is my current formula. The "EB Sent 5 Days Before" date should be 5 days or more before the "Start Date" which will trigger the checkbox. It is checking the box even if the "EB Sent 5 Days Before" field is empty.
=IFERROR(IF(WORKDAY([Start Date]@row, -5) >= [EB Sent 5 Days Before]@row, 1, 0), 0)
I also need to work in holidays. I have found info on adding dates for that formula but where should I place it in the formula?
Thanks so much for your help!
Lorraine
Answers
-
A blank date cell will be seen as "in the past" which is why you're seeing a checkbox. Try adding a simple IF statement at the front of your formula that ignores blank cells, like so:
=IF([EB Sent 5 Days Before]@row = "", 0, IFERROR(IF(WORKDAY([Start Date]@row, -5) >= [EB Sent 5 Days Before]@row, 1, 0), 0))
In regards to adding in holiday days, you'll want to do that within the WORKDAY function. I've bolded the section where you would put this:
=IF([EB Sent 5 Days Before]@row = "", 0, IFERROR(IF(WORKDAY([Start Date]@row, -5, Holidays:Holidays) >= [EB Sent 5 Days Before]@row, 1, 0), 0))
Or here's another Community thread with discussions around holidays in a separate sheet.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so much @Genevieve P. ! It worked like a charm. I appreciate your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!