Max 4 days per week betweeen two dates

john-paul

New Member
Joined
Nov 23, 2008
Messages
26
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Aug 18, 2015
Messages
10,640
Try:

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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
Joined
Nov 23, 2008
Messages
26

ADVERTISEMENT

Thank you Eric W.
It is a great solution but I noticed there were a few instances it was a day off.
If it can be adjusted please let me know.
Also, what does "000111" mean?
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
... 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
Joined
Aug 18, 2015
Messages
10,640
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
Joined
Nov 23, 2008
Messages
26
Thank you Tetra201, it works perfectly.
Hadn't used the INTL version before, thanks Eric W for suggesting that.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top