Auto populate additional dates based on specific date and criteria

[budimu]

Hi all,

i am currently trying to provide my client with a tracking sheet (check list) of items to provide me based on specific timeframes, but i needed for the dates to auto-populate based on an initial date provided ("GO Live Date" in my example).

GO Live date = 05/14/2014

Data required 5 weeks prior to GO Live = (date to be auto populated based on "5 weeks prior" & "05/14/2014)

Data required 3 weeks prior to GO Live = (date to be auto populated based on "3 weeks prior" & "05/14/2014)

... and so on ...

thank you for any help!

 

[SirJB7]

Hi, budimu!

Excel stores dates as integer numbers starting from 1 for the Jan 1st, 1900 and adding 1 for each day. It also stores time as fractions of a day (fractions of 1) starting from 0 for 12:00 AM and 0.999988425925926 for 23:59:59.

So the cells holding date & time values can be easily used in simple arithmetic operations like adding 1 or subtracting 0.25 for getting the next day or getting the time 6 hours before.

In your uploaded file and assuming that the go live date is in cell B3 as a valid date value, you can do this:
B3: =B2-8*7
B7: =B2-5*7
B10: =B2-4*7
B15: =B2-3*7

Regards!