Show current week of rotation between two dates?

[Searshc]

I'm looking for a formula that will let me see the current week of a rotation based on two dates. Basically, I have a sync date, and a new sync date with a set number of weeks in the rotation--say, 2-weeks, 4-weeks, 12-weeks (whatever) that a routing computer uses to send my technicians to different areas. When I need to update the technician to a new area, I need to see which week of that rotation I'm in. I want to be able to enter this information into excel so it can calculate what the current week rotation is--some of the data I have goes back to 2008, and it makes it very hard to see where the system thinks the current rotation is. I was hoping excel could help, but I'm stuck at coming up with a formula. Many thanks for any assistance!

 

[Searshc]

start date = Jan 20 2009 pattern is work 5 days, 1 day off, work 4 days, 1 day off, work 3 days, 3 days off.
= work 12 days with 5 days off = 17 day cycle.

number of days from Jan 20 2009 is easily calculated, divide by 17, integer the answer and from there calculate how many days into the current cycle.

Is it more complicated than that?

It is based on a weekly rotation, not days. For example--I can start a sync date on Monday or Friday (this week), but the sync date will still register this as week 1, as they both occur in the same week. Next week would be week 2 of a rotation, etc. So when I have a rotation that has something crazy--say, 12 weeks that dates back to 2/15/12 (date randomly chosen), I need to see which week of rotation I'm looking at, independent of the days that have passed.

 

[NoSparks]

I can start a sync date on Monday or Friday (this week), but the sync date will still register this as week 1, as they both occur in the same week. Next week would be week 2 of a rotation, etc

.

"Sync date" is used twice in this statement. Is it reasonable to think they do not refer to the same date, that the first refers to the rotation start date and the second to the date in question as to what week of the rotation it is in?

Are the week 1 and week 2 in the above quote correct or incorrect?

when I have a rotation that has something crazy--say, 12 weeks that dates back to 2/15/12 (date randomly chosen), I need to see which week of rotation I'm looking at

In this example, is today (5/21/2013) equal to day 7 of week 6 of the 12 week rotation?

 

[oldbrewer]

I am sorry that I still do not understand what you are trying to do. Try to explain it in plain english avoiding jargon like sync date, rotation, etc.

 

[NoSparks]

I am sorry that I still do not understand what you are trying to do. Try to explain it in plain english avoiding jargon like sync date, rotation, etc.

I'll second that. This just doesn't seem that it should be so complicated. Obviously I'm missing something.

 

[Searshc]

I'll second that. This just doesn't seem that it should be so complicated. Obviously I'm missing something.

First of all, let me say thank you in advance for your patience. I'll see if I can re-phrase this:

I have weekly rotations on a schedule for my staff. The start date can be any day in the week, but the system does not care about the day, just that we have week 1, week 2, week 3, etc. Now we judge this rotation by 'x' number of weeks--a random number where I need people to be at a given time. Again--the date of start is unimportant--weeks start on Sunday, so if I started someone on Saturday, it would be week 1, then on Sunday it would become week 2.

Over time, we need to make changes to the existing rotation, but we have to look up the first week of rotation, and count off successive weeks on a calender to make sure the changes we make match where we are in the rotation. So if we have a 12-week rotation, I have to count 12 weeks, then count 12 again, from whenever the start date was, to the 'current week'. I may also have to make changes to a future point in the rotation, say, 2 weeks from now, and have to count to whenever I plan to effect my changes.

I want to see about setting up a formula, a chart, and array--ANYTHING that will let me post the date we started the rotation, calling it 'week 1'. Then on Sunday count as 'week 2', and so on. It needs to let me dynamically say that a rotation is 'x' amount of weeks, and based on those items, tell me which week of rotation I would be in on another entered date.

Example: You started a 10-week rotation on 1/12/13. If I have to make changes on 6/4/13, which week (not day) of the rotation would I be in? It's week 2, when you count it on the calender, accepting that Sunday starts a whole new week, regardless of when the day the rotations started. That rotation can be as little as 2 weeks, as much as 48--but the day of start is scattered all over, and requires that we manually count every time.

 

[NoSparks]

OK, the way I interpret this now, is that the first day of every week falls on Sunday, and every week will have 7 days except for the very first week of the rotation which will be less than 7 days unless the rotation start date was a Sunday and the only thing you are interested in is the week number in the rotation.

Have I got it now?

 

[NoSparks]

Try this, D2 will be the week in the rotation for the date in C2

A2 = Number of weeks in rotation
B2 = Start date of rotation
C2 = The date in question
D2 =IF(MOD(INT((C2-(B2-WEEKDAY(B2,1)+1))/7),A2)=0,1,MOD(INT((C2-(B2-WEEKDAY(B2,1)+1))/7),A2)+1)

 

[oldbrewer]

If Tom started his rotation on a wednesday and works 4 days at town1 then has 2 days off, then works 4 days at town 2 and has 2 days off, and so on for another 18 towns thus ending his rotation. This takes 20 x 6 days = 120 days = 17 weeks and 1 day. Does he get extra days off friday to tuesday before starting over? Does he start over on the following wednesday and work 4 days at town1? And on any particular date you want to know what week number of his rotation he is in ?

 

[Searshc]

That's exactly it--yes!

 

[Searshc]

Try this, D2 will be the week in the rotation for the date in C2

A2 = Number of weeks in rotation
B2 = Start date of rotation
C2 = The date in question
D2 =IF(MOD(INT((C2-(B2-WEEKDAY(B2,1)+1))/7),A2)=0,1,MOD(INT((C2-(B2-WEEKDAY(B2,1)+1))/7),A2)+1)

NoSparks, I would kiss you if my HR department was really, really clear in that 4-hour lecture+movie we had to watch. If you're ever in Raleigh/Durham NC--I owe you a beer.