date calculation issues

[Guest]

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 but don't know how to use it. My
excel experience with formula's is limited. Very basic steps is required.

 

[Ron Rosenfeld]

On Mon, 13 Jun 2005 10:32:04 -0700, mike richard <mike

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 but don't know how to use it. My
excel experience with formula's is limited. Very basic steps is required.

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

 

[Guest]

Thanks Ron, that worked perfectly.
Enjoy yourself.
Mike

 

[Ron Rosenfeld]

Thanks Ron, that worked perfectly.
Enjoy yourself.
Mike

You're welcome. Thanks for the feedback.

--ron