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

claytm00

New Member
Joined
Mar 27, 2006
Messages
43
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
Joined
May 19, 2002
Messages
8,458
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
Joined
Mar 27, 2006
Messages
43
Hi thanks - I cannot seem to get this to work.

My example =

A1 = 20-Oct
Leadtime = 2 days

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.

I've tried the advice but my answer is 31-Oct
 

claytm00

New Member
Joined
Mar 27, 2006
Messages
43

ADVERTISEMENT

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

claytm00

New Member
Joined
Mar 27, 2006
Messages
43
Very sorry - I spoke too soon!!!

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

A1 = 16-Oct
Leadtime is 7 days.

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
Joined
Aug 13, 2004
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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
Joined
May 19, 2002
Messages
8,458
Book1
ABCD
11lead days7Lead due date
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
Joined
May 19, 2002
Messages
8,458
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
Joined
Mar 27, 2006
Messages
43
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top