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!

 

[oldbrewer]

Jan 1st start 8 week rotation
Feb 15th which week of the 8 weeks am I in

Is this what you want?

 

[Searshc]

Yes, please! But it has to be able to sometimes span years, some of these things have not been fixed in a long time.

 

[Searshc]

I got to thinking last night, is there any way to have excel just count up to a certain number, and then start over? I can then just use a vlookup against the dates and get the right week I'm looking for.

 

[oldbrewer]

you can put today's date in A1 and in B1 put today() and in C1 put B1-A1
this will give you number of days since you entered the date in A1

Not sure why you need to span years - please explain.

 

[Searshc]

The system we use runs on a weekly rotation. I have the date the rotation was started, and we have to enter a date that we want to start a new rotation. However, some of these rotations were started in 2008/2009, and the number of weeks in rotation varies. I want to be able to set a start date, and an end date, with the number of weeks in the rotation. I want Excel to tell me which week of rotation we will be in for that second date.

 

[oldbrewer]

If you have a start date - say Jan 8 2008 and the rotation is 9 weeks, = 63 days, take today's date and subtract from it Jan 8 2008, format as an integer number of days, divide by 63, integer that answer, multiply by 63, add that number of days to Jan 8 and you have the date the current cycle started.

Is that exactly what you want?

 

[NoSparks]

assuming cycle start date is in A2 and date in question in colB and weeks in cycle in C2, would this work?

=IF(MOD(INT((B2-$A$2)/7),$C$2)=0,1,MOD(INT((B2-$A$2)/7),$C$2)+1)

 

[Searshc]

assuming cycle start date is in A2 and date in question in colB and weeks in cycle in C2, would this work?

=IF(MOD(INT((B2-$A$2)/7),$C$2)=0,1,MOD(INT((B2-$A$2)/7),$C$2)+1)

Unfortunately, no. It has to be broken down per week, not per day. So if this had dates of 5/10 through 5/15 on a 2-week rotation, it would say that it's week 1 because it's under 7 days. Thank you for the offer, however.

 

[NoSparks]

Seeing you haven't posted a solution, are you still looking?

If so can you clarify what your sync date is?

The formula given assumes the sync date in A2 to be a known start of the first week of a rotation.

if this had dates of 5/10 through 5/15 on a 2-week rotation, it would say that it's week 1 because it's under 7 days.

What were you using for your sync date?