Excel PivotTable Date Grouping in 24-hr Center

Question

I am attempting to analyze data of agents working in my company that runs 24/7. I was going to construct a pivot table to show the daily operations of each individual. This fit most of my needs however, being a 24-hour business, some agents work overnight (ie. 8pm-4am), and this is causing the data to be inaccurate when viewing those members by day as it includes half of 2 shifts rather than their whole shift. The data I have has the time/day listed for each action the agent has taken over a period of time. Any assistance to properly group the dates would be very much appreciated.

date microsoft excel pivot table

Rajesh Sinha · Answer

I'm sure that you are looking for this only:

How it works:

Enter Start & End Date and Time using,  mm/dd/yy h:mm AM/PM cell format.

You may alter Date & Time format according to practice in your zone.

Formula in cell U2 & applied format is HH:MM.
=T2-R2

The formula in cell V2 determines the Working Shift, helps to categories data accordingly while creating Pivot Table.
=LOOKUP(HOUR(R2),{0,12,16},{"Morning","Afternoon","Night"})&" Shift"

N.B.

While creating Pivot Table, put Agents in ROWS, Shift Details in COLUMNS and Duration in VALUE (use COUNT).

I've skipped the column Work Status/Key, if you feel you may include
it also.

Excel PivotTable Date Grouping in 24-hr Center

One Answer

Add your own answers!

Ask a Question