Calculating what shift should be running on a three week , three shift rotating pattern

[adyk]

Hello All,

I am having real trouble with this and am beginning to go round in circles.

I need to work out what shift should be on from a date time in excel

an example would be 05/02/2013 21:39 - (shift 3 is on)

We currently have three shifts on a three rotating pattern

Week one (current as of w/c 04/02/2013)
6:00 -14:00 Shift 2
14:00 - 22:00 Shift 3
22:00 - 06:00 Shift 1

The next week (Week 2)
6:00 -14:00 Shift 3
14:00 - 22:00 Shift 1
22:00 - 06:00 Shift 2

and the following week (week 3)
6:00 -14:00 Shift 1
14:00 - 22:00 Shift 2
22:00 - 06:00 Shift 3

these just repeat and follow the pattern into next year 2014.

Having this calculate automatically would be a real bonus but i think I've confused my self on this and having trouble with the logic.

any help or guidance in the right direction would be very much appreciated.

Regards

[daddylonglegs]

Hello adyk, welcome to Excel Forum

If you have a specific date/time in A2 you can use this formula to get the correct shift

=IF(A2="","","Shift "&LOOKUP(MOD(A2,1)*24,{0,6,14,22},CHOOSE(MATCH(MOD(A2-DATE(2013,2,4)-"6:00",21),{0,7,14}),{1,2,3,1},{2,3,1,2},{3,1,2,3})))

[adyk]

Wow, thanks for the quick reply.

It works very well, without taking to much of your time could you explain the logic and it's workings.

I had began to use the mod function but had really confused myself after about 8 hours

thanks again

[daddylonglegs]

OK, if you had the week 1 shift pattern every day you could use this formula

=IF(A2="","","Shift "&LOOKUP(MOD(A2,1)*24,{0,6,14,22},{1,2,3,1})

MOD(A2,1)*24 gives you the number of hours through the day, e.g. 14.25 would represent the time 14:15 and if you look up that number against {0,6,14,22} you get the previous value (14) and that matches to the corresponding shift in {1,2,3,1}, i.e. 3 to give "Shift 3"

....but because you have different shift patterns this part.....

=CHOOSE(MATCH(MOD(A2-DATE(2013,2,4)-"6:00",21),{0,7,14}),{1,2,3,1},{2,3,1,2},{3,1,2,3})

....supplies the correct one of the three possible patterns

This part......

A2-DATE(2013,2,4)-"6:00"

gives the amount of days (as a decimal) elapsed since the specified week 1 start date/time and then MOD with divisor 21 gets the number of days into the 3 week cycle, e.g. if A2 = 31/12/2013 15:00 then

A2-DATE(2013,2,4)-"6:00" = 330.375

=MOD(330.375,21) = 15.375

and we want to know which week that's in so MATCH gives us that

=MATCH(15.375,{0,7,14}) = 3

CHOOSE then uses that 3 to give the correct shift pattern for that date, i.e. the 3rd listed which is {3,1,2,3}

Note that you can use any start date/time for the 3 week cycle - in the future or the past - formula still works

If you want you can set up some of the data in a table to simplify the formula and make it easier to change if the setup changes in future......

[adyk]

Many Thanks,

I think i'd well have been another 8 hours to get anywhere near that.

Regards

adyk