Autofill Monday based on a deployment date

cass@aplaceforRover

Hi - I have a "date of deployment column" that I would like to auto-populate the column to the left to be the Monday prior to that exact deployment date which I will be calling "Launch week date". 

Also the the 3 columns to the left of that will need to populate 4 Mondays prior to launch week date, 6 Mondays prior to launch week date and 8 Mondays prior to launch week date.

Does this make sense? it is a sprint schedule system and I need them to autofill all those Mondays once you select ANY date in the "date of deployment column" screenshot below. I have tried so many formulas and it fails. help

Genevieve P.

Hi,

To do this, you would first need to make sure that all of these columns are the Date column type so that you can perform calculations with dates.

Then you can use this formula to minus 4 days off of the date in your "Date of deployment" column, to return the Monday before the Friday date (this presumes that all your deployment dates are Friday):

=[Date of deployment]@row - 4

 

You can use the same formula structure, but now based off of your "Launch Week" column, to minus the number to days back to return the weeks prior. For example, your BA Review Date which is 8 weeks before Launch Week would be 56 days earlier:

=[Launch Week]@row - 56

You will note that I used @row in each of these formulas, instead of referencing the specific row to help the sheet easily auto-fill this formula in the rows below.

In the images below you will see that I adjusted my Date columns to show the day of the week to make it easier to review in a quick glance. You can find information on how to standardize your date formats here. 



If I have misunderstood your question, please let me know!