I manage digital marketing campaigns from end-to-end, and there are various steps within each campaign's production process that are due certain dates out from and relative to a final launch date, which may be subject to change.
In Google Sheets, I would like to be able to input a launch date and have each step leading up to it display its due date a specific amount of weekdays out from the launch date. If the launch date changes, I would like all the dates in the steps that come before it to change dynamically/automatically with it.
e.g. The launch date is 23-12-2019, but the last step in the process is due one weekday before the launch date, so it must display 20-12-2019; the second last step is due 3 weekdays before the launch date, so it must display 18-12-2019, and so on...
This was once possible in a former role where I used Excel, but I am at a loss for how to set this in Google Sheets. I would then like to set conditional formatting so that if the due date per step arrives, that it turns red, but remains colourless otherwise.
I would appreciate any assistance you can give me.