Eliminate Work Days in a Formula

[scocoRHM (Scott Coco)]

I am entering a date into the Due to Web Production column:

I then want the “Final Edit Due” column to automatically change to 3 Working Days before the “Due to Web Production” column.

Then have the “Recording Due Date” to updated to be 7 Working Days before the “Final Edit Due” column.

Then have the “Booking Due Date” to update to be 10 Working Days before the “Recording Due Date”.

The first formula that I did worked fine, but it included Weekends:

FORMAT_DATE(SUBTRACT_DAYS({Final Edit Due},7), “MMM DD, YYYY”)

This formula seems to be subtracting too many days:

IF(WORKDAYS({DUE TO WEB PROD},SUBTRACT_DAYS({DUE TO WEB PROD},4))<5,FORMAT_DATE(SUBTRACT_DAYS({DUE TO WEB PROD},6), “MMM DD YYYY”), FORMAT_DATE(SUBTRACT_DAYS({DUE TO WEB PROD},3), “MMM DD YYYY”))

[DanIngham-IOI (Dan Ingham IOI / BPX)]

Hi Scott
I think you will need to use something like this
IF(WEEKDAY(FORMAT_DATE(SUBTRACT_DAYS({Due to Web Production},3),“DD-MMM-YYYY”))=7,FORMAT_DATE(SUBTRACT_DAYS({Due to Web Production},4),“DD-MMM-YYYY”),IF(WEEKDAY(FORMAT_DATE(SUBTRACT_DAYS({Due to Web Production},3),“DD-MMM-YYYY”))=1,FORMAT_DATE(SUBTRACT_DAYS({Due to Web Production},5),“DD-MMM-YYYY”),FORMAT_DATE(SUBTRACT_DAYS({Due to Web Production},3),“DD-MMM-YYYY”)))

The formula is looking if when it minus the 3 days it’s a Saturday or Sunday, if it is it will push back more days to make it the Friday
The number 7 reflects a Saturday & the number 1 is Sunday
Normally 1 is Monday & 7 Sunday, but it may depend on how the system is set for start of week being Sunday or a Monday

Hope this helps
Many thanks Dan