Rotating work schedule

2015
Sun Mon Tues Wed Thur Fri Sat Week Weekday
Weekday 1 2 3 4 5 6 7
Week 1 26 Oct 14 OFF ON ON OFF OFF ON ON Date 04 Feb 15 1 4
Week 2 02 Nov 14 ON OFF OFF ON ON OFF OFF

So this is my rotating work schedule, as of right now I could type a date on the right and it will give me the Week # and Weekday # and then I have to look on the chart on the right to see if I'm off or not. I want to make it so that I type in a date and it just automatically tells me "OFF" or "ON." This is driving me nuts, please help!

Hi,
Use this sheet, do not change anything that is not highlighted yellow or it will stop working. If you accidentally write over cell B13 (where it tells you whether you are on or off), just copy and paste this code into it:

=INDEX(OFFSET(INDEX(2:2,MATCH(B12-VALUE(INDEX($B$15:$B$21,MATCH(TEXT(B12,"ddd"),$A$15:$A$21,0))),2:2,0)),1,0,7),MATCH(TEXT(B12,"ddd"),$A$3:$A$9,0))

Again, if you change anything that is not highlighted yellow, this sheet will not work. Otherwise, it works perfectly.

Hope this helps! If you like it, Please mark the thread as solved!