# Date calculation - how do I not add weekends???

#### claytm00

##### New Member
Hi,

I have a spreadsheet where dates are put in then another cell then references the cell and then adds a predefined value.

For example - If A1 = 16-Oct and the value to add is 7 then this would return 23-Oct.

However, how can I then set up a formula that for when the value to add equals a Saturday or Sunday, that it rounds it up to the nearest Monday?

So my example would be - If A1 = 17-Oct and the value to add is 5 days then I don't want 22-Oct as this would be a Sunday but would want Mon 23-Oct?

Thanks

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### texasalynn

##### Well-known Member
use the workday function, which requires you to turn-on the Analysis Toolpak add-in. This is a part of Excel that doesn't automatically turn-on. Go to the Tools - Add-ins - Click Analysis Toolpak. No special security to add this in. If you need to consider your work holidays, add them to your workbook.

=WORKDAY(A1,7)

#### claytm00

##### New Member
Hi thanks - I cannot seem to get this to work.

My example =

A1 = 20-Oct

I want to add 2 days to 20-Oct but where (and in this case) the answer is either a Saturday or Sunday, I want this to round up to a Monday.

#### texasalynn

##### Well-known Member
the workday formula adds two workdays so the Oct 20 should give you Oct 24
Book1
ABCD
1210/20/2006
1310/24/2006
Sheet1

#### claytm00

##### New Member

Cool, thanks for the quick reply - I think that this works now!

#### claytm00

##### New Member
Very sorry - I spoke too soon!!!

This formula works however - see my example below....

A1 = 16-Oct

In this case, the 7 days will result in 23-Oct so this answer will not fall on a Sat or Sun. For this example, I don't want the formula to ignore Sat and Sun in the 7 day count.

When I use the formula for the above example, my answer is 25-Oct so this is ignoring the Sat and Sun but still adding 7 days.

Is there a way where I specify that if the answer of the above falls on a Saturday or Sunday that it rounds it up to the next 'Workday'?

Apologies if this is confusing

#### mardeebo

##### New Member

I'm having the exact same issue as you...and I think I found a fix.

Perhaps one of the experts here can look at my formula and see if there's a flaw I'm not yet finding.

This will check to see if the result is a saturday or sunday and if it is, add another business day. If the result falls on a holiday, it will add a business day there as well.

HEre's my formula, using a table with holidays in it defined as "holidays".

My original date is in cell A4 and i'm adding 4 to it...

=IF(OR(WEEKDAY(A4+4)=1,WEEKDAY(A4+4)=7,IF(ISNUMBER(MATCH(A4+4,Holidays,0)),1,0)),WORKDAY(A4+4,1,Holidays),A4+4)

What do ya'll think?

#### texasalynn

##### Well-known Member
Book1
ABCD
12date10/18/200610/25/2006
Sheet1

formula:
Code:
``=IF(WEEKDAY(B12+B11)=1,B12+1,IF(WEEKDAY(B12+B11)=7,B12+3,B12+B11))``

#### texasalynn

##### Well-known Member
Code:
``````whoops saw a flaw
=IF(WEEKDAY(B12+B11)=1,B12+B11+1,IF(WEEKDAY(B12+B11)=7,B12+B11+2,B12+B11))``````

#### claytm00

##### New Member
Thank you - when I typed it in, I didn't get the correct result - I went back to here to see if you had posted an update and you had!!!

Phew - Thanks this is great

Replies
3
Views
143
Replies
0
Views
75
Replies
5
Views
105
Replies
1
Views
83
Replies
2
Views
230

1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back