Exclude weekends from formula column when subtracting date and number columns

Platform discussions
, ,
1

Hey everyone, has anyone figured out how to exclude weekends from formula columns when calculating a new date from a date - number column? Excluding weekends from timeline column obviously doesn’t help here, and can’t remove weekends from a whole account yet… Have checked other posts and have not been able to figure it out. Here is how far I got, thanks in advance!

Screenshot 2023-10-26 131718
Screenshot 2023-10-26 131718748×477 50.1 KB

2

Hey Bruce!

Let me know how this goes?

IF(WEEKDAY({Date}) <= MOD({Lead time},5), DATE(YEAR({Date})-1, MONTH({Date}), DAY({Date}) + 365 - {Lead time} - (2*(ROUNDDOWN(({Lead time}) / 5, 0)+1))), IF(WEEKDAY(DATE(YEAR({Date})-1, MONTH({Date}), DAY({Date}) + 365 - {Lead time} - (2*ROUNDDOWN(({Lead time}) / 5, 0)))) = 1, DATE(YEAR({Date})-1, MONTH({Date}), DAY({Date}) -2 + 365 - {Lead time} - (2*ROUNDDOWN(({Lead time}) / 5, 0))), IF(WEEKDAY(DATE(YEAR({Date})-1, MONTH({Date}), DAY({Date}) + 365 - {Lead time} - (2*ROUNDDOWN(({Lead time}) / 5, 0)))) = 7, DATE(YEAR({Date})-1, MONTH({Date}), DAY({Date}) - 1 + 365 - {Lead time} - (2*ROUNDDOWN(({Lead time}) / 5, 0))), DATE(YEAR({Date})-1, MONTH({Date}), DAY({Date}) + 365 - {Lead time} - (2*ROUNDDOWN(({Lead time}) / 5, 0))))))

Just replace Date field and lead time field with your columns. Let me know how you go!

1 Like
3

Thanks Bianca! I found same thing in Formula use cases, and it works :). pasted it in a google doc and used Ctrl+H to replace all those ‘{Date}’ references. Here it is if anyone else is doing the same thing.

IF(WEEKDAY({Expected Start Date}) <= MOD({Lead Time},5), DATE(YEAR({Expected Start Date})-1, MONTH({Expected Start Date}), DAY({Expected Start Date}) + 365 - {Lead Time} - (2*(ROUNDDOWN(({Lead Time}) / 5, 0)+1))), IF(WEEKDAY(DATE(YEAR({Expected Start Date})-1, MONTH({Expected Start Date}), DAY({Expected Start Date}) + 365 - {Lead Time} - (2ROUNDDOWN(({Lead Time}) / 5, 0)))) = 1, DATE(YEAR({Expected Start Date})-1, MONTH({Expected Start Date}), DAY({Expected Start Date}) -2 + 365 - {Lead Time} - (2ROUNDDOWN(({Lead Time}) / 5, 0))), IF(WEEKDAY(DATE(YEAR({Expected Start Date})-1, MONTH({Expected Start Date}), DAY({Expected Start Date}) + 365 - {Lead Time} - (2ROUNDDOWN(({Lead Time}) / 5, 0)))) = 7, DATE(YEAR({Expected Start Date})-1, MONTH({Expected Start Date}), DAY({Expected Start Date}) - 1 + 365 - {Lead Time} - (2ROUNDDOWN(({Lead Time}) / 5, 0))), DATE(YEAR({Expected Start Date})-1, MONTH({Expected Start Date}), DAY({Expected Start Date}) + 365 - {Lead Time} - (2*ROUNDDOWN(({Lead Time}) / 5, 0))))))

1 Like
4

Related issue - There doesn’t look to be a way to set reminders on dates in formula columns, which is essential for this column to be full proof.

5

You’re correct Bruce, I am afraid this isn’t currently supported. You can add your vote to the existing feature request here, Include formula columns in automation recipes. This feature is on our teams radar!

1 Like