date calculation issues

M

mike richard

I need a formula that will calculate future dates for me. If I were to
insert todays date,(as an example) 13-june-2005 into excel, I need it
to calculate 6 weeks, 6 months, 1 year, 2 years, 3 years and 4 years
ahead of the date specified. On top of this, the future dates cannot
land on a weekend or a holiday.

I am aware of the workday function which is part of the Analysis tool
pack but i am unsure how to use it.
 
R

Ron Rosenfeld

I need a formula that will calculate future dates for me. If I were to
insert todays date,(as an example) 13-june-2005 into excel, I need it
to calculate 6 weeks, 6 months, 1 year, 2 years, 3 years and 4 years
ahead of the date specified. On top of this, the future dates cannot
land on a weekend or a holiday.

I am aware of the workday function which is part of the Analysis tool
pack but i am unsure how to use it.
Click to expand...

So long as you are going to use the ATP, then the EDATE function is the
simplest way of adding months to your initial date. It takes care of the
problem that can occur when adding six months and not having the resultant
month have enough days. Or adding years to 29 Feb 2004.

To use the WORKDAY function so as to avoid falling on a weekend or holiday, you
need to have a range, which you may name Holidays, that contains a list of all
the Holiday dates. You then subtract 1 from the calculated date, and add 1
workday to that result. The result will be the first non-holiday, non-weekend
after your computed date. (If you want the day before, then add 1 to the
calculated date and subtract 1 workday).


A2: Initial Date
B2: =workday(A2+42-1,1,Holidays) (6 weeks)
C2: =workday(edate(A2,6)-1,1,Holidays) (6 months)
D2: =workday(edate(A2,12)-1,1,Holidays) (1 year or 12 months)
E2: =workday(edate(A2,24)-1,1,Holidays) (2 years or 24 months)
F2: =workday(edate(A2,36)-1,1,Holidays) (3 years or 36 months)
G2: =workday(edate(A2,48)-1,1,Holidays) (4 years or 48 months)

This should get you started. Let me know how it works out.


--ron
 
Top