+ Reply to Thread
Results 1 to 13 of 13

WorkDay Count Formula

  1. #1
    Registered User
    Join Date
    07-13-2006
    Posts
    6

    WorkDay Count Formula

    Hello There.

    I am using the work day count formula with the analysis toolpack. The formula does not count holidays. so i need to make changes to skip to the next workday when there is a holiday inplace obvously a good example coming up is christmas.

    Can someone please let me know how to input all the holidays in my formula ?

  2. #2
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    Ziggy M,

    Put your holidays in a range say A1:A5,

    =WORKDAY(B1,1,A1:A5)

    B1 = Start Date
    1 = number of days from start date
    A1:A5 are your excluded holidays.

    HTH

    Steve

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Thumbs up

    Quote Originally Posted by Ziggy M
    Hello There.

    I am using the work day count formula with the analysis toolpack. The formula does not count holidays. so i need to make changes to skip to the next workday when there is a holiday inplace obvously a good example coming up is christmas.

    Can someone please let me know how to input all the holidays in my formula ?
    Hi Ziggy M,

    You need the NETWORKDAYS formula, this takes care of holidays

    oldchippy

  4. #4
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    Ziggy M,

    My bad. Didn't read the post carefully. Old Chippy is right that you need the NETWORKDAYS function to count the days. Something like.

    =NETWORKDAYS(B1,B2,$A$1:$A$5)

    Where B1 and B2 are the start and end dates respectively and A1:A5 are your holidays.


    HTH
    Steve

  5. #5
    Registered User
    Join Date
    07-13-2006
    Posts
    6
    Ok and how do i get the holidays, in there..

    For example i have a project that starts on the Monday December 18th and I will need 7 work days to complete.. so how do i put that christmas 25th is a holiday so the formula does not count that day

  6. #6
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    Did you try putting 12/25/2006 in cell A1?

    Just a question, are you trying to determine the date that you have to complete your project or trying to count the number of days between two dates? If you need a completion date and are looking to exclude the holiday then you would use the WORKDAY function, not NETWORKDAYS. You did not state this info in your OP.

    Try,

    =WORKDAY("12/18/2006",7,"12/25/2006")

    or you can put your start date and holiday in cells to reference in your formula

    =WORKDAY(A1,7,C1)

    Where A1 = 12/28/2006 and C1 = 12/25/2006

    HTH

    Steve

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Quote Originally Posted by Ziggy M
    Ok and how do i get the holidays, in there..

    For example i have a project that starts on the Monday December 18th and I will need 7 work days to complete.. so how do i put that christmas 25th is a holiday so the formula does not count that day
    OK,

    Start date is a date that represents the start date.

    End date is a date that represents the end date.

    Holidays is an optional range of one or more dates to exclude from the working calendar, such as bank holidays and floating holidays. The list can be a range of cells that contains the dates.

  8. #8
    Registered User
    Join Date
    07-13-2006
    Posts
    6
    Ok can you give me an example.. Where do i put the holidays and how does the formula work....

  9. #9
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Quote Originally Posted by Ziggy M
    Ok can you give me an example.. Where do i put the holidays and how does the formula work....
    See screenshot
    Attached Images Attached Images

  10. #10
    Registered User
    Join Date
    07-13-2006
    Posts
    6
    Ok you guys have been very helpful so far but i don't think this will work. What i am trying to do is complete a timeline for example.

    Brainstorm Project = 2 days Start Date =A1

    so i have 2 days to complete a project that starts on December 23 So i want it to count 2 workdays, but since December 25th is a holiday as well as the 26th the actual date complete should read December 27th. How do i accomplish that ?

  11. #11
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Quote Originally Posted by Ziggy M
    Ok you guys have been very helpful so far but i don't think this will work. What i am trying to do is complete a timeline for example.

    Brainstorm Project = 2 days Start Date =A1

    so i have 2 days to complete a project that starts on December 23 So i want it to count 2 workdays, but since December 25th is a holiday as well as the 26th the actual date complete should read December 27th. How do i accomplish that ?
    See screenshot using WORKDAY formula
    Attached Images Attached Images

  12. #12
    Registered User
    Join Date
    11-07-2006
    Posts
    3

    multiple holidays

    using this formula, what if you wanted to have multiple holidays, such as the entire week of chrismtas excluded (dec 25-29)?

  13. #13
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Quote Originally Posted by reneedubois
    using this formula, what if you wanted to have multiple holidays, such as the entire week of chrismtas excluded (dec 25-29)?
    Just a minor adjustment
    Attached Images Attached Images

+ 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