Forum Discussion
Need Formula Help
I have a table of phone activity for agents that I need to create charts from. I have another table for a chart to extract Log In and Log Out times for each day. I don't know what formula to use that will take into account the different dates, and also the fact that agents might log in multiple times a day. What is a formula I can use to pull the first sign in time of each day? I tried SUMPRODUCT but it added the times together, I just need to know the initial log in and log out time for each day. Can I use multiple criteria on VLOOKUP?
3 Replies
Attached is a sample of the report, if I attach the whole thing it will be too big. I am trying to pull each agent into their own table so I can track what time they log in and out, among other things. I am having a hard time getting a formula to work to pull the time Agent 1 logged in the first time in the morning, and logged out at night each day.
Below is a sample of a table for one agent I am trying to put together so I can build charts for their dashboard.
You can use GROUPBY to aggregate the data:
=GROUPBY(DemoTbl,,,,0,,(DemoTbl[S]="S/In")+(DemoTbl[S]="S/Out"))
Your question little bit unclear. When you say in/out time for an agent for a day then you need at least agent name, date and in/out time. There may multiple logging means first one is in and last one is out. Times are store as decimal numbers in Excel. So, we can use MIN(), MAX() functions to get those in/out times. See the sample file and let us know your feedback and also attach a sample file to make the question clear. Also show your desired output manually in sample sheet so that we can make that output by formula.
=DROP(GROUPBY(A2:B18,C2:C18,HSTACK(MIN,MAX),0,0),1)
