a repeating formula based on multiple options

[Waz']

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.

 

[Chip Pearson]

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, LLC
www.cpearson.com

 

[Waz']

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




- 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

 

[Chip Pearson]

Use a formula like the following and copy to the right for as many
dates as you have in row 1 and then copy that down for as many names
that you have in column A.

=IF($B2="10-4",IF(D$1-$C2<=10,"on","off"),IF(D$1-$C2<=21,"on","off"))

Here, $B2 is the first cell for the schedule type, either "10-4" or
"21-7". $D$1 is the first date in row 1. $C2 is the start date for a
user. If you keep the $ character in the formula as shown, you can
copy enter the formula in D2 and the copy to the right and then down
and the cells will properly adjust their references.

E.g.,
A B C D
1 "Name" "Type" "StartDate" dates......
2 Joe 10-4 1/1/2010 formula....
3 Jane 21-7 1/15/2010 formula...


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

 

[J_Knowles]

Using Chip Pearson's approach, I made this formula change for making a year
long schedule.

also cell C2, C3, C4 ... needs to be date format (2/7/10, 2/15/10 ....) -
Waz, your last example you had 7 and 15 for starting dates.

place this formula starting at cell D2 and copy - paste as far as needed to
the right and down.

This is all one formula:

=IF($B2="10-4",IF(MOD(D$1-$C2,14)+1<=10,"on","off"),IF(MOD(D$1-$C2,28)+1<=21,"on","off"))

HTH,