Employee Schedule with multiple options.

[Scott]

Some time ago I turned to this group for assistance with an employee
scheduling problem. When the options were 10 days on and 4 days off
OR, 10-on, 1-off, 10-on, 8-off... The following formulae worked
briliantly.

=IF($G4>0,IF($F4="Reg 10-4",IF(MOD(G$1-
$G4,14)+1<=10,"on","off"),LOOKUP(MOD(G$1-$G4,29)+1,
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28;"on","on","on","on","on","on","on","on","on","on","off","on","on","on","on","on","on","on","on","on","on","off","off","off","off","off","off","off"})),"")


I now have to contend with a new set of multiple schedule choices.
Given a fixed starting date, the calculation needs to generate a table
showing days worked and days off for the following scheduling types.

10-4
10-1-10-8
20-8
21-7
14-7


Not sure that a "formula" is still the best approach here, but thank
you in advance for any help all the same.

Scott

 

[Alan]

Some time ago I turned to this group for assistance with an employee
scheduling problem. When the options were 10 days on and 4 days off
OR, 10-on, 1-off, 10-on, 8-off... The following formulae worked
briliantly.

=IF($G4>0,IF($F4="Reg 10-4",IF(MOD(G$1-
$G4,14)+1<=10,"on","off"),LOOKUP(MOD(G$1-$G4,29)+1,
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28­;"on","on","on","on","on","on","on","on","on","on","off","on","on","on","on­","on","on","on","on","on","on","off","off","off","off","off","off","off"})­),"")

I now have to contend with a new set of multiple schedule choices.
Given a fixed starting date, the calculation needs to generate a table
showing days worked and days off for the following scheduling types.

10-4
10-1-10-8
20-8
21-7
14-7

Not sure that a "formula" is still the best approach here, but thank
you in advance for any help all the same.

Scott

Your question doesn't make complete sense because I don't know what
was in the cells defined in your example (G4,F4,G1). However, I would
suggest that a way forward would be to write a User Defined Function
to address all of the permutaions you require. That function can then
be called from within the cells of your spreadsheet.

 

[Scott]

Your question doesn't make complete sense because I don't know what
was in the cells defined in your example (G4,F4,G1). However, I would
suggest that a way forward would be to write a User Defined Function
to address all of the permutaions you require. That function can then
be called from within the cells of your spreadsheet.- Hide quoted text -

- Show quoted text -

Hello Allan,

Can you expand on your UDF idea/suggestion?

To better explain my question, there are esentially two cells
referenced in the formula with balance of the spreadsheet filled by
the formula to calculate the on and off days. The first is the
schedule type and the second is a reference to a date. When I enter
say, 08/27/10 into G4, that date defines the starting point for the
schedule. From there, I choose the type of schedule in F4. Currently
there are only two options and now that I need to expand the possible
schedule types, I am beginning to see that there is a point afterwards
"IF" statements begin to boggle the mind. Nevertheless, if that is the
best option then I need to figure out how to incorporate 7 possible
working schedules into a single formula. the only two inputs are the
date and schedule type.

The possible days when an employee could be working and then off is as
follows and where working days are shown followed by a dash and then
the number of days off...
10-4
10-1-10-8 (In this schedule, after 10 days of work, the employee takes
a one day break and returns for 10 more working days followed by 8
days off)
20-8
21-7
14-7
14-14
5-2


Thanks in advance
Scott