Nested IF on time range for staff roster - What am I doing wrong??

[Legacy 323044]

Hi all excel experts,

I'm currently working on a roster template for my team. I have got the a structure that I want but IF, AND formulas don't seem to work properly.

What I want to achieve are;

1. The roster results updated according to the time entered for each activity (meeting, breaks, lunch)
2. able to tell how many staff are available/ on break at each 15mins time slot

The formulas are calculated from;

• Operating hours 7:00am - 7:00pm
• Shift hours (vary from staff to staff)
• Start Up (SU) - 30 mins meeting in the morning
• Morning tea (T1) - 15 mins tea break
• Lunch (L) 1 hour lunch break
• Afternoon tea (T2)
• Green Peace (GP) - time for staff to do admin tasks

Here's the formula I use;

=IF(AND(P$3>=$F4,P$2<=$G4),"SU",
IF(AND(P$3>=$H4,P$2<=$I4),"T1",
IF(AND(P$3>=$J4,P$2<=$K4),"L",
IF(AND(P$3>=$L4,P$2<=$M4),"T2",
IF(AND(P$3>=$N4,P$2<=$O4),"GP","A")))))

Row 3 & 2 are 15 mins time slot (start/ finish) eg. 7:00 & 7:14, 7:15 & 7:29 and so on...

The problems I'm facing;

1. The abbreviation above don't response correctly with the time for each activity
2. I also would like to add Unavailable (U) for the time slot outside staff working hours (vary from staff to staff, based on their shift hours)

I have been working on this roster for days now and i'm getting very frustrated. Should I keep on using the structure/ formulas that I have or should I look at something else? INDEX, MATCH?

It is a roster for call centre environment so it's very important for us to know how many staff are available to take calls, especially during peak hours.

Any help is appreciated.

 

[QU4487]

Hi,

I'd like to help, but I'm really struggling to picture how your data looks. Any chance of uploading an example?

You'll have more chance of a response this way.

Q