+ Reply to Thread
Results 1 to 5 of 5

Use networkdays INCLUDE weekends, Exclude holidays

  1. #1
    ronnomad
    Guest

    Use networkdays INCLUDE weekends, Exclude holidays

    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?

  2. #2
    Bernie Deitrick
    Guest

    Re: Use networkdays INCLUDE weekends, Exclude holidays

    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


    "ronnomad" <ronnomad@discussions.microsoft.com> wrote in message
    news:7F0F4142-87D5-40F4-9C2B-87FA750BD6CF@microsoft.com...
    > 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?




  3. #3
    ronnomad
    Guest

    Re: Use networkdays INCLUDE weekends, Exclude holidays

    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 Deitrick" wrote:

    > 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
    >
    >
    > "ronnomad" <ronnomad@discussions.microsoft.com> wrote in message
    > news:7F0F4142-87D5-40F4-9C2B-87FA750BD6CF@microsoft.com...
    > > 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?

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: Use networkdays INCLUDE weekends, Exclude holidays

    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)

    "ronnomad" <ronnomad@discussions.microsoft.com> wrote in message
    news:B87860A9-DF79-4DB4-BC5A-F9FC3996977F@microsoft.com...
    > 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 Deitrick" wrote:
    >
    > > 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
    > >
    > >
    > > "ronnomad" <ronnomad@discussions.microsoft.com> wrote in message
    > > news:7F0F4142-87D5-40F4-9C2B-87FA750BD6CF@microsoft.com...
    > > > 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?

    > >
    > >
    > >




  5. #5
    ronnomad
    Guest

    Re: Use networkdays INCLUDE weekends, Exclude holidays

    Bernie, Bob,

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

    Ron

    "Bob Phillips" wrote:

    > 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)
    >
    > "ronnomad" <ronnomad@discussions.microsoft.com> wrote in message
    > news:B87860A9-DF79-4DB4-BC5A-F9FC3996977F@microsoft.com...
    > > 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 Deitrick" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > "ronnomad" <ronnomad@discussions.microsoft.com> wrote in message
    > > > news:7F0F4142-87D5-40F4-9C2B-87FA750BD6CF@microsoft.com...
    > > > > 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?
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1