What working day of the month a particular date is?

[benposaner]

Hi

Sure it's simple but can I use a formula to workout what working day of the month a particular date is?
Assuming Working week is Mon-Fri and Bank holidays are excluded.

For example today 7th January is 4th working day of the month. (1st is Bank Hol and 2nd & 3rd are weekend)
So 1st working day would be have been the 4th Jan, etc.

So the formula should return working day 14 for 21st Jan 2021

I've tried using WORKDAY.INTL, to also exclude weekends and Holidays, but can't figure out how use the (start_date, days) in the formula to tell me what the corresponding working day of the month would be.

Any help would be great.
Thanks.
Ben

 

[etaf]

networkday()

=NETWORKDAYS(DATEVALUE(1&"/"&MONTH(A1)&"/"&YEAR(A1)),A1,[Holidays])

DATEVALUE(1&"/"&MONTH(A1)&"/"&YEAR(A1))
Will pickup the first of the month

 

[benposaner]

That's great, thanks.

 

[etaf]

you are welcome

 

[Dave Patton]

alternatives

T202101a.xlsm
	A	B	C
1	7-Jan-21	4	4
2	21-Jan-21	14	14
5a

Cell Formulas
Range	Formula
B1:B2	B1	=NETWORKDAYS(EOMONTH(A1,-1)+1,A1,Holidays)
C1:C2	C1	=NETWORKDAYS.INTL(EOMONTH(A1,-1)+1,A1,1,Holidays)

Named Ranges
Name	Refers To	Cells
'5a'!Holidays	='5a'!$M$1:$M$7	B1:C2

 

[benposaner]

Many thanks, these will be great!