On call schedule

[txlawdog]

I am trying to create a formula that checks one cell for the day of the week and another cell to determine if the week of the year is an even number or and odd number. It would then need to select a number from a key that has been set up on another sheet. I have attached a screen shot of the calendar and weekday rotation for reference. The on call schedule is set up so that an investigator is on call on a weekday every other week and Sat and Sun every nine weeks. This has stumped me thus far. I am not a expert at Excel, yet my supervisor thinks I am and asked me to create the calendar.

 

[hajiali]

Sorry for the late reply. I had to create couple of Helper cells that will do what your needing.

1st Helper

Workbookx.xlsm
	AW
22	WEEKEND ONCALL
23	12
24	4
25	5
26	6
27	7
28	8
29	9
30	10
31	11
Sheet4

2nd Helper does the custom Week Number.

Workbookx.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
5	WEEK #		23	23	23	23	23	24	24	24	24	24	24	24	25	25	25	25	25	25	25	26	26	26	26	26	26	26	27	27	27	27	27
Sheet4

Cell Formulas
Range	Formula
D5:AH5	D5	=IF(TEXT(D3,"DDD")="SUN",WEEKNUM(D3)-1,WEEKNUM(D3))

3rd Helper: does the Week conversions:

Workbookx.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
2	WEEK CONVERTION		5	5	5	5	5	6	6	6	6	6	6	6	7	7	7	7	7	7	7	8	8	8	8	8	8	8	9	9	9	9	9
Sheet4

Cell Formulas
Range	Formula
D2:AH2	D2	=IF(INT(D5/9)=D5/9,9,IF(D5>9,D5-(9*(ROUNDDOWN(D5/9,0))),D5))

Then the ON CALL Formula Becomes:

Workbookx.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
33	ON CALL INVESTIGATOR			4	7	11	7	7	12	6	9	10	0	8	8	5	8	4	7	11	9	9	12	6	9	10	0	10	10	5	8	4	7	11
Sheet4

Cell Formulas
Range	Formula
D33:AH33	D33	=IF(OR(TEXT(D3,"DDD")="SAT",TEXT(D3,"DDD")="SUN"),INDEX($AW$23:$AW$31,D2),IF(ISEVEN(WEEKNUM(D3)),INDEX($AM$24:$AQ$24,,MATCH(TEXT(D3,"DDDD"),$AM$23:$AQ$23,0)),INDEX($AR$24:$AV$24,,MATCH(TEXT(D3,"DDDD"),$AR$23:$AV$23,0))))

you can place the Helper cells to any cells you choose. I just used those cells. To make it Invisible to make it less information appear to users you can hide those rows or use the Custom Format option using the link HERE

Let me know if this helps