Hey all.. While coming up with a menu rotation for some people I know I came up with this formula for a cycle rotation. Thought if anyone else might need it to set up some sort of daily cycle, figure I'd post it. I used the LOOKUP function to complete the rest of the task, but out of all the things, this formula was actually my biggest challenge in the spreadsheet.


=((TODAY()-<Numerical Date Value>)+1)-((ROUNDDOWN((TODAY()-(<Numerical Date Value>)-1)/<Cycle Value>,0))*<Cycle Value>)
Numerical Date Value = Actual Number Value of the Start Date. Easy way to figure this out is to put a date into a cell, then change the Cell format to number with no decimals.

Cycle Value = Total number of days in the Cycle. For a 21 Day Cycle put 21 in this value.


To explain the formula a little bit (if people would like to know the method behind the madness). It takes the value of today and subtracts the start date value. This gives you the total amount of days. It then subtracts the the base cycle date to give you the day of the cycle. The base cycle date is the day in that cycle where it would start off again on day 1 of the cycle (I hope I'm not being too confusing). So if you were on a 10 day cycle, every 11th day it would start on day 1 of the cycle again.

So if we were on day 67 of a 10 Day Cycle the simplified formula would look like this.

(67+1)-((ROUNDDOWN(67,0)/10)*10)

Simplify the first part of the equation.

(68)-((ROUNDDOWN(67,0)/10)*10)

Now we take away the ROUNDDOWN to give us what cycle we are on. The ROUNDDOWN ensures we are always at the base number (Day 1) of our cycle.

(68)-((6)*10)

Simplify further

(68)-(60)

Subtract the base and you have your Day

68-60 = 8

Remember. the +1 at the beginning of the formula. This serves two purposes. It gives you a day cycle of 1 at the very beginning. Also whenever the base cycle and the total days are even, it elimnates the 0 value you would get. Since the first day would have started at 0, the 8 will show up as a 7 at the end of the formula.