How to calculate work days for different work weeks

[greyjester]

I am trying to develop a formula that will allow me to calculate the number of work days for work weeks with different begin/end dates. For example, it is easy to use the networkdays functions to calculate the number of monday-friday work days for the period Jan 1-15 2009.

However, if I have an overseas work week that runs Sunday-Thursday, how can I modify the networkdays formula to return the number of workdays that follows that work week? I also have work weeks that run Saturday-Wednesday that need the same problem solved.

I am a complete loss as how to continue and need a repeatable formula so I am not dragging out a calendar and manually counting days each time I need to recreate this process.

Any help that could be provided would be greatly appreciated.

Greyjester

 

[xld]

Try

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1,2,3,4,5}))
-SUMPRODUCT(--(COUNTIF(holidays,(ROW(INDIRECT(start_date&":"&end_date)))*(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1,2,3,4,5}))))

The array constant {1,2,3,4,5} is the days of the week to include, 1 is Monday.

 

[greyjester]

Thank you for your reply, but I am not sure how to implement your solution.

I can't upload my spreadsheet so here is a description of what I am using:

Column 1 - Cycle start date, example 1/1/09
Column 2 - Cycle end Data, example 1/15/09
Column 3 - M-F work days, formula networkdays(A2-B2) we are not worried about holidays
Column 4 - Sunday-thursday workdays
Column 5 - Sat-Wed Workdays.

I am not versed in array formulas, so any additional help you could provide would be appreciated. Would be willing to send you a copy of my spreadsheet outside of this forum if you think it would be beneficial, just need an email address.

Regards,
Greyjester

 

[xld]

It isn't an array formula, just substitute the cell names with the actual cells, the array constant with the appropriate values and go.

 

[barry houdini]

Hello greyjester

Where the work week is 5 consecutive days (like Sunday to Thursday) then you can just "offset" NETWORKDAYS, i.e for Column 4 - Sunday-Thursday workdays just use

=NETWORKDAYS(A2+1,B2+1)

for Saturday to Wednesday you can offset by 2, i.e.

=NETWORKDAYS(A2+2,B2+2)

For work weeks of any length you can use variations on this formula

=SUM(INT((WEEKDAY(A2-{2,3,4})+B2-A2)/7))

{2,3,4} defines the days where 1 = Sun through to 7 = Sat so the above counts Mon to Wed