Automate a report showing how many times in intervals

[Praveen1992]

Hi everyone!

Just signed up and I think I'd be here for a long time now as I recently started a new position at my company in which I have to work with Excel more than I thought. I bought an online class at udemy and I'm still working on it.
I need your help with this small report I'm working on.

Goal: Automate the report showing how many times an agent extended breaks and lunches in intervals, 1-5 mins, 5-10 mins, over 10 mins.

What I did so far: We get daily reports in CSV format which isn't very friendly and I make few changes to it before I dump into my raw data sheet. I made a table that pulls the names and the late intervals using simple If, And & OR functions.

Challenge: Pivot doesn't work as I'm getting multiple entries in the Names column. I need the report to show how many instances of extended breaks Agents in the intervals mentioned above.

I attached the file with this thread that I worked on so far which can help you understand the issue better.

Thanks in advance. I really appreciate your help.
Praveen.

[kersplash]

You might be better getting your source data into the best format for the pivot table as your first step.

Power Query is very good for this sort of thing if you attach a sample of your raw data file.

[MarvinP]

Hi Praveen,

You can't group by duration because those things are text and not numbers. I've converted them to minutes and done a Pivot Table group with a reason filter. See the attached.

Extended Breaks group by minutes.xlsx

To get those durations to values I copied a zero cell and then Pasted Special over all those durations using ADD. That converts them to numbers. I then multiplied by 60 to get minutes (I think).
Pivot table will group numbers but not text.