Vlookup referencing time of day

D

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
65
Office Version
  1. 365
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
 
Last edited by a moderator:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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)))
 
Upvote 0
Solution
DarrenK said:
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
Click to expand...
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.
 
Upvote 0
DRSteele said:
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)))
Click to expand...
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.
 
Upvote 0
Lebene said:
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.
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
INDEX MATCH Function Help
Replies
6
Views
227
jeffj13
J
U
  • Question
Find start shift excluding exceptions
Replies
9
Views
252
Peter_SSs
Peter_SSs
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
644
felixstraube
F
D
  • Question
Nested IF statements to analyze time data
Replies
6
Views
89
RobOrBob
R
P
  • Question
Calculate time between start and finish minus the lunch break
2
Replies
13
Views
713
paulalex93
P

Forum statistics

Threads
1,214,651
Messages
6,120,739
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top