Date Calculations

this can certainly be shortened, but it works:

=DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1 -
(WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)>=5) -
(WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=6)

In article <CBF12F76-6F86-45E9-840F-73E968D8F0ED@microsoft.com>,
"Bruce" <Bruce@discussions.microsoft.com> wrote:

> Hope I can explain this clearly. If this, or something like it, has been
> answered before, please direct me to the post.
>
> I have a worksheet in which I need to calculate the future date, one month
> from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If
> the calculated day is a weekend (Saturday or Sunday) I need to return the
> immediately previous Friday. So, if I am calculating the date based on an
> original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday,
> so I would instead need to get to 6/17/2005.
>
> Presently I see that this would probably entail a LOT of nested functions to
> cover February and the move from December to January, etc. Just wondering if
> there's a simpler way to approach this (aside from just manually entering the
> dates after visually determining the correct date).
>
> Thanks in advance!

Thank you, JE. I will test this out and see if it is what I need.

"JE McGimpsey" wrote:

> this can certainly be shortened, but it works:
>
> =DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1 -
> (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)>=5) -
> (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=6)
>
> In article <CBF12F76-6F86-45E9-840F-73E968D8F0ED@microsoft.com>,
> "Bruce" <Bruce@discussions.microsoft.com> wrote:
>
> > Hope I can explain this clearly. If this, or something like it, has been
> > answered before, please direct me to the post.
> >
> > I have a worksheet in which I need to calculate the future date, one month
> > from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If
> > the calculated day is a weekend (Saturday or Sunday) I need to return the
> > immediately previous Friday. So, if I am calculating the date based on an
> > original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday,
> > so I would instead need to get to 6/17/2005.
> >
> > Presently I see that this would probably entail a LOT of nested functions to
> > cover February and the move from December to January, etc. Just wondering if
> > there's a simpler way to approach this (aside from just manually entering the
> > dates after visually determining the correct date).
> >
> > Thanks in advance!
>