Using Excel to calculate future dates with various intervals

[MH]

Hi,
I need to build a calendar, where I have a given start date. From this
start date, I need to capture future predicted dates, based on various
intervals. How do I do this?

Example:
Start Date: 1/1/09
Future Date intervals: 1 week, 2 weeks, 1 month, 3 months, 12 months, 18
months, 2 years

Thanks for your help with this.

MH

 

[T. Valko]

Let's assume your start date is 2/29/2008 and you want the future date in 12
months (1 year). What result do you expext?

Start date is 1/31/2009 and you want the future date in 1 month. What result
do you expect?

The future dates for the weeks is pretty straightforward:

1 week: =start_date+7
2 weeks: =start_date+14

For the months and years, well, you have to think about those for a minute
and decide what the correct end date should be because months and years
don't have the same number of days in them!

 

[MH]

Hi T. Valko,

For your examples, I would want it to end 1 calendar month or year later,
regardless of the odd number of days (i.e. 2/29/09 would end one month later
2/29/09 or one year later 2/29/10. If this exceeds the number of days in the
month it would fall on, then I would need it to fall on the last day of that
month, say 2/28/09.

What is the proper method to calculate these days if this is the case?
Thanks.

 

[T. Valko]

Ok....

For months or years:

=EDATE(start_date,n)

Where n = number of months. Express years in months: 1 year =12 months, 3
years = 36 months.

The EDATE function requires the Analysis ToolPak add-in be installed for
Excel versions prior to Excel 2007. If you enter the formula and get a
#NAME? error look in Excel help for the EDATE function and it'll tell you
how to install the Analysis ToolPak add-in.

 

[Ashish Mathur]

Hi,

You could also try the following

1. Suppose 1/1/2009 is entered in cell B4;
2. In B7:B13, enter 1,2,1,3,12,18,2
3. In C7:C13, enter weeks,weeks,months,months,months,months,years
4. In E7, enter the following formula and copy down

=IF($C7="weeks",DATE(YEAR($B$4),MONTH($B$4),DAY($B$4)+($B7*7)),IF($C7="months",DATE(YEAR($B$4),MONTH($B$4)+B7,DAY($B$4)),DATE(YEAR($B$4)+B7,MONTH($B$4),DAY($B$4))))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[MH]

Thank you T. Valko and Ashish Mathur for your suggestions. I will certainly
give them a try.

Do these formulas work in other formulas if I need to calculate number of
days, months, or years etc with the same conditions applied? I imagine I
would bracket the formula you gave me in the formula of interest. I don't
have an example to apply at this time.

Second question, slightly off topic:
I will need to apply this date formula for various start dates and variable,
in a single excel spreadsheet. Is there a way I can set this up so that it
can autopopulate a series of cells with new dates, that were previously blank
(ie. fill down the existing list), when given a product, ref doc., lot, and
base date only. The first two items determine the intervals assigned, and
the lot and base date would be the variables that would be added later that
need the predicted dates autopopulated.

Ex:
A B C D E
Product 1 Ref Doc Lot 1 Base Date Predicted
Intervals
(I would need it to populate downward or in a fashion that can be sorted
based on the predicted intervals)

Thanks very much for your help.
MH

 

[Shane Devenshire]

Hi,

And for another idea
1. Enter 1/1/2009 in A1
2. Drag the fill handle down with the right mouse button
when you release the mouse you will see choices for
Fill Months
Fill Years (12 months)
3. For weeks enter 1/1/2009 in A1 and 1/8/09 in A2, highlight both and drag
the fill handle down (no right mouse this time)
4. For 2 week increments enter 1/1/2009 in A1 and 1/15/09 in A2, highlight
both and fill down.
5. For 3 month increments enter 1/1/2009 in A1 and 4/1/2009 in A2 and repeat
as above.
6. For 18 months enter 1/1/2009 and 6/1/2010 and repeat as above.
7. For 2 years use 1/1/2009 and 1/1/2011

 

[aldrin]

Hi .Good day.

I would like ask question on how to make an auto prediction for date and time.

ex : current date & time ---- to be compute after 60 hours. ---- into prediction date & time
9/13/2015 3:00 9/16/2015 15:00

pls help me to do this..

Tnx a lot. hope to get respone.
aldrin

 

[aldrin]

Hi .Good day.
I would like ask question on how to make an auto prediction for date and time.
ex : current date & time ---- to be compute after 60 hours. ---- into prediction date & time
9/13/2015 3:00 ------ 9/16/2015 15:00
pls help me to do this..
Tnx a lot. hope to get respone.
aldrin