Vlookup referencing time of day

DarrenK

New Member
Joined
Aug 5, 2017
Messages
24
I attempted to use a mod formula but was not successful. I've since deleted it. I'm looking to generate something to assist with schedule adherence.

On Sheet 1, Agent Joe Smith works 8:30a-5:00p. I would like cell B2 (next to his name on different sheet) to reflect his status depending on the time of day. For instance, if it's currently 10:05am, that falls into his first break time from 10:00-10:15. The B2 status should say "On Break" and of course if it's any time outside of his 2 15-minute breaks or his 30-minute lunch, it should display "On Calls"

The times for each agent's breaks will differ. So the actual lookup for each period will need to be based on spreadsheet 2 which will display data as:

Sheet2ABCDEFG
3Agent NameShift StartShift EndBreak 1Lunch StartLunch EndBreak 2
4Joe Smith8:305:0010:0012:1512:453:15

Any ideas?

Thank you in advance
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,311
Office Version
  1. 365
Platform
  1. Windows
I think this might help. Someone else can probably do better. I needed helper columns next to the original data so that there could be sorted reference table of times. I don't think this will work if the start time is before midnight.

MrExcel posts18.xlsx
ABCDEFGHIJKLMNOPQ
3Agent NameShift StartShift EndBreak 1Lunch StartLunch EndBreak 2break2on calloffon callbreak1on calllunchon call
4Joe Smith8:30:00 AM5:00:00 AM10:00:00 AM12:15:00 PM12:45:00 PM3:15:00 AM3:15:00 AM3:30:00 AM5:00:00 AM8:30:00 AM10:00:00 AM10:15:00 AM12:15:00 PM12:45:00 PM
5John Snell6:00:00 AM3:30:00 AM8:30:00 AM10:45:00 AM11:15:00 AM1:45:00 AM1:45:00 AM2:00:00 AM3:30:00 AM6:00:00 AM8:30:00 AM8:45:00 AM10:45:00 AM11:15:00 AM
6
7
8current time2:56:44 PM
9
10Agent NameCondition
11John Snellon call
Sheet50
Cell Formulas
RangeFormula
J4:J5J4=G4
K4:K5,O4:O5K4=J4+15/60/24
L4:L5L4=C4
M4:M5,P4:Q5M4=B4
N4:N5N4=D4
B8B8=MOD(NOW(),1)
B11B11=INDEX(J3:Q3,MATCH(B8,INDEX(J4:Q5,MATCH(A11,A4:A5,0),0)))
 
Solution

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
I attempted to use a mod formula but was not successful. I've since deleted it. I'm looking to generate something to assist with schedule adherence.

On Sheet 1, Agent Joe Smith works 8:30a-5:00p. I would like cell B2 (next to his name on different sheet) to reflect his status depending on the time of day. For instance, if it's currently 10:05am, that falls into his first break time from 10:00-10:15. The B2 status should say "On Break" and of course if it's any time outside of his 2 15-minute breaks or his 30-minute lunch, it should display "On Calls"

The times for each agent's breaks will differ. So the actual lookup for each period will need to be based on spreadsheet 2 which will display data as:

Sheet2ABCDEFG
3Agent NameShift StartShift EndBreak 1Lunch StartLunch EndBreak 2
4Joe Smith8:305:0010:0012:1512:453:15

Any ideas?

Thank you in advance
Edit: Ok, I saw someone has replied with probably a working solution whilst I was typing! So just ignore me.

Hello, I couldn't figure out a solution which is probably now what you wanted to hear but I got halfway there. Maybe it will help jog something for you or give you ideas?

=IFERROR(IF(VLOOKUP(U7,$X$2:$Y$17,2,0),IF(AND(T7>=MIN(AA3:AB3),T7<=MAX(AA3:AB3)),"On break","On calls")),"Invalid name")

This formula allowed me to do this to an extent but the issue is the IF(AND TIME RANGE segment of the formula. Since it can only reference one row at a time, it is not dynamic. This is the part I can't figure out. It should just look for the name in your table and then find the corresponding ranges of time.
By the way, I added two extra columns called Break 1 End and Break 2 End.

I hope this gives you or someone else an idea.
 

DarrenK

New Member
Joined
Aug 5, 2017
Messages
24
I think this might help. Someone else can probably do better. I needed helper columns next to the original data so that there could be sorted reference table of times. I don't think this will work if the start time is before midnight.

MrExcel posts18.xlsx
ABCDEFGHIJKLMNOPQ
3Agent NameShift StartShift EndBreak 1Lunch StartLunch EndBreak 2break2on calloffon callbreak1on calllunchon call
4Joe Smith8:30:00 AM5:00:00 AM10:00:00 AM12:15:00 PM12:45:00 PM3:15:00 AM3:15:00 AM3:30:00 AM5:00:00 AM8:30:00 AM10:00:00 AM10:15:00 AM12:15:00 PM12:45:00 PM
5John Snell6:00:00 AM3:30:00 AM8:30:00 AM10:45:00 AM11:15:00 AM1:45:00 AM1:45:00 AM2:00:00 AM3:30:00 AM6:00:00 AM8:30:00 AM8:45:00 AM10:45:00 AM11:15:00 AM
6
7
8current time2:56:44 PM
9
10Agent NameCondition
11John Snellon call
Sheet50
Cell Formulas
RangeFormula
J4:J5J4=G4
K4:K5,O4:O5K4=J4+15/60/24
L4:L5L4=C4
M4:M5,P4:Q5M4=B4
N4:N5N4=D4
B8B8=MOD(NOW(),1)
B11B11=INDEX(J3:Q3,MATCH(B8,INDEX(J4:Q5,MATCH(A11,A4:A5,0),0)))
I have a feeling this might work. As of this moment, all shifts are over so my column of SHIFT END is displaying in every cell next to every agent. I will have to monitor it throughout the next few days and see if the status updates based on the current time. I will mark as a solution over the weekend. Thank you, DRSteele.
 

DarrenK

New Member
Joined
Aug 5, 2017
Messages
24
Edit: Ok, I saw someone has replied with probably a working solution whilst I was typing! So just ignore me.

Hello, I couldn't figure out a solution which is probably now what you wanted to hear but I got halfway there. Maybe it will help jog something for you or give you ideas?

=IFERROR(IF(VLOOKUP(U7,$X$2:$Y$17,2,0),IF(AND(T7>=MIN(AA3:AB3),T7<=MAX(AA3:AB3)),"On break","On calls")),"Invalid name")

This formula allowed me to do this to an extent but the issue is the IF(AND TIME RANGE segment of the formula. Since it can only reference one row at a time, it is not dynamic. This is the part I can't figure out. It should just look for the name in your table and then find the corresponding ranges of time.
By the way, I added two extra columns called Break 1 End and Break 2 End.

I hope this gives you or someone else an idea.
I appreciate the assistance, Lebene. I did mention using a vlookup thinking I needed it. But for now I think DRSteele's formula might be exactly what I need. Will have to test over the next few days. I will have to try your suggestion and see if I can get it working. Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,148
Members
410,775
Latest member
alal1030
Top