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]

can you provide an example of this.

 

[txlawdog]

	ECSO CID Schedule														January/2022
DAY OF MONTH:			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
			Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
ADMIN
Lt. Thompson	110	M-F 8-5			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W
PERSONS CRIMES
Sgt. Hundley	##	M-TH 8-7			W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W				W
CAPRS
Inv. McIntosh	##	M-TH 8-7			W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W				W
Inv. Key	131	TU-F 8-7				W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W
CAC
Inv. Clay	##	M-TH 8-7			W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W				W
SOR/JAIL CASES
Inv. Westmoreland	##	M-TH 8-7			W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W				W
PROPERTY CRIMES
Sgt. Fitzgerald	121	TU-F 8-7				W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W
WEST SIDE
Inv. Pollins	##	TU-F 8-7				W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W
Inv. Atilano	##	M-TH 8-7			W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W				W
EAST SIDE
Inv. Houston	##	TU-F 8-7				W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W								WEEK 1					Week 2
Inv. Pearson	##	M-TH 8-7			W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W				W					Monday	Tuesday	Wednesday	Thursday	Friday	Monday	Tuesday	Wednesday	Thursday	Friday
FINANCIAL/FRAUD																																						12	6	9	10		5	8	4	7	11
Inv. Shelton	##	TU-F 8-7				W	W	W	W				W	W	W	W				W	W	W	W				W	W	W	W
CRIME SCENE
CSI Katie Gildner	##	M-F 8-5			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W
PROPERTY TECH
Holly Hatcher		M-F 8-5			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W
CASE CLERK
Laura Gaither		M-F 8-5			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W	W	W	W	W			W
INVESTIGATORS ON DUTY:			0	0	7	12	12	12	6	0	0	7	12	12	12	6	0	0	7	12	12	12	6	0	0	7	12	12	12	6	0	0	7
ON CALL INVESTIGATOR			11	11	#	6	9	#	4	4	4	5	8	4	7	11	5	5	#	6	9	#	6	6	6	5	8	4	7	11	7	7	#
ON CALL SGT.			Sgt. Fitzgerald		Sgt. Hundley							Sgt. Fitzgerald							Sgt. Hundley							Sgt. Fitzgerald							Sgt. Hundley
WEEK OF THE YEAR:			1	2	2	2	2	2	2	2	3	3	3	3	3	3	3	4	4	4	4	4	4	4	5	5	5	5	5	5	5	6	6
TIME OFF KEY:					T	TRAINING				C	COMP				V	VACATION				H	HOLIDAY				P	PERSONAL				S	SICK
CALL SCHEDULE KEY:			1	THOMPSON				3	HUNDLEY				5	PEARSON				7	ATILANO				9	WESTMORELAND					11	Pollins
			2	FITZGERALD				4	CLAY				6	MCINTOSH				8	SHELTON				#	Key					12	Houston

 

[txlawdog]

I have posted a mini sheet above. The part I am interested in is the "On Call Investigator"

 

[txlawdog]

I have posted a mini sheet above. The part I am interested in is the "On Call Investigator"

The on call key is on the right side of the sheet

 

[hajiali]

Try this for the weekday:

WorkbookY.xlsm
	F
33	5
Sheet4

Cell Formulas
Range	Formula
F33	F33	=IF(ISODD(WEEKNUM(F3)),INDEX($AM$24:$AQ$24,MATCH(TEXT(F3,"DDDD"),$AM$23:$AQ$23,0)),INDEX($AR$24:$AV$24,MATCH(TEXT(F3,"DDDD"),$AR$23:$AV$23,0)))

Note that Excel will have week 2 starts on 1/2/22 because 1/1/2022 starts on Saturday

Im still confused on the Sat and Sunday

 

[hajiali]

Note that Excel will have week 2 starts on 1/2/22 because 1/1/2022 starts on Saturday

So therefore 1/2/22 - 1/7/22 is considered an even number since Excel will register it as 2nd week and not 1st week. since your starting on Monday anyways I would change

IF(ISODD(WEEKNUM(F3)),INDEX($AM$24:$AQ$24,MATCH(TEXT(F3,"DDDD"),$AM$23:$AQ$23,0)),INDEX($AR$24:$AV$24,MATCH(TEXT(F3,"DDDD"),$AR$23:$AV$23,0)))

to

IF(ISEVEN(WEEKNUM(F3)),INDEX($AM$24:$AQ$24,MATCH(TEXT(F3,"DDDD"),$AM$23:$AQ$23,0)),INDEX($AR$24:$AV$24,MATCH(TEXT(F3,"DDDD"),$AR$23:$AV$23,0)))

and if you want to give more explanation on weekend we can add another if statement for that to the above formula.

 

[txlawdog]

The weekends are on a rotating schedule of 9 weeks. The numbering system starts with 4 and goes to 12. The even week FRI is covered by the weekend on-call. For example; this year #12 was on call the first weekend, the following weekend #4 was on call and covered Friday, as well as Sat and Sun. #5 was next, but did not cover Friday as it was covered by the weekday schedule.

 

[hajiali]

The even week FRI is covered by the weekend on-call

Sorry for the late reply But im still lost how this year #12 begin on the first weekend. can you provide more details.

 

[txlawdog]

This is because the on-call rotation for weekends does not restart at the beginning of the year. It continues from the previous year. Therefore the 1st weekend was #12.