Date Formula

[Robyn Bellanger]

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 Malinsky]

Try this:

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

 

[Sandy Mann]

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