How can I calculate time with a large pool of raw data?

Occasional Visitor

Good Evening,

 

I'm self taught when it comes to formulas in Excel so if this is a very simple answer I apologize, as I had to learn (and quickly)  because my boss doesn't know how to use Excel at all.

 

Situation: My company sends reports out which will have literally thousand of account numbers say in cell A. In cell B, they have time stamps indicating what time an employee accesses that account to do whatever internal coding is listed in cell C, on the date listed in cell D. (there is a Cell that had each employees operator ID in Cell E)

 

*Each employee has their own Excel tab but there is also a tab with everyone just mixed it*

 

My Issue: When I look at these reports Cell A can repeat multiple times depending on how much was done by one employee (indicated in cell C) on a particular date (Cell D). 

 

Ex: This is how one persons first 8 entries can look on this huge report

 

 ABCD
This row added in just for clarity and doesn't exist on the report(account number)(Time)(Process)(Date)
11237:00:00 AMAccessed Billing1/2/2021
24566:59:13 AMContract Issued4/6/2021
31237:03:02 AMPrepared Mailing1/2/2021
41237:05:00 AMFinancial Review1/2/2021
51237:13:24 AMSent out Mailing1/2/2021
64563:01:00 PMCorrespondence 3/1/2021
778912:00:00 PMOutbound Call3/1/2021
812312:05:12 PMFinancial Review Edit3/5/2021

 

What I need to do: I need to figure out how long each employee spent in each account each day.

 

What I have been doing (Unsuccessfully): I have been sorting by oldest date and then by time smallest to largest, and then trying to figure out a formula that I could enter in to Cell F that would tell me how long that employee spent in each account each day. 

 

These reports literally have thousands of entries since they run it each quarter and want to monitor everyone's time in each account for the employees KPI (key performance indicators) as well as security concerns. 

 

My work around has been asking the data person to run and send me everyone's report each day, then I manually figuring it out because I know I wont have the time to do it for all 40 employees.

 

I also started to try and learn about IF AN NOT formulas on my lunch break this evening and I tried something like =IF(A2,(AND(D2)),SUM(B2:B9)), which gave me a result of "True", which I think I know why it did, but again, I'm learning on my own on my breaks and lunches.

 

Request: Is there a formula I could use that when populated could calculate how long each employee spent in each account each day?

 

Additional Information: We use Windows and I think we have Excel 2013

1 Reply

@I_Want_To_Learn This  should be relatively easy with Power Query (PQ). Download and install the add-in as described in the link below if you really are on Excel 2013. In later versions, the functionality is incorporated in Excel. 

https://powerquery.microsoft.com/en-us/excel/ 

 

Once you have PQ, get familiar with this tool. It's amazingly powerful and not all that difficult to learn. You'll connect to the raw data, set correct data types (especially for dates and times) and append all the data from the individual sheets into one.

Then you can summarise (group, sort, filter etc.) the time records per employee, per day per account. You'll need to be clear, though, with the logic of calculating total duration of a task since the raw data contains only one time stamp. In other words, when does a task start and when does it end? For instance, what duration should be derived from row number 2 in your example? Perhaps you can upload a file with a more realistic example of the raw data you are dealing with. Replace any sensitive information though with some fake data, but leave the structure of the data as it is.