On Feb 21, 10:40*am, Chip Pearson wrote:
If A1 is "21-7" (or "10-4") and B1 is <= 21 (or 10), then the formula
below will return "On", otherwise it will return "Off".

=IF(A1="21-7",IF(B1<=21,"on","off"),IF(B1<=10,"on","off"))

Before entering the values "21-7" or "10-4" into cells, format those
cells as Text. Otherwise, Excel will attempt to treat them as dates.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
* * * * Excel, 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com

On Sun, 21 Feb 2010 09:19:38 -0800 (PST), "Waz'"
wrote:



I am struggling to come up with an elegant formula solution to
accomplish the following task...

I have a schedule with people working 21-7 (21 days on and 7 days off)
and 10-4 (10 days on and 4 days off)schedules. By defining either of
these schedule options in one column and the day number of their
rotation (say, the schedule is starting on day number 3 of a 10 day
rotation) I want to create a formula to complete the rows of a
schedule to show "working" or "off" based on just these two
variables.- Hide quoted text -

- Show quoted text -




Thanks for the help, but I still can't get the formula to roll forward
based on the type of schedule.

Here's what I am looking at...

A B C D
E F G
H I J K
name schedule type starting day 02/21/10 02/22/10
02/23/10 02/24/10 02/25/10 02/26/10 02/27/10
02/28/10

Herb 10-4 7 on
on on off
off off off on
Gary 21-7 15 on
on on on on
on off off



Due to several people starting and finishing their rotations at
unpredictable intervals, I am trying to get a snapshot of what the
overall schedule looks like by knowing hte type of schedule and the
point that they are at in their rotation. I am hoping for a formula-
based solution so that I only have to enter the values in columns A.B
and C and the status - "on" or "off" can be claculated.


Any help would be greatly appreciated.
Warm Regards
Scott