Rotating shift

I want to create a sheet which will tell me what days I'm off on a rotating schedule
The schedule is as follows

Sunday____Monday____Tuesday___Wednesday_Thursday__Friday_____Saturday__Sunday____Monday____Tuesday___Wednesday_Thursday__Friday_____Saturday
04 Sep 16__05 Sep 16__06 Sep 16__07 Sep 16__08 Sep 16_09 Sep 16__10 Sep 16_11 Sep 16__12 Sep 16__13 Sep 16_14 Sep 16__15 Sep 16_16 Sep 16__17 Sep 16
ON_________OFF_______OFF________ON_________ON______OFF________OFF______OFF________ON_______ON_______OFF_______OFF_______ON________ON

And it continues to rotate like this, I want to be able to type in any given date (past, present, or future) and have to automatically tell me if I'm on or off that day, can anyone help me accomplish this?

Or this:

Formula:

Please Login or Register  to view this content.

Start your shift with ON or OFF and after that you will get alternate:

Please Login or Register  to view this content.

If you want to start with double ON or OFF then just create one more shift at the start with opposite shift.

OK I'm not sure I understand how any one of these formulas works, I want to be able to have a blank cell where I could type in any date, then it will say if I'm off or on, Not sure how I could do that with either of these formulas. Am I missing something?

Attach a sample workbook that takes variate examples that you might run into. (Like what you mention above, what if you are sick how shifts continue etc).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Looking at your data it seems to me that if the week number is even the shift cycle goes (Monday - Sunday)

Off-Off-On-On-Off-Off-Off

and for an odd week number it goes

On-On-Off-Off-On-On-On

If this is so you could use the excle function "WEEKNUM" to set up a formula.

Alf

Originally Posted by Neil_

Paste my formula in a blank workbook in any other cell than A2 and type your date in A2.

OK Cool, with some slight modification on the order of the "off's and On's" it worked!

Originally Posted by Alf

Looking at your data it seems to me that if the week number is even the shift cycle goes (Monday - Sunday)

Off-Off-On-On-Off-Off-Off

and for an odd week number it goes

On-On-Off-Off-On-On-On

If this is so you could use the excel function "WEEKNUM" to set up a formula.

Alf

I get what you're saying, and I'm sure you can use WEEKNUM somehow, but I'm not good enough with Excel yet to devise a formula like that, how would I even start that?