Pivot table or array formula
 

Hi All

I use Excel 2003.

I have a set of data in columns in a spreadsheet that reflects sets of phone
calls.

The columns are (User ID; Name; Date; Start time; End Time; Call Length;
Hold Length; Talk Time; and Wrap Time).

I currently have vba code that shows averages of the Call Length, talk time
and wrap time with date as a page item and name as a row item.

I now want to add something that shows these averages over parts of the day,
hour by hour during the opening hours. Is their a way to group certain start
times that fall between set times with just the pivot table functionality or
would I need to use some code or formulas to add an extra column to look at
the start times and give a time or name to indicate what part of the day that
call is in then use a pivot table.

Alternatively would I be best to forget pivot tables and set up a table
using array formula's to do what I want.

--
Regards and Thanks for any assistance.

Francis Brown.

Pivot table or array formula
 

In a pivot table you can group records together. So long as your times are
all actual times and not just text values then you can group on the time
field which should be what you want.

Add the time field and right click on it. Select Group by and you are good
to go.
--
HTH...

Jim Thomlinson


"Francis Brown" wrote:

Hi All

I use Excel 2003.

I have a set of data in columns in a spreadsheet that reflects sets of phone
calls.

The columns are (User ID; Name; Date; Start time; End Time; Call Length;
Hold Length; Talk Time; and Wrap Time).

I currently have vba code that shows averages of the Call Length, talk time
and wrap time with date as a page item and name as a row item.

I now want to add something that shows these averages over parts of the day,
hour by hour during the opening hours. Is their a way to group certain start
times that fall between set times with just the pivot table functionality or
would I need to use some code or formulas to add an extra column to look at
the start times and give a time or name to indicate what part of the day that
call is in then use a pivot table.

Alternatively would I be best to forget pivot tables and set up a table
using array formula's to do what I want.

--
Regards and Thanks for any assistance.

Francis Brown.

Pivot table or array formula
 

Perfect solution. have also now used macro recorder to point me in the
direction of the VBA code.

Thanks Jim.
--
Regards and Thanks for any assistance.

Francis Brown.


"Jim Thomlinson" wrote:

In a pivot table you can group records together. So long as your times are
all actual times and not just text values then you can group on the time
field which should be what you want.

Add the time field and right click on it. Select Group by and you are good
to go.
--
HTH...

Jim Thomlinson


"Francis Brown" wrote:

Hi All

I use Excel 2003.

I have a set of data in columns in a spreadsheet that reflects sets of phone
calls.

The columns are (User ID; Name; Date; Start time; End Time; Call Length;
Hold Length; Talk Time; and Wrap Time).

I currently have vba code that shows averages of the Call Length, talk time
and wrap time with date as a page item and name as a row item.

I now want to add something that shows these averages over parts of the day,
hour by hour during the opening hours. Is their a way to group certain start
times that fall between set times with just the pivot table functionality or
would I need to use some code or formulas to add an extra column to look at
the start times and give a time or name to indicate what part of the day that
call is in then use a pivot table.

Alternatively would I be best to forget pivot tables and set up a table
using array formula's to do what I want.

--
Regards and Thanks for any assistance.

Francis Brown.