In my excel file, I have to add a certain number of days to any given date.
If the end date ends up on a weekend, I need to round it up to the next week
day. For example. The contractor timing is 20 calendar days (Not business
days). The start date may vary, but the Contractor timing is set at 20 days.
What date formula do I use to calc calendars days but not end up an a
weekend.

Column A Column B Column C
Start Date Timing Due Date
11-30-09 20 days 12-20-09 (needs to
be Monday 12-21-09)

This is what I was using, but it's seems there must be a easier way.
=IF(WEEKDAY($a2+$B2)=7,($a2+$b2)+2,IF(WEEKDAY($a2+ $b2)=1,($a2+$b2)+1))

What you have is not terribly long or confusing and there is really no need
to change, but it could be reworked little by using a different return type
on the Weekday function. The ,2 shifts the numbers to Monday=1 to Saturday=7

=$A1+$B1+IF(WEEKDAY($A1+$B1,2)<6,0,8-WEEKDAY($A1+$B1,2))

It is a little more direct in that there is no longer a nested IF
--
If this helps, please remember to click yes.


"mmanis" wrote:

In my excel file, I have to add a certain number of days to any given date.
If the end date ends up on a weekend, I need to round it up to the next week
day. For example. The contractor timing is 20 calendar days (Not business
days). The start date may vary, but the Contractor timing is set at 20 days.
What date formula do I use to calc calendars days but not end up an a
weekend.

Column A Column B Column C
Start Date Timing Due Date
11-30-09 20 days 12-20-09 (needs to
be Monday 12-21-09)

This is what I was using, but it's seems there must be a easier way.
=IF(WEEKDAY($a2+$B2)=7,($a2+$b2)+2,IF(WEEKDAY($a2+ $b2)=1,($a2+$b2)+1))

=IF(WEEKDAY($A2+$B2,2)5,$A2+$B2+8-WEEKDAY(D3,2),$A2+$B2)

is another way

"Paul C" wrote:

What you have is not terribly long or confusing and there is really no need
to change, but it could be reworked little by using a different return type
on the Weekday function. The ,2 shifts the numbers to Monday=1 to Saturday=7

=$A1+$B1+IF(WEEKDAY($A1+$B1,2)<6,0,8-WEEKDAY($A1+$B1,2))

It is a little more direct in that there is no longer a nested IF
--
If this helps, please remember to click yes.


"mmanis" wrote:

In my excel file, I have to add a certain number of days to any given date.
If the end date ends up on a weekend, I need to round it up to the next week
day. For example. The contractor timing is 20 calendar days (Not business
days). The start date may vary, but the Contractor timing is set at 20 days.
What date formula do I use to calc calendars days but not end up an a
weekend.

Column A Column B Column C
Start Date Timing Due Date
11-30-09 20 days 12-20-09 (needs to
be Monday 12-21-09)

This is what I was using, but it's seems there must be a easier way.
=IF(WEEKDAY($a2+$B2)=7,($a2+$b2)+2,IF(WEEKDAY($a2+ $b2)=1,($a2+$b2)+1))