Weekend dates calculation

[jbenton]

I have a a formula set up to calcuate a send date for mail, however the problem is that I can't figure out how to make the date move forward to the next working day if the final result ends on a weekend.

Example:
=C6-5 (where c6 = the date we need a response by, for example 11-23-07)

The send date needs to be 5 days before the response date (11-18-07) however since we don't work on sunday I need the date to move forward to the next monday (11-19-07).

Any help would be appreciated thanks,

JB

[corinereyes]

Hi jbenton,

Assume the date is in B9: Try this formula:

If the date falls on Saturday, result is Monday:

Please Login or Register  to view this content.

If the date falls on Saturday, and you want it to be Friday:

Please Login or Register  to view this content.

If the formula returns the serial number of the date, go to Format Cells, Custom, mm/dd/yyyy.

[Mark@Work]

Somthing like this should do the trick:
=IF(WEEKDAY(C6-5,2)=7,C6-6,C6-5)
ie:
If the day 5 days before the date in C5 is a Sunday use the date 6 days before instead.

Mark.