Max 4 days per week betweeen two dates

john-paul

New Member
Joined
Nov 23, 2008
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
... 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."
 
Upvote 0
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.
 
Upvote 0
Thank you Tetra201, it works perfectly.
Hadn't used the INTL version before, thanks Eric W for suggesting that.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top