# How to calculate work days for different work weeks

#### greyjester

##### New Member
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.

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

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

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

Replies
6
Views
145
Replies
3
Views
250
Replies
4
Views
175
Replies
3
Views
459
Replies
2
Views
222

1,196,340
Messages
6,014,721
Members
441,838
Latest member
ykg1991

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back