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

[claytm00]

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

 

[texasalynn]

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]

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

 

[texasalynn]

the workday formula adds two workdays so the Oct 20 should give you Oct 24

Book1
	A	B	C	D
12	10/20/2006
13	10/24/2006
Sheet1

 

[claytm00]

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

 

[claytm00]

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]

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]

Book1
	A	B	C	D
11	lead days	7		Lead due date
12	date	10/18/2006		10/25/2006
Sheet1

formula:

=IF(WEEKDAY(B12+B11)=1,B12+1,IF(WEEKDAY(B12+B11)=7,B12+3,B12+B11))

 

[texasalynn]

whoops saw a flaw
=IF(WEEKDAY(B12+B11)=1,B12+B11+1,IF(WEEKDAY(B12+B11)=7,B12+B11+2,B12+B11))

 

[claytm00]

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