Formula to check if day matches then return value..

[AllyBobs]

Im driving myself crazy as a newbie to using excel in this way... I have a spreadsheet picture below, it has conditional formatting to block out weekends and holidays to prevent adding breakfast club sessions... all I want to do is find an easy way for it to populate N in the relevant days each person attends regularly. Bonus points if can across the multiple months and avoid the blocked holiday dates. I added check boxes in hopes of finding a way for it to check if day matches then add N but after 24 hours I need help.

Hoping this makes sense

Thanks

 

[skillilea]

How about something like this?

MrExcel.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1															February
2							HIDE THESE COLUMNS
3	HIDE THIS ROW													5	6	7	1	2	3	4	5	6
4	Defaults						Lookup Vals							Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
5	Mon	Tue	Wed	Thu	Fri		Mon	Tue	Wed	Thu	Fri			1	2	3	4	5	6	7	8	9
6	x			x	x		2			5	6		Dude 1	N	N			N			N	N
7		x	x					3	4				Dude 2						N	N
8				x	x					5	6		Dude 3	N	N						N	N
9	x			x			2			5			Dude 4	N				N			N
Sheet1

Cell Formulas
Range	Formula
O3:W3	O3	=WEEKDAY(O4)
O5:W5	O5	=O4
O6:W9	O6	=IF(ISNUMBER(MATCH(O$3, $H6:$L6, 0)), "N", "")
H6:L9	H6	=IF(B6= "x", WEEKDAY(H$5), "")

 

[AllyBobs]

Thank you... off to have a go! will let you know!

 

[AllyBobs]

This didnt work for me unfortunately

 

[skillilea]

What specifically didn't work?

 

[AllyBobs]

Late reply...It seemed to clear my conditional formatting and came up with #Name error...

I have thought of another option which counts how many Mondays, Tuesdays etc individually in the month shown but cant figure out how to tell it to remove holidays

hmmm

 

[AllyBobs]

For example I could use the below but need it to exclude the holdays...
For a Count of Mondays:

=NETWORKDAYS.INTL(start,end,"0111111")
For a Count of Tuesdays:

=NETWORKDAYS.INTL(start,end,"1011111")
For a Count of Wednesdays:

=NETWORKDAYS.INTL(start,end,"1101111")
For a Count of Thursdays:

=NETWORKDAYS.INTL(start,end,"1110111")
For a Count of Fridays:

=NETWORKDAYS.INTL(start,end,"1111011")

 

[skillilea]

Look at all the formulas in the green color. You need to make sure they are included.
I'm pretty sure the sheet works to your specs.

Worry about conditional formatting and framing after you get the functionality working.