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.

This should work:
=IF(WEEKDAY(B2-30,1)=1,B2-29,IF(WEEKDAY(B2-30,1)=7,B2-31,A2-30))

tj

"Robyn Bellanger" wrote:

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.

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

I mistyped and had A2 at the end of the formula instead of B2.

tj


"tjtjjtjt" wrote:

This should work:
=IF(WEEKDAY(B2-30,1)=1,B2-29,IF(WEEKDAY(B2-30,1)=7,B2-31,A2-30))

tj

"Robyn Bellanger" wrote:

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.