Solved How can I auto-populate dates in Excel based on other dates?

September 22, 2016 at 11:43:36
Specs: Windows 7
I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

See More: How can I auto-populate dates in Excel based on other dates?

Reply ↓  Report •

September 22, 2016 at 12:06:12
✔ Best Answer
Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day.

See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:

As for your question, simply subtract the number of Days required from the cell with the Goal Date.

With 11/01/2016 in A1, use this to return 9/20/2016:


(42 is 6*7, or 6 weeks.)

I'll leave the other two formulas to you. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

September 22, 2016 at 13:12:33
Perfect - thank you! I was worried that Excel didn't really know how to handle dates and it would be much more complex to deal with months of varying lengths. Since Excel has that information embedded and this is just a matter of subtraction, that makes it very easy. Thanks again for the assistance, "Derby"!

Reply ↓  Report •

Related Solutions

Ask Question