I need a column that is always -5 working days before another date column

[nev (Neville Stehbens)]

Hello,

There is actually a formula to do this in the formula help in the use cases section however when i use that formula it doesn’t work and it tells me I’m creating a circular dependency,

So I have 2 columns, {scheduled delivery date} and {detailing cut off date} the latter needs to be always 5 working days before the scheduled delivery date.
The description of the use case is as follows - Subtract X working days from a date, where X is a column value (Exclude weekends)
and the formula examples is as follows

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

Could someone let me know where i am supposed to put the 5, {scheduled delivery date}, and the {detailing cut off date}

I tried replacing the {# Working Days Before} column with my {detailing cut off date} column and the {Date}'s with my {scheduled delivery date} column but i got the circular dependency issue.

Thanks in advance!

[nev (Neville Stehbens)]

Ok,
So I realize I’m answering my own question here haha! but I got it just after I posted.

The formula that works is below, you just need to change the {Date} to the original column (in my case the {scheduled delivery date}) and the {# Working Days Before} with the number of days you want (in my case 5). You also need to make sure you remove the { and the } from either side of the #working days before spots in the example.

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

I hope this saves someone else some time!