Calculate number of workdays per week

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Hi,

Need assistance please with 2 formula to calculate dates and number of working days in a given week.

C2: 2019 (manually entered)
C8: =IF(WEEKDAY(DATE($C$2,1,1),2)<6,DATE($C$2,1,1),DATE($C$2,1,1)+7-(WEEKDAY(DATE($C$2,1,1)+1)))
(returns the 1st Jan if not a weekend)

C9:C12: =$C8+(7-WEEKDAY($C8,2)+1) (all Mondays in Jan 2019)
C13: 01/02/2019 (1 Feb 2019)
C14:C17: =$C13+(7-WEEKDAY($C13,2)+1) (all Mondays in Feb 2019)
C18: 01/03/2019 (1 Mar 2019)
C19:C22: =$C18+(7-WEEKDAY($C18,2)+1) (all Mondays in Mar 2019)

First formula request is a single formula for C9 to year end, which returns either 1st of a given month if not a weekend and every Monday's date for given year (C2)

D7:H7 contain office locations

Each intersection of a date (C8:C62) vs office location (D7:H7) should show the number of working days in that week, capped by month end and take into account any public holidays for the office location (there is a separate table of locations and public holidays)

E.g. 1 Jan 2019 (C8) for London (D7) would show value 3 in D8 because there are 3 working days including public holidays for UK for week of 1 Jan 2019

21 Jan 2019 (C11) for London (D7) would show 5 in D11
28 Jan 2019 (C12) for London (D7) would show 4 in D12 4 working days that week until end of month for UK
1 Feb 2019 (C13) for London (D7) would show 1 in D13 1 working day that week for new month for UK
4 Feb 2019 (C14) for London (D7) would show 5 in D14 5 working days that week for given month for UK

Second formula request is to calculate the count of workdays for a given date, taking into account any public holidays for that week and location.

TIA for any assistance,
Jack
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
First formula solved as:
=IF(AND($C7+(7-WEEKDAY($C5,2)+1)>EOMONTH($C7,0),WEEKDAY(EOMONTH($C7,0),2)<6),DATE($C$2,MONTH($C7)+1,1),$C5+(7-WEEKDAY($C7,2)+1))

Dragged from C8:C64

Hoping for help with second formula to calculate count of days in work week, accounting for public holidays and month ends.
 
Upvote 0
Did you consider
=NETWORKDAYS.INTL(C12,EOMONTH(C12,0),1,Holidays)
 
Upvote 0
Considered yes, created a working formula, no!

Guess wasn't sure how it would work with the date list including the 1st of each month if weekday + every Monday date. Will try and revert, thanks for suggestion though.
 
Upvote 0
So the first formula contains a mix of mondays and the first of the month? If ive read right this may work:

=IF(C7="",WORKDAY(DATE($C$2,1,1)-1,1,holidays),IF(MONTH(WORKDAY(C7+(7-WEEKDAY(C7,2)+1)-1,1,holidays))<>MONTH(C7),WORKDAY(DATE($C$2,MONTH(C7)+1,1)-1,1,holidays),WORKDAY(C7+(7-WEEKDAY(C7,2)+1)-1,1,holidays)))

For the 2nd where you say you have a given date you could use:

=NETWORKDAYS(A1-WEEKDAY(A1,3),A1-WEEKDAY(A1,3)+4,holidays)

with a date in A1.
 
Upvote 0
Hi Steve, thanks for reply.

Yes first columns are all Mondays and all 1st of the Month, if the day is a weekday, working formula I now have is:
=IF(AND($C7+(7-WEEKDAY($C5,2)+1)>EOMONTH($C7,0),WEEKDAY(EOMONTH($C7,0),2)<6),DATE($C$2,MONTH($C7)+1,1),$C5+(7-WEEKDAY($C7,2)+1))
see #2

Thanks initially to @Dave, I started using NETWORKDAYS (got as far as: =NETWORKDAYS(D5,DATE($D$1,MONTH(D5),5))).. and then met a friend in a pub!

Your suggestion appears to work, thank you and I'll reply if any further issues.
 
Upvote 0
Not quite working unfortunately :(

Last Monday in January 2019 is 28/01/2019 This should show a value of 4 workdays left in that workweek, capped by month-end 31 Jan (Thursday)

Next date (out of order/not a Monday) is 01/02/2019 this should show a value of 1 workday left in that week - both show 5

Formula I'm using (Holidays argument is fixed range) is

=NETWORKDAYS($D10-WEEKDAY($D10,3),$D10-WEEKDAY($D10,3)+4,U$6:U$52)
=NETWORKDAYS($D11-WEEKDAY($D11,3),$D11-WEEKDAY($D11,3)+4,U$6:U$52)

I think your formula is calculating the Monday and Friday (as adjusted dates i.e. -WEEKDAY part) and finds number of workdays between start and end, but doesn't cap out if month-end is reached.
 
Last edited:
Upvote 0
Formula changed to:
Code:
=NETWORKDAYS(IF(DAY($D10)<>1,$D10-WEEKDAY($D10,3),$D10),MIN(EOMONTH($D10,0),$D10-WEEKDAY($D10,3)+4),X$6:X$52)
which returns 4 and 1 for dates 28/01/2019 and 01/02/2019 respectively
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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