COUNTING WORKING DAYS IN A WEEK

[hakim_tanzil]

Hi there,

I've been trying to figure out how to count a working day in a week minus Sunday & Holiday

Our working days in a week start from every Friday - Thursday, and we usually determine our week by using formula =WEEKNUM(TODAY();15)

Now I'm trying to count how many working days in a certain week from the weeknum formula result

Appreciate if someone could help me on this

 

[Burrgogi]

It's hard to answer your question. What is the range of dates you're working wtih? In another word give us a start date and end date.

 

[Alex Blakenburg]

See if the formula in Column B gives you what you need.
The formula needs a year and will likely need some adjustment for the 1st and last week of the year.

20230311 WeekNum calc Week Dates hakim_tanzil.xlsx
	A	B	C	D	E	F	G	H
1	Year	2023
2			Visual Check	Visual Check		Holidays
3	Week Number	Working Day	Week Start	Week End		Dates
4	2	5	Fri 06-Jan-23	Thu 12-Jan-23		Wed 18-Jan-23		<-- Test Value Only
5	3	4	Fri 13-Jan-23	Thu 19-Jan-23
6	4	5	Fri 20-Jan-23	Thu 26-Jan-23
Summarised Calc

Cell Formulas
Range	Formula
B4:B6	B4	=NETWORKDAYS(DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15)-6,DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15),$F$4:$F$8)
C4:C6	C4	=DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15)-6
D4:D6	D4	=DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15)

 

[hakim_tanzil]

See if the formula in Column B gives you what you need.
The formula needs a year and will likely need some adjustment for the 1st and last week of the year.

20230311 WeekNum calc Week Dates hakim_tanzil.xlsx
	A	B	C	D	E	F	G	H
1	Year	2023
2			Visual Check	Visual Check		Holidays
3	Week Number	Working Day	Week Start	Week End		Dates
4	2	5	Fri 06-Jan-23	Thu 12-Jan-23		Wed 18-Jan-23		<-- Test Value Only
5	3	4	Fri 13-Jan-23	Thu 19-Jan-23
6	4	5	Fri 20-Jan-23	Thu 26-Jan-23
Summarised Calc

Cell Formulas
Range	Formula
B4:B6	B4	=NETWORKDAYS(DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15)-6,DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15),$F$4:$F$8)
C4:C6	C4	=DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15)-6
D4:D6	D4	=DATE($B$1,1,1)+(7*A4)-WEEKDAY(DATE($B$1,1,1),15)

Thanks Alex Blakenburg for your reply.

Using the NETWORKDAYS formula, how do I determine the start date & end date just by using the week number? Let's say I want to determine the working days on week #15, so the start & end date should be something like converting the weeknum into the start & end date

The date range is from Friday 17th of March 2023 to 23rd of March 2023, and a public holiday happen to be on 22nd of March 2023. The result is supposed to be 5 working days

 

[hakim_tanzil]

This seems like going back the logic, converting the weeknum to identify the start & end date

 

[bebo021999]

Book1
	A	B	C	D
1	Holidays		Week #	Working days
2	07 January 2023		12	5
3	22 March 2023
4
5
Sheet1

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS.INTL(DATE(YEAR(TODAY()),1,1)-CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),1,1)),2,3,4,5,6,0,1)+($C$2-1)*7,DATE(YEAR(TODAY()),1,1)-CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),1,1)),2,3,4,5,6,0,1)+($C$2-1)*7+6,11,$A$2:$A$5)

 

[hakim_tanzil]

It works. Thank you all