1. ## 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. ## 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?

3. ## 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. ## 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

5. ## 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>

6. ## 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.

7. ## 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. ## 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. :=)

9. ## 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.

10. ## 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

11. ## 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
•