Jan
Hello Folks, at the moment I'm doing a rota, I would like you guys to scrutinize the formula which I’m using and potentially give me a smaller formula or more precise formula for my needs. I've got it as far as my brain can take it but I’m sure somebody out there could do better.
Let me explain the rota first off; the rota is based on 6 shifts and employees rota on a 4 on 4 off basis:
- Shift 1: 7am - 7pm
- Shift 2: 7pm - 7am
- Shift 3: 11pm - 5am
- Shift 4: Off
- Shift 5: Off
- Shift 6: Off
So, for example, employee A's shift pattern might look like:
26/01/10 Shift 1
27/01/10 Shift 1
28/01/10 Shift 1
29/01/10 Shift 1
30/01/10 Shift 4
31/01/10 Shift 4
01/02/10 Shift 4
02/02/10 Shift 4
03/02/10 Shift 2
04/02/10 Shift 2
05/02/10 Shift 2
06/02/10 Shift 2
07/02/10 Shift 3 … and so on …
So the shift for one person rotates each 24 days, within that rotation it rotates a further 6 times. Confused yet?
Now, what the formula does is take the date and gives a number between 1 and 6 (Shift 1-6). This is what I have, but if you need any more details just let me know.
A2 26/01/2010
A3 =INT( ROUND( ( ( $A2/24-INT( $A2/24) )*24 ),0) /4 )+1
Answer:
yes i am confused…i blew off the top section entirely, and just stuck with the part where you wanted to shorten the formula.
i tested it as:
- A2 to A99 has dates starting January 26th and increasing a day(autofilled)
- copy&pasted your formula in B2 and filled down (it was easier to see the formula working)
that whole ROUND( ( ( $A2/24-INT( $A2/24) )*24 ),0) can be replaced by the MOD() function…”returns the remainder”…the opposite of the INT()
=INT(MOD($A2,24)/4)+1
copy&paste that, it should do the same thing.
since your formula was in A3, it looks like you’re going to go across?
hope this helps