0

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.

  • share a copy of your sheet – user0 Apr 23 at 3:35
0

Dates are stored in Sheets as numbers, and then cosmetically displayed as dates according to your formatting selections. This is great as it makes date math trivial. One day ago? =TODAY()-1 And so on.

Your described need is just slightly different in that you need to only count weekdays. Thankfully, there is an inbuilt Sheets function just for this, WORKDAY. So an example formula to automatically show the Second To Last Step date, 3 days before your manually-entered launch date, could resemble: =WORKDAY(launchDate,-3)

Across all dates, the Conditional Formatting menu choices matching "overdue" would be:
Format Rules → Format cells if:  "Date is before"  "tomorrow"

See: this example sheet. Edit the LAUNCH date to see the other dates and highlighting change as well.

screenshot of example Sheet, with the launch date on a monday, the penultimate date on the friday before, and three earlier milestones highlighted since they're before today, so overdue

  • You didn't mention where your offsets come from. They're manual in my answer's example. Also, there is one correct worldwide date format, 2019-12-18 , that is, yyyy-MM-dd (See: ISO 8601 intro, mandatory xkcd). My example uses that format. •• That all aside, welcome! Let us know how things work out, or if you have issues or new questions. – Joel Reid Apr 24 at 15:28
  • Thank you very much for your response, Joel. I had to figure out the 'LAUNCHDATE' side of things, but managed to using the date range option for that particular column of cells. I believe I have now achieved what I need to using your guide. One other thing I was wondering was as I do not work Mondays, is there a way to bypass not only weekends but also Mondays in the calculation of "leading up to launch date" days? – Billy Apr 26 at 3:16
  • Just to be sure we're using the same terms, the launchDate in my example WORKDAY formula was just a Named Range for C12. They can all refer to your launch date cell manually, or have the date hardcoded into the formula. ••• As for skipping Mondays, WORKDAY has an optional third parameter; a list of dates to skip. Say your project is 14 weeks, we could list and skip "the 15 Mondays leeding up to the Monday after launch" with something like ARRAYFORMULA(C$12-(WEEKDAY(C$12-2))+7-7*(ROW(1:15)-1)) inserted into WORKDAY() as parameter 3. – Joel Reid Apr 26 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.