Adding Days to a Date excluding weekends

[srsteiner]

Looking for a way to add days to a date and exclude weekends in the process.

Ex.

If my date is Mar. 16. and I need to add 5 days to it. The result I'm looking for is Mar. 23 (not adding the Mar. 20 and Mar 21 into the total), not Mar. 21.

Want to just add "business" or "working day" to the date.

Thanks!!

 

[santeria]

Use the WORKDAY formula.

You need the Analysis pak for it to be available.

=WORKDAY("3/15/2004",5)

So thats last Monday, plus the five working days from the date stated, and then the result will be next monday 3/22

The cell has to be formatted in Days etc...

 

[Zack Barresse]

or something like this...

Book3
	A	B	C	D
17	3/16/2004	3
18	3/22/2004	5
Sheet2

notice in the workday formula the "-1", depends wether you want to count tht day or how you interpret it.

HTH

nice one santeria!

 

[santeria]

The way you added the -1 works better.
Makes use of the function WORKDAY, and adds up the working days including the start date, makes more sense.

Ta

 

[Ekim]

I will add a little twist to your scenario by assuming that the 18th and the 22nd are public holidays. Ignoring the weekend and the public holidays, adding 5 days to 16 March 04 will result in 25 March 04.

Business Days from a start date.xls
	A	B	C	D	E	F	G	H	I	J	K
1
2					BusinessDays					Holidays
3		16-Mar-04	Tue		5	25-Mar-04				18-Mar-04	Thu
4	1	17-Mar-04	Wed							22-Mar-04	Mon
5		18-Mar-04	Thu
6	2	19-Mar-04	Fri			=WORKDAY(B3,E3,J3:J4)
7		20-Mar-04	Sat
8		21-Mar-04	Sun
9		22-Mar-04	Mon
10	3	23-Mar-04	Tue
11	4	24-Mar-04	Wed
12	5	25-Mar-04	Thu
13		26-Mar-04	Fri
14		27-Mar-04	Sat
15		28-Mar-04	Sun
16		29-Mar-04	Mon
Sheet1

Regards,

Mike

 

[Aladin Akyurek]

The way you added the -1 works better.
Makes use of the function WORKDAY, and adds up the working days including the start date, makes more sense...

What if the date to start with is not a workday like:

3/20/2004

which is a saturday?

Leaving the holidays out for the moment...

=WORKDAY(A1,5)-1

would give:

3/25/2004

while

=WORKDAY(A1,5-(NETWORKDAYS(A1,WORKDAY(A1,0))>0))

gives:

3/26/2004

 

[santeria]

You have a good point, but it also where NETWORKDAYS comes into it's own.

Strictly speaking, the WORKDAY formula works as a strightforward calculation.
But to fully utilise the excel formulas, you need a Table of Holidays, use Networkdays as the intermediary calc form, unless you choose to have WORKDAY utilising the three argument form of the function that draws it's data from a Holiday table, that way the basic handy form of WORKDAY actually goes one better than using the additional step.

So, WORKDAY functions properly with the Holiday database, the minus one, and the data-base for holiday calcs.

 

[santeria]

Fair Point Aladin...
And What happens if the person observes Friday as a holiday ??

 

[Aladin Akyurek]

Fair Point Aladin...
And What happens if the person observes Friday as a holiday ??

I said: "leaving out the holidays for the moment". They can be added to the formula. The point I was trying to make is that -1 can be risky with a (for whatever reason) non-working day as a start date.

 

[srsteiner]

Thanks to all!!!! Excellent answers and thoughts to consider. This is a wonderful resource.