Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Employee Schedule with multiple options.
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($G40,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,2 0,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Employee Schedule with multiple options.
On 21 Aug, 05:32, Scott wrote:
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($G40,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,2 0,21,22,23,24,25,26,27,28*;"on","on","on","on","on ","on","on","on","on","on","off","on","on","on","o n*","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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Employee Schedule with multiple options.
On Aug 27, 3:34*am, Alan wrote:
On 21 Aug, 05:32, Scott wrote: 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($G40,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,2 0,21,22,23,24,25,26,27,28**;"on","on","on","on","o n","on","on","on","on","on","off","on","on","on"," o*n*","on","on","on","on","on","on","off","off","o ff","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.- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
employee schedule with hours | Excel Worksheet Functions | |||
Employee schedule | Excel Discussion (Misc queries) | |||
employee schedule | Excel Discussion (Misc queries) | |||
Employee Schedule | Excel Programming | |||
Employee Tracking Schedule | Excel Discussion (Misc queries) |