weekly rotation, too simple

[scott]

I'm embarassed to ask, but.....I need to have two names
rotate every 7 days. I am expected to populate a schedule
every week of the name of an on-call employee. There are
only two on-call employees, and they alternate every other
week. Can I make the spreadsheet alternate names
automaticlly every 7 days?

thanks,

scott

 

[Alan]

=IF(ISODD(WEEKNUM(TODAY())),"Smith","Jones")
will return Smith if the week number is odd, Jones if its even. This changes
on a Sunday, ie the week is Sunday to Saturday.If you want it to change on a
different day eg Saturday then
=IF(ISODD(WEEKNUM(TODAY()+1)),"Smith","Jones")
will do that so you can make it changeover on the day you want by adjusting
TODAY()+1 to suit, To experiment, enter a list of consecutive dates in say
A1:A25 and in B1:B25 enter
=IF(ISODD(WEEKNUM(A1)),"Smith","Jones") and copy down.
Regards,

 

[Myrna Larson]

I don't know what your calendar looks like, but if it's just the Saturday
dates in column A and the name in B, the simplest way is to do this:

Type the assignment for the 1st weekend literally in B2, i.e. you type Smith
or Jones. In B3, type this formula and copy it down the rest of the weeks:

=IF(B2="Smith","Jones","Smith")

or

=IF(MOD(ROW(),2)=0,"Smith","Jones")

Change the 0 to a 1 if the result is the reverse of what it should be.

As for using a formula that involves WEEKNUM, this will sometimes fail at the
end of the year. The reason is that a year is 365 or 366 days, which is 52
weeks plus 1 or 2 days, so every year has a week 53. Assuming you use the
Saturday date (instead of TODAY()), whenever the last Saturday of the year
falls in week 53, you'll have 2 odd-numbered weeks in a row. That happened in
the year 2000, and will happen again in 2005 and 2011.

There's another reason not to use WEEKNUM: it's buggy! For the year 2000,
dates 12/24 through 12/30 show as week 53, and 12/31/2000 is week 54! I
haven't played around with it to find the next year with a week 54.

To make this independent of the year, I would calculate it using
MOD(SomeSaturdayDate,14). That will give you a number between 0 and 13. If the
result is between 0 and 6, it's the 1st week of the pair, if between 7 and 13,
it's the 2nd week of the pair.

Let's assume (a) you have the list of the Saturday dates for 2004 in column A;
(b) Smith worked the first weekend of 2004. The first Saturday was Jan 3,
2004. You could use this formula in column B. In fact you would not have to
modify it in any way when the dates in column A are for the year 2005.

=IF(MOD(A2,14)=MOD(DATE(2004,1,3),14),"Smith","Jones")

The requirement here is that the date in A2 is a Saturday.