Pivot Table Help! Find events with response times longer than 6 minutes

I have a report that shows information on our call light system at a hospital.
The report shows:
Unit
Date
Room #
'Activity'
Time/Data

Unit/Date doesn't really matter because I only pull the report for the current day and have to pull individually for each unit.

For the Activities:
Normal is the actual time of day that the call light was pushed.
Total Time is the amount of time it took once the call light was pushed for the nurse to respond.

I need to investigate all call lights that were answered after 6 minutes or longer. What I need to know was what time of the day it was to determine if there are trends.

I know I can do this with a pivot table but after messing with it for way to long I am needing your help!! Please help!!

Attached is an example of a report.

Have you attempted to sort your data in any way?
The yellow rows appear to be subtotals that don't match up with the data above them.

It would be easier to help you if we could understand what we're looking at.

Originally Posted by jason.b75

Have you attempted to sort your data in any way?
The yellow rows appear to be subtotals that don't match up with the data above them.

It would be easier to help you if we could understand what we're looking at.

I should have unhighleted before uploading. The highlighted rows are the total time, it's those over 6minutes that I want to determine time of day.

Ok, so just to confirm, the yellow rows do match up to the data above them, even if the room number is not the same?

Also, you want all of the rows related to the yellow line showing, not just the 'Total Time'?

Oh, wow! No I must have uploaded a file that was filtered. I apologize.
Here is the original report so this has no filters and should make more sense. Thank you so much for looking at it!

Ok, I can't see any way to get that to a pivottable, possibly with power pivot, but I need to be sure that I'm following what you need first.

Try this formula in I5 of your latest sample file, then fill down to the last row of data and filter by column I to show TRUE results only.

We can understand your need better if you show your expected outcome with a bit of copy and paste

How strict are you on this data layout?

I would be wanting to see a unique identifier column for each call, otherwise you are letting the sorting of the data determine what information is related to each call, ie. the way the data currently sits.

You could then use Power Query and then pivot tables or formulas to get you what you want.

This data layout is just how the report is pulled from the platform, I am able to change it if it quick and easy. This is currently a report we have to provide daily so the less manipulating the better.

Also Jason.b75 I am unsure what formula you want me to try.

Sorry, that was a moment of monday morning madness! I forgot to add the formula to my reply

=OR(AND(E5="Total Time",TIMEVALUE(H5)>TIME(0,6,0)),AND(OR(ISNUMBER(SEARCH({"am","pm"},H5))),IFERROR(TIMEVALUE(INDEX(H6:H$956,MATCH("Total Time",E6:E$956,0)))>TIME(0,6,0),0)))

See if it gives you the expected results when you filter on TRUE.

You. Are. Amazing!

Yes, that works. So if I wanted to change it from 6 minutes to any other time (let's say 5 minutes), would I just have to replace the 6's with 5's?

I'm not sure that i have earned such a compliment after forgetting to post the formula

Yes, to change the time, that is all you need to do.

I wasn't actually expecting this to be the final solution, it was only meant as a test to see if I was looking at the right information to extract.

Ok I got too excited. I thought that the room numbers were lining up but that doesn't appear to be the case.

When I use that formula it shows all of the call lights that took longer than 6 minutes but the date the call light was pushed does not match (the room numbers/dates don't match either).

My end goal is for it to show both (the time of day the call light was pushed and the amount of time it took for them to answer (Those over 6 minutes)).

I'm not sure what you mean, as far as I can see the filtered data looks correct.

I've re-attached your file with the formula and filter already applied. This one uses a slightly different formula, which shows all of the rows for the calls over 6 minutes rather than just the initial call and the total time taken, aside from the intermediate steps, the results are the same for both methods.

Thank you, you are correct this gives me what I need! Appreciate it!