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]

Yes, but it's all divided by days, and I am concerned that because it is not reflecting the week number or something that if something is shorter than 7 days, say a sync date on a Friday with a new date on a Monday, that it will not reflect the current correct week.

 

[Searshc]

Sync dates are usually random days in a given week. It all depends on when we needed to have people on the move. It can be on any given month/day, or year since 2007 or so--and it is not always on the first day of the week, we've had them start on Saturdays, and sometimes on Mondays. Any day of the week is fair game--and so if it uses a /7 to check against, it will not always return the correct day of rotation, depending on when the old one started, and a new one is due to start.

 

[oldbrewer]

The problem for me is I still do not understand what you are trying to do. It is very hard for an "expert" to explain something that a "layman" will understand. In the UK we have shift systems like work 4 rest 2 work 3 rest 2 work 4 rest 2 work 3 rest 4 then start again. That pattern can continue for years. You talk about a pattern starting on a saturday and ending say wednesday of the following week. That is just work sat sun mon tue wed then have 2 days off. Why is that a problem to you?

 

[NoSparks]

Sorry, not able to help you, know where your trying to go but can't figure out where you're starting.

Perhaps question should have been sync what to what?

Good luck with your project hope someone else can assist.

 

[Searshc]

Many thanks for the help so far--it is appreciated. Pretty much we have zip codes where we route our technicians, and these get updated pretty often. The first sync date is the date that we placed the entire rotation into effect, and after awhile it becomes so bloated or misused that we have to erase it and start a new rotation. BUT, we can't just stop the existing one until we've run the calls, so we create a second date to tell the system to start a new rotation, deleting the ones after the new sync date. Normally this is just a week or two, but we have a lot (as in, hundreds) that have 12-week, 20-week, even a single 45-week rotation.

Since the system runs by the week number of the rotation, we need to find out when the first sync date was so that we can begin the new rotation in the right spot--preferably an empty area with no calls. Otherwise, we can build a route for the technician, but send him to a completely different zip code, and then end up double-booking him. Very messy system, overall.

I'll keep playing with it myself, but this one has just been biting me in the butt left and right.

 

[oldbrewer]

There would be merit in scrapping this system and setting up a more logical workable system - Read up on systems thinking maybe.....

 

[Searshc]

There would be merit in scrapping this system and setting up a more logical workable system - Read up on systems thinking maybe.....

Several of us have a bounty out on the programmers of this thing.

 

[NoSparks]

Searshc, do you have a solution for this problem yet? If so what did you come up with? If not do you mind a few more questions towards another "kick at the can"?

 

[Searshc]

Sorry--had a few days of vacation to take. No solution yet. I'm thinking that maybe I need to create an array with the days (and the weeknum) and see if I can create something around that. If anyone knows a way offhand to have excel count a repeating sequence, great. But it looks like I can't just do this with a straight formula.

 

[oldbrewer]

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?