Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workdays Formula (2000)

    I'd like to get the formula to cut and paste for adding workdays from a start date, to an end date, with Saturdays being counted as half a day, Mon - Friday as whole days, and work holidays excluded. I found the NETWORKDAYS function but am not advanced enough to figure out how to exclude the holidays and get Saturdays in as half, even after reading the help articles. Thanks in advance.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Workdays Formula (2000)

    From Help "NETWORKDAYS(start_date,end_date,holidays) returns the number of whole working days between start_date and end_date, excluding weekends and any dates identified in holidays." So your half days on Saturday are a problem. But it sounds like you want a columnar list of dates meeting the workday criteria from a start date to an end date, correct?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays Formula (2000)

    Yes, I am trying to get the formula because currently I am doing it manually, actually counting the days on a calendar, but the sheet is getting extensive and very time consuming to be adding up like that. The addition would be across a row of dates. The process starts with a start date in one column, goes across 7 more, with a date that each process is completed and then at the end I need to total the workdays (using the first and the last column as my start and finish dates,) which in this business happens to be 5 1/2 workdays. The Saturdays as half seem to be a problem.

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays Formula (2000)

    Hi There

    =NETWORKDAYS(B2,B3,D27)+(INT(DATEDIF(B2+(7-WEEKDAY(B2)),B3,"D")/7)+1)/2

    Think that does it. NETWORKDAYS doesn't know about Saturdays, hence the tweek. Spreadsheet attached.

    B2 = Start Date
    B3 = End Date
    D27 = Your list of holiday dates. Extend/used a named range to suit.

    Regards
    Peter
    Attached Files Attached Files

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Workdays Formula (2000)

    Whoa, you mean your data layout is like this?

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center valign=bottom>1</td><td align=right valign=bottom>12/31/2001</td><td align=right valign=bottom>1/1/2002</td><td align=right valign=bottom>1/2/2002</td><td align=right valign=bottom>1/3/2002</td><td align=right valign=bottom>1/4/2002</td><td align=right valign=bottom>1/5/2002</td><td align=center valign=bottom>2</td><td align=right valign=bottom>Monday</td><td align=right valign=bottom>Tuesday</td><td align=right valign=bottom>Wednesday</td><td align=right valign=bottom>Thursday</td><td align=right valign=bottom>Friday</td><td align=right valign=bottom>Saturday</td><tr><td align=center valign=bottom>3</td><td valign=bottom>
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Workdays Formula (2000)

    hlstew, Peter's solution gives you what you need in terms of counting the workdays, but do you actually need to lay the days out in the spreadsheet to track certain data? It would help to know if you have a required data layout.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays Formula (2000)

    If your data is arranged as John suggests...

    Add a column G. In G1 put:

    =NETWORKDAYS(A1,F1,Z1:Z20)+.5

    Copy that formula down column G to all appropriate cells. (I'm assuming that the range Z1:Z20, or whatever you want to use, has your holiday dates in it.)

    You can now total Colum G.

    Hope that helps,

    Regards
    Peter

  8. #8
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays Formula (2000)

    I'm out of here for the weekend but this is an example spreadsheet. They are trying to track cycle time in a manufacturing process so it looks like this. I haven't had time to try out the solutions from your replies yet but will be at it again on Monday. Have a good weekend and thanks for your help. :=)
    Attached Files Attached Files

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Workdays Formula (2000)

    To which I can only add that you can do some funky conditional formatting with "formula is"

    ISERROR(MATCH(thiscell,$holidaylist,0))

    to color or hide holidays if you wish.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays Formula (2000)

    Attached is a re-worked spreadsheet using formula that I suggested. I've ommited a holiday calendar at the moment, you can add it yourself. I not that I get a VERY different answer in row 6? one of the others is out by 0.5 too?

    Regards
    Peter
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays Formula (2000)

    =IF(COUNTIF(lstHolidayDates,G3),TRUE,FALSE)

    Where 'lstHolidayDates' is the named range holding your holiday dates... and G3 is the current cell. That'll work with conditional formatting.

    Regards
    Peter

Posting Permissions

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