48hr checkbox excluding weekends
Hi, I currently have a formula to tick a box after 48 hours but I need it to exclude weekends. The problem is it's a live workflow and I'm reluctant to make changes unless I'm 100% sure.
My current formula is: =IF((TODAY() - [input date column]@row) > 2, 1, 0)
I was thinking something like this: =IF((WORKDAY() - [input date column]@row) > 2, 1, 0)
Or maybe: =IF((WORKDAY(TODAY() - [input date column]@row) > 2, 1, 0)
Can anybody tell me if this would work, or how I could make it work please, many thanks. in advance.
Kind regards,
Portals
Thanks and have a great day 🙂
Answers
-
Hi @Portals
I hope you're well and safe!
You could use the WEEKDAY function to check if it's a Saturday or Sunday.
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks @Andrée Starå
My post may have been a little vague in that date itself would always be a weekday, it's the 48 hour flag that needs to omit weekends and only count weekdays in the formula. Would the weekday function work in this scenario and could you assist with the formula please?
Kind regards,
Portals
Thanks and have a great day 🙂
-
@Portals This doesn't actually solve your question, in the sense that I can't offer a formula-based solution, but I think you could use an automation workflow to check the box instead.
You can have automations run only on weekdays. In the automation you could check if the date in the input date column is more than a certain number of days in the past. Every weekday, in rows where the input date is more than a certain number of days in the past, and the checkbox column is blank, then you could have the automation edit the checkbox column. I attached an example using a random I sheet I have - pretend the "TouchPoint Response Date" is your input date column, and "Manager" is your checkbox column. You could test the automation method first by making the workflow update a temporary test column instead of the real checkbox column.
-
@Courtney S. thank you so much,
If this works it would potentially be a better solution for me, I just hope they don't come back asking for bank holidays to be removed (unless you can add this to the automation too?)
I'll give it a go tonight and let you know how I get on.
Kind regards,
Portals
Thanks and have a great day 🙂
-
Oh, I suppose they will want holidays excluded! I haven't had to solve that problem for myself before, but maybe go back to the formula. Apparently there is a "NETWORKDAYS" function that can be made to exclude holidays if you list them in a sheet. You could maybe combine IF, NETWORKDAYS, and TODAY?
NETWORKDAYS Function | Smartsheet Learning Center
Formula Help - Exclude Holidays — Smartsheet Community
https://community.smartsheet.com/discussion/74904/networkday-counting-excluding-holidays
Or maybe see this: Exclude weekends and holidays from automation reminders — Smartsheet Community
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives