A text based output based on random Times & a shift pattern. Is it possible?

[elsopix]

Hello Mr Excel! This is my first post so be gentle with me. I am trying to put together a project for work but google is just not cutting the mustard when it comes to providing answers!

So here is the scenario,
We have a 24hr business which operates a 6 days on 4 days off shift pattern. so 5 teams, A-E work, at any one time, one team works Earlies (0700 - 1500), one works lates (1500 - 2300) and the last team work nights (2300 - 0700).

we currently use excel to perform calcs by copy and pasting in from our in house programmes. This gives us performance data. What it does not give at a glance is which team was on duty at the time. This would be useful as it would allow that team supervisor to concentrate on issues pertaining to him or her.

On my sheet I the time appears as 01/01/15 07:16

So i have tried doing a nested IF function however this is inelegant. As it does not tell the team, just if it was the early , late or night team - this should be obvious from the time.

So what I need to do is have the sheet calculate which team would have been on and simply output "Team A" in a seperate cell.

If you could give me any pointers that would be great.

For further points though - the shift pattern varies on Fridays & saturdays. So earlies work till 1700 and lates start then.

is there a way to do this or do i need a reference sheet with the shifts on?

Hoping you can help.

All the best
Elsopix

 

[barry houdini]

Hello Elsopix, welcome to MrExcel

I assume that there is a repeating pattern which repeats exactly every 10 days, except for the complication with Friday/Saturday shifts, so try setting up a table to show the shift starts for a past 10 day period (further back than any of the dates you want to query), e.g. create a table in P2:Q31 where column P shows successive shift start times/dates (starting with an early shift) and column P shows the team, so P2 might be 1/12/2014 07:00 and Q2 "Team A" and then P3 will be the next shift start time, 1/12/2014 15:00 and Q3 "Team B" etc.

For this table just ignore the Friday/Saturday changes, show all late shifts starting at 15:00

Now if you have a specific date/time in A2 this formula in B2 will give the relevant team name

=LOOKUP(MOD(ROUND(A2-P$2,5),10)+P$2,P$2:Q$31)

.....although that doesn't take account of the Friday/Saturday discrepancy, it will be wrong for Friday and Saturday times between 15:00 and 17:00 only, so adjusting for that will give you this version of that formula

=LOOKUP(MOD(ROUND(A2-P$2,5),10)+P$2-IF(AND(WEEKDAY(A2)>5,HOUR(A2)>14,HOUR(A2)<17),1/8),P$2:Q$31)

Edit:

It occurs to me that if your shifts are all the same length (except for Friday and Saturday) then you only really need to have a single start time/date and a shift order for the 5 shifts, so assuming P2 shows a past early shift start time/date for the first team (TEAM A) then you can use this formula without any table:

=CHOOSE(INT(ROUND(MOD(ROUND(A2-P$2,9)-IF(AND(WEEKDAY(A2)>5,HOUR(A2)>14,HOUR(A2)<17),1/8),5/3)*3,9))+1,"TEAM A","TEAM B","TEAM C","TEAM D","TEAM E")

Just list the teams in the order they occur

 

[elsopix]

Thanks BarryHoudini for that rapid reply. I will try that this afternoon and let you know how I get on.
Cheers
elsopix

 

[elsopix]

Thanks Barry,
The second formula appears to work well and returns values nicely. However I have noticed that I have misled you on my shift pattern. My pattern is on a 20 week pattern and so Teams do not work a,b,c,d,e on a constant rotation. There are two teams off at any one time.
so the pattern goes: eca, eca, bec, bec, dbe, dbe, adb, adb, etc.
However i have used the second formulae and quickly made a look up table and it seems to be working perfectly.
I will make sure I give it a thorough testing before i put it into full use, but it looks great to me!

Thank you so much for your assistance with this. Much appreciated!

I have to ask - how do you get to your level on excel? is it programming or maths background or trial and error?