Date Formula
 

Hi,

I am trying to put together a formula that will calculate a cell referencing
a date less 30 days but to return a Friday date if it falls on Saturday and
a Monday date if it falls on Sunday.

Example:

B2= 01/10/05

=b2-30 returns a date of 12/11/04 which is on Saturday. I want it to return
12/10/04 Friday instead. If it fell on Sunday I would want it to return
Monday's date. Otherwise, if b2-30 falls on Monday through Friday I want
that date returned.

Any help would be greatly appreciated.

Try this:

=IF(WEEKDAY(B2-30)=7,B2-31,IF(WEEKDAY(B2-30)=1,B2-32))

"Robyn Bellanger" wrote in message
...
Hi,

I am trying to put together a formula that will calculate a cell
referencing
a date less 30 days but to return a Friday date if it falls on Saturday
and
a Monday date if it falls on Sunday.

Example:

B2= 01/10/05

=b2-30 returns a date of 12/11/04 which is on Saturday. I want it to
return
12/10/04 Friday instead. If it fell on Sunday I would want it to return
Monday's date. Otherwise, if b2-30 falls on Monday through Friday I want
that date returned.

Any help would be greatly appreciated.

Michael,

=IF(WEEKDAY(B2-30)=7,B2-31,IF(WEEKDAY(B2-30)=1,B2-32))

will return Fridays if the target date would have been a Sunday instead of
Mondays and FALSE for Monday through Friday.
I am sure that you meant:

=IF(WEEKDAY(B2-30)=7,B2-31,IF(WEEKDAY(B2-30)=1,B2-29,B2-30))


Just for variety:

=(B2-30)-(WEEKDAY(B2-30)=7)+(WEEKDAY(B2-30)=1)

will also do it.

Regards

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Michael Malinsky" wrote in message
...
Try this:

=IF(WEEKDAY(B2-30)=7,B2-31,IF(WEEKDAY(B2-30)=1,B2-32))

"Robyn Bellanger" wrote in message
...
Hi,

I am trying to put together a formula that will calculate a cell
referencing
a date less 30 days but to return a Friday date if it falls on Saturday
and
a Monday date if it falls on Sunday.

Example:

B2= 01/10/05

=b2-30 returns a date of 12/11/04 which is on Saturday. I want it to
return
12/10/04 Friday instead. If it fell on Sunday I would want it to return
Monday's date. Otherwise, if b2-30 falls on Monday through Friday I
want
that date returned.

Any help would be greatly appreciated.