# Max 4 days per week betweeen two dates

#### john-paul

##### New Member
Hi,

Trying to calculate number of workdays (M-F) between two dates, where someone is employed for a max of 4 days per week.

So, for example, if contract period were 1st April 2020 to 28th April 2020:
- First week is Wed 1st. Thurs 2nd and Fri 3rd Apr, so would only be able to work 3 days only that week.
- Week 2-4 would be 4 days each week as they are full weeks
- Week Five of Mon 27 Apr and Tue 28 Apr, would be 2 days only.

So total days if max of 4 per week is 17.

Hoping someone can help

Thanks

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Special-K99

##### Well-known Member
Probably best to supply a description of how the days worked are indicated.
If you had a range of days from 1st April to 28th and an indicator (1 or 0) to say they've worked or not
then it would just be a case of SUMming the date range.

#### Eric W

##### MrExcel MVP
Try:

=NETWORKDAYS.INTL(A1,A2,"0000111")+(WEEKDAY(A1,16)>3)

#### Eric W

##### MrExcel MVP
Upon reflection, I realized that my formula will fail in one very specific situation:

If the start date is on a Tuesday, Wednesday, or Thursday, and if the end date is on a Tuesday, Wednesday, or Thursday OF THE SAME WEEK, the result will be 1 too high. So if you have contracts of 1-3 days, this could be an issue. The formula can be remediated if necessary.

#### john-paul

##### New Member

Thank you Eric W.
It is a great solution but I noticed there were a few instances it was a day off.
Also, what does "000111" mean?

#### Tetra201

##### MrExcel MVP
... I noticed there were a few instances it was a day off...
See if the following formula works for you:

=NETWORKDAYS(A1,A2)-MAX(0,NETWORKDAYS.INTL(WORKDAY.INTL(A1-1,1,"0111111"),A2,"1111011"))

The logic behind this formula is "find the number of regular workdays and decrease it by the number of Fridays after the first Monday."

#### Eric W

##### MrExcel MVP
Sorry, this thread kind of got away from me. Try Tetra's formula and see how that works. If you still have issues, please give us some examples where it doesn't work and one of us will surely figure something out.

#### john-paul

##### New Member
Thank you Tetra201, it works perfectly.
Hadn't used the INTL version before, thanks Eric W for suggesting that.

Replies
3
Views
547
Replies
2
Views
138
Replies
5
Views
225
Replies
9
Views
270
Replies
18
Views
321

1,127,827
Messages
5,627,124
Members
416,223
Latest member
RichardHell

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