Use networkdays INCLUDE weekends, Exclude holidays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We work in a 24/7 environment. I calculate production for a seven days
schedule but, we do close for holidays. Networkdays, by default, excludes
weekends when calculating. I do not see any other date fuction that will
calculate the workdays, include weekends and exclude the days we are closed..
Does anyone have a work-around or another solution?
 
Ron,

With a list of your holidays in column A, entered as dates, (and nothign else in column A), you
could use a formula like

=C2-B2-(COUNTIF(A:A,">=" & B2)-COUNTIF(A:A,">" &C2))

where B2 has the start date and C2 has the end date. Note that you may want to add 1 to the
result....

HTH,
Bernie
MS Excel MVP
 
Bernie,

Thanks for the work-around. I'll try it but, based on your answer I'll
elaborate on my question. I look at inventory availablity and use a simple
formula to determine how many days of production I can get. I then convert
the days into a Day/Date format so that I can issue a report saying
"Production Through Sunday December 25, 2005".

What I am really trying to do is add the number of days of production
availablity for those days that we are closed. So, in the example, instead
of production through 12/25, production is realy through 01/04/06 because we
are closed 12/22-12/26 and again 12/30-1/3.

Networkdays would do this if I could count weekends.

As an aside, I have also worked with 123 and, in 123 the Networkdays allowed
the user to determine which days of the week to count (or not count).

Thanks,

Ron
 
Bernie's formula does that.

Assuming the holiday dates are in column A and the date to check is in B2,

="Production Through "&TEXT(B2,"dddd dd mmmm yyyy")&" =
"&B21-TODAY()-(COUNTIF(A:A,">=" & TODAY())-COUNTIF(A:A,">" &B2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Bernie, Bob,

Thanks, both of you. I'll try the solutions later today and post another
reply.

Ron
 
Back
Top