Formula to identify the number of days per month (incl & excl UK bank holidays).

[Hakunabrotata]

Hi all,

I was wondering if anyone could help me with a formula to determine:

-The number of days in a month (option to exclude UK Bank holidays)

-How many different days there were in that month (4 x mondays, 4 x tuesdays, 5 x wednesdays etc). Also.if there is the option to excluce UK Bank holidays, that would be great.

Essentially the source cell would just be your basic 'July-2024'.

Any help would be greatly appreciated.

 

[Kaper]

The basic formula (I expect you need workingdays) would be (and the date 1st July 2024 is in A1 - make sure it's a date, not just text which only looks like a date):

=NETWORKDAYS(A1,EOMONTH(A1,0),[range_of_cells_containing_UK_bank_holidays])

If you think of all days (including weekends) then

=EOMONTH(A1,0)-A1+1

and including weekends, but excluding bank holidays

=EOMONTH(A1,0)-A1+1-COUNTIFS([range_of_cells_containing_UK_bank_holidays],"<=" & EOMONTH(A1,0),[range_of_cells_containing_UK_bank_holidays],">=" & A1)

Number of Mondays:

=SUM(--(WEEKDAY(SEQUENCE(EOMONTH(A1,0)-A1+1, , A1), 2)=1))

Tuesdays:

=SUM(--(WEEKDAY(SEQUENCE(EOMONTH(A1,0)-A1+1, , A1), 2)=2))

(changed last comparision only), etc.
Mondays but not Bank Holidays

=LET(MyDays,SEQUENCE(EOMONTH(A1,0)-A1+1,,A1),SUM((WEEKDAY(MyDays,2)=1)*NOT(COUNTIF([range_of_cells_containing_UK_bank_holidays],MyDays))))

PS. [range_of_cells_containing_UK_bank_holidays] in all above formulas is just an address range in a sheet, like: Sheet2!G1:$G$11 or similar

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	01/07/2024							Holidays
2	Total 23	Mon 5	Tue 5	Wed 5	Thu 4	Fri 4		01/01/2024
3								29/03/2024
4								01/04/2024
5								06/05/2024
6								27/05/2024
7								26/08/2024
8								25/12/2024
9								26/12/2024
Sheet6

Cell Formulas
Range	Formula
A2:F2	A2	=LET(hols,H2:H13,d,WORKDAY(A1,SEQUENCE(NETWORKDAYS(A1,EOMONTH(A1,0),hols),,0),hols),w,WEEKDAY(d,2),HSTACK("Total "&ROWS(d),MAP({"Mon","Tue","Wed","Thu","Fri"},SEQUENCE(,5),LAMBDA(x,y,x&" "&SUM(--(w=y))))))
Dynamic array formulas.

 

[Scott R]

=LET( e, EOMONTH( A1, 0 ), hol, H2:H13,
dys, VSTACK( NETWORKDAYS.INTL( A1, e, 1, hol ), NETWORKDAYS.INTL( A1, e, REPLACE( "1111111", SEQUENCE( 5 ), 1, 0 ), hol )),
HSTACK( {"TOT";"Mon";"Tue";"Wed";"Thu";"Fri"}, dys )
)