Calculate number of workdays per week

[JackDanIce]

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

 

[JackDanIce]

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.

 

[Dave Patton]

Did you consider
=NETWORKDAYS.INTL(C12,EOMONTH(C12,0),1,Holidays)

 

[JackDanIce]

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.

 

[steve the fish]

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.

 

[JackDanIce]

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.

 

[JackDanIce]

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.

 

[JackDanIce]

Formula changed to:

=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