How JustAnswer Works:
• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
Ask Scott Your Own Question
Scott, MIT Graduate
Category: Homework
Satisfied Customers: 3040
Experience:  MIT Graduate (Math, Programming, Science, and Music)
3546829
Type Your Homework Question Here...
Scott is online now

# I am using an array formula and I need it to pull times

### Customer Question

I am using an array formula and I need it to pull times for each specific group of times based on an agent. When i do just the Min formula I get times, however I need to only have my formula look at times that match up to a specific individual ID here are the formulas I am using: =MIN(IF(LILO!\$H\$2:\$H\$6>Combo_Breaker!D7,LILO!\$H\$2:\$H\$6)) this one works pulling time. By addition to this formula looks like this =MIN(IF((LILO!\$A\$2:\$A\$7=C11*LILO!\$H\$2:\$H\$6>Combo_Breaker!D11,LILO!\$H\$2:\$H\$6)) does not work to just have the report only look at the times that match a given agent. Is there any way using this formula to have it just pull over the times from the LILO for the given agent.
Submitted: 11 months ago.
Category: Homework
Expert:  swmcdonnell replied 11 months ago.
Hi, my name is*****'m taking a look at the file you sent now.
Expert:  swmcdonnell replied 11 months ago.
Have you ever used a pivot table?
Expert:  swmcdonnell replied 11 months ago.
You're going to have to help me understand what's in the Excel file a little better.
Expert:  swmcdonnell replied 11 months ago.
Well, please chat me back when you get back online. I can definitely help you, I just need to understand a little better what exactly you're trying to do and what's on the spreadsheet.
Customer: replied 11 months ago.
I have used a pivot table before. This is an attendance report. When the macro runs it moves data from the schedule and lilo tabs to the combo breaker tab. I need the column for the lilo 1 in the. Combo breaker to.show only login times that match the agent in the lilo tab. For instance same dude is showing a login on the combo breaker of 3:12 when it hold be 3:14
Customer: replied 11 months ago.
The current formula is pulling the closest login times but I need it to pull times that match the agent name only
Expert:  swmcdonnell replied 11 months ago.
OK, I'm looking at it. I will have a few questions, hold on.
Expert:  swmcdonnell replied 11 months ago.
the schedule is scheduled time in/out, the LILO is actual time in/out and the Combo Breaker compares the two?
Customer: replied 11 months ago.
that is correct.
Expert:  swmcdonnell replied 11 months ago.
When yoiu match the LILO to the schedule, how do you know if you're matching the right entries?
Customer: replied 11 months ago.
That is my struggle. My formula is pulling over times, but I am unsure what to add to make sure it is only looking at the login times for the scheduled person
Customer: replied 11 months ago.
The second formula I hd in my initial question I thought would do this but doesnt
Expert:  swmcdonnell replied 11 months ago.
What's the ultimate objective? What information are you trying to get at?
Expert:  swmcdonnell replied 11 months ago.
For example, are you trying to see by each 30-minute segment how much time the person actually spent? And then just summarize that?
Expert:  swmcdonnell replied 11 months ago.
Or is it enough to look at a day at a time?
Customer: replied 11 months ago.
Yep.
Expert:  swmcdonnell replied 11 months ago.
OK, so let's look at jboggie. Scheduled 5am to Noon in 30-minute increments. He logged in a 3:12, almost 2 hours early, and then logged out at 6:00. How does that get recorded? Does the time he worked when not on the schedule show up separately?
Customer: replied 11 months ago.
The time not scheduled is disregarded. The 3:12am to 6am would be applied to the 30 minute blocks that fall within that range.
Expert:  swmcdonnell replied 11 months ago.
for each 30-minute block, is it enough to know how many minutes the person worked during that time period?
Customer: replied 11 months ago.
As long as the right login time is referenced
Expert:  swmcdonnell replied 11 months ago.
Have you ever considered putting the data into a database?
Customer: replied 11 months ago.
I hadn't. I was hoping just to tweak the formula in the combo breaker.
Expert:  swmcdonnell replied 11 months ago.
We could probably do that and get it to work this one time, but the next time you try to do it, you're going to have to change it around. You're hardcoding absolute cell references in your formulas, and they probably won't be accurate when the data changes.
Customer: replied 11 months ago.
Okay. I can look into getting a database set up
Expert:  swmcdonnell replied 11 months ago.
On the spreadsheet, first your data should be sorted by date and time, in ascending order. I think that's part of the issue.
Customer: replied 11 months ago.
Okay.
Expert:  swmcdonnell replied 11 months ago.
How many agents do you have?
Customer: replied 11 months ago.
About 300
Expert:  swmcdonnell replied 11 months ago.
Wow, OK, I was going to ask if it would be feasible to put each agent on its own tab, but I don't think it is.
Expert:  swmcdonnell replied 11 months ago.
It's possible to write some Excel VBA code to do it, but it would be better if it were in an Access database or something similar. The reason is that, in a database, it's easy to go person by person and to know you're only dealing with the information about that person. In Excel, it's harder to know where someone begins and ends. But it is possible by writing a VBA program.
Customer: replied 11 months ago.
Okay. I will look into it.
Expert:  swmcdonnell replied 11 months ago.
Even if it's in Access, you're still going to have to write a little code.
Expert:  swmcdonnell replied 11 months ago.
Sorry, I wish I had a simpler solution for you.
Expert:  swmcdonnell replied 11 months ago.
Ultimately, I think you want something like the attached. Let me know if I can help you any further.