Determining work days and date ranges (Gantt related)

[davidstoll]

I'm working on a gantt chart where I want to be able to determine the number of "work days" which occur as of a certain date (which could be a weekend or holiday start). I have to subtract non working days such as holidays, which are listed in another tab.

The function I am using is WORKDAY()

The problem I am having is if the function (WORKDAY) crosses over a holiday from start date plus the number of work days, it is off by one and it counts the holiday as a work day even though it is in the list. It seems to only have a problem if the holiday is a Monday. If I edit the holiday list to change the holiday (Labor day 2018) to a Tuesday, it all works fine.

I have attached a mini sheet with basic data so you can see what I am talking about.

http://www.filedropper.com/gantt-test

I hope it is ok to link to a test sheet??? If not, I apologize. I understand the security aspect. Let me know how I can get the data if the link is not acceptable.

Thank you

 

[Dave Patton]

Workday should work OK even if the holiday is a Monday.

You did not specify the Start date or the number of days.

N.B. I did not open your linked info.

Excel 2010
	B	C	D	E	F
1	Start	Days			Holidays
2	Fri 31-Aug-18	6	Tue 11-Sep-18		Mon 03-Sep-18
3
9b

Cell Formulas
Range	Formula
D2	=WORKDAY(B2,C2,Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='9b'!$F$2

 

[davidstoll]

Yes, the sample has the start date and number of days.

 

[davidstoll]

That is how it should work, but my example shows that there is a slight problem and I can't figure out what it is. Could you take a look?

 

[Dave Patton]

Post an example like I did in post #2 ; I used MrExcel Html Maker.
or
Provide the same information in your reply and specify your expected result.

 

[davidstoll]

I cannot open the html file maker files for some reason. I'm sorry.


E______________________F____________________G__________H
"Scheduled Start"_________"Complete Date"_______"ignore"_____"DaysToComplete"

Sat 09/01/2018__________Fri 09/07/2018_________x__________4
Sun 09/02/2018__________Fri 09/07/2018_________x__________4
Mon 09/03/2018__________Fri 09/07/2018_________x__________4
Tue 09/04/2018__________Mon 09/10/2018________x__________4

09/03/2018 is a holiday in the holiday list.

"CompleteDate" formula for the last entry:
=WORKDAY(E15,H15,holidays!A$5:A$58)

Shouldn't the "CompleteDate" for the last entry be Fri 09/07/2018?

 

[Dave Patton]

Excel 2010
	A	E	F	G	H	I	J
1	Holidays	Start			Days
2	Mon 03-Sep-18	Sat 01-Sep-18	Fri 07-Sep-18		4		Tue-Fri
3		Sun 02-Sep-18	Fri 07-Sep-18		4		Tue-Fri
4		Mon 03-Sep-18	Fri 07-Sep-18		4		Tue-Fri
5		Tue 04-Sep-18	Mon 10-Sep-18		4		Wed-Fri +Mon
6
9b

Cell Formulas
Range	Formula
F2	=WORKDAY(E2,H2,Holidays)
F3	=WORKDAY(E3,H3,Holidays)
F4	=WORKDAY(E4,H4,Holidays)
F5	=WORKDAY(E5,H5,Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='9b'!$A$2

With the last row, the 4th date is Monday. wed thu Fri Mon =4

 

[davidstoll]

So, the "Start Date" is not included in the days worked? That is counter intuitive to me.

If my intent is to include the start date as the first worked day, then I need to subtract 1 in the formula...?

F2 =WORKDAY(E2-1,H2,Holidays)
F3 =WORKDAY(E3-1,H3,Holidays)
F4 =WORKDAY(E4-1,H4,Holidays)
F5 =WORKDAY(E5-1,H5,Holidays)

 

[Dave Patton]

Glad the problem is solved.

The Help information states
"Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date).