Viewer Request: How do I calculate dates in the future?
Today’s lesson is my answer to a viewer’s question: “How does Excel calculate a date in the future?”
The viewer wrote me because they were having trouble writing a formula that would return a date “N-Months” in the future. Or “3 years in the future” from a specific starting date.
I am asked this question frequently. In this short Excel video lesson I demonstrate how you write the formula.
Here are the steps to follow in this Excel Training Video:
- Use the AutoFill feature and Options to increment your “Starting Date.” I recommend using your “right-mouse button” to AutoFill because it automatically brings up a Menu of Options when you release the Right-Mouse Button.
- In response to the viewer’s question, we will choose “Fill Months.”
- Remember that we are using a “Hard-Coded” date as our “Starting Date,” so each value is really a “constant value.” This may not be the optimal result that we are seeking.
- In order to provide flexibility (with our starting date) we need to learn how to construct a formula that will increment (in this case the MONTH) our values.
- Use the “DATE() Function.
- For each of the “Arguments,” use the YEAR(), MONTH() and DAY() functions.
- To answer my viewer’s question we use MONTH(A2) +1 in the “Month Argument.”
- To “Augment the Year,” we use YEAR(A2) +1 in the “Year Argument.”
- Finally, write a “Formula” to verify that Excel is properly accounting for “Leap Years.”
