48hr checkbox excluding weekends

Portals

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.

Andrée Starå

Hi @Portals

I hope you're well and safe!

You could use the WEEKDAY function to check if it's a Saturday or Sunday.

https://help.smartsheet.com/function/Weekday

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!

Portals

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?

Courtney S.

@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.

Portals

@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.

Courtney S.

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