Rota summary of time gained with summary of time used - pivot two columns from single row

[godisaclog]

Hi there folks,

I have another conundrum. I'm sure this one is also easy - but it must be because it's Friday afternoon I can't figure it out.

I have a rota in Excel where my rows are days. One of my agents performs on-call each day and "gains" time. So this is a two column table, with one column the agent initials, and the next column the accumulated time.

Next to this I have another two columns for the loss of accumulated time. In theory, multiple agents could "use" time they have accumulated on a single day, but to keep it simple lets assume that just one agent can.

So now I have four columns:

Agent | Time gained this day | Agent | Time used this day
AB | 2.5 | CD | 1
AB | 2.5 | |
AB | 2.5 | |
AB | 1.5 | EF | 2.5
CD | 2.5 | AB | 1.5
etc.

What I'm trying to do is basically provide a summary of what each's agents accumulated time is. Total time accumulated is a simple pivot table, as is total time gained. But what I can't seem to do is combine the agent columns into a single column if you like so that the pivot table basically sums one column and subtracts the equivalent used time from the other.

So what I would hope as an output would be:

Agent | Total Time accumulated over period | Total time used over period | Currently available time
AB | 9 | 1.5 | 7.5
CD | 2.5 | 1 | 1.5
EF | 0 | 2.5 | -2.5


Any ideas anyone?

Thanks!

Clogs.

[etaf]

can you restructure you data
instead of
Agent | Time gained this day | Agent | Time used this day
AB | 2.5 | CD | 1
AB | 2.5 | |
AB | 2.5 | |
AB | 1.5 | EF | 2.5
CD | 2.5 | AB | 1.5

where you have different agents on the same row - can you do

Agent | Time gained this day | Time used this day
AB | 2.5 |
AB | 2.5 |
AB | 2.5 |
AB | 1.5 |
CD | 2.5 |
CD| ---- |1
EF| ---- |2.5
AB| ----|1.5

then in a pivot table - that should now work as required
and use a calculated field for the "Time used this day"

if you had a column for date then you can group by days


I have a sheet called original that has the data on as requested
and on sheet "with day" i have added a date column and used a pivot table to show the figures each day

Date | Agent | Time gained this day | Time used this day
1/1/13 |AB | 2.5 |
1/1/13 |AB | 2.5 |
1/1/13 |AB | 2.5 |
1/1/13 |CD| ----|1
1/1/13 |EF| ---- |2.5
1/1/13 |AB| ----|1.5
2/1/13 |CD |4 |
2/1/13 |EF |3 |
2/1/13 |EF |---- |3.5