Calender / Roster Help please

[DDX]

Hi All

Need some help please. We are working on 28 day roster. With some off days in between. Then the cycle starts again. It looks like this
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Th[/TD]
[TD="align: center"]Fr[/TD]
[TD="align: center"]Sa[/TD]
[TD="align: center"]Su[/TD]
[TD="align: center"]Mo[/TD]
[TD="align: center"]Tu[/TD]
[TD="align: center"]We[/TD]
[TD="align: center"]Th[/TD]
[TD="align: center"]Fr[/TD]
[TD="align: center"]Sa[/TD]
[TD="align: center"]Su[/TD]
[TD="align: center"]Mo[/TD]
[TD="align: center"]Tu[/TD]
[TD="align: center"]We[/TD]
[TD="align: center"]Th[/TD]
[TD="align: center"]Fr[/TD]
[TD="align: center"]Sa[/TD]
[TD="align: center"]Su[/TD]
[TD="align: center"]Mo[/TD]
[TD="align: center"]Tu[/TD]
[TD="align: center"]We[/TD]
[TD="align: center"]Th[/TD]
[TD="align: center"]Fr[/TD]
[TD="align: center"]Sa[/TD]
[TD="align: center"]Su[/TD]
[TD="align: center"]Mo[/TD]
[TD="align: center"]Tu[/TD]
[TD="align: center"]We[/TD]
[/TR]
[TR]
[TD="align: center"]T[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]

I would like to build a calendar with the date and roster. If I select a date in next year, I would like to see on what shift I would be.

But I have no idea how to do this.
Thanks for the help

 

[Misca]

The following formula is one solution for a "Shift Calculator":

=MID("TNNNXDDDXXXNNXXDDDDXNNXXXXXX",MOD(MyDate-DayOne,28)+1,1)

DayOne = Known date that starts the cycle
MyDate = Any date after DayOne

I'd suggest you use cells to enter these dates.

Here's how it works: The MOD part returns the reminder of number of days divided by 28. Possible values are always between 0 and 27. Since we're using this reminder to fetch the Nth value from our "ShiftList" we need to add 1 to it ( = can't get the 0th value from the list).

 

[DDX]

Thanks a lot Misca

It helped a lot. Learned something new today.
Will go and try putting the two together.