Prev: "Save file before closing"
Next: Hidding rows when sorting
From: GEM on 1 Jun 2010 12:19
I have a large DB, this is a small example,

Client Date Time
1 5/31/2010 9:32:43 AM
1 5/31/2010 9:34:21 AM
1 5/31/2010 10:51:39 AM
3 5/31/2010 11:10:13 PM
3 5/31/2010 03:13:10 PM
5 5/31/2010 05:42:01 PM
5 5/31/2010 06:45:43 PM
2 5/31/2010 06:47:11 PM
4 5/31/2010 08:50:31 PM

I would like to create a pivot table that will count how many times each
client placed an order between these time groups,

9:00 am - 4:00 pm
4:01 pm - 6:00 pm
6:01 pm - 11:59 pm

Each entry is one order placed....
From: Gary Brown on 1 Jun 2010 14:57
Assuming Column C contains the Time, use a helper column with the following
formula...

=IF(AND(C2>=0.375,C2<=0.666667),1,IF(AND(C2>0.666667,C2<=0.75),2,IF(C2>0.75,3,0)))

A result of 1 = 1st shift of 9am to 4 pm
A result of 2 = 2nd shift of 4:01 pm to 6 pm
A result of 3 = 3rd shift of 6:01 pm to midnight
A result of 0 = from 12:01 am to 9:59 am

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"GEM" wrote:

> I have a large DB, this is a small example,
>
> Client Date Time
> 1 5/31/2010 9:32:43 AM
> 1 5/31/2010 9:34:21 AM
> 1 5/31/2010 10:51:39 AM
> 3 5/31/2010 11:10:13 PM
> 3 5/31/2010 03:13:10 PM
> 5 5/31/2010 05:42:01 PM
> 5 5/31/2010 06:45:43 PM
> 2 5/31/2010 06:47:11 PM
> 4 5/31/2010 08:50:31 PM
>
> I would like to create a pivot table that will count how many times each
> client placed an order between these time groups,
>
> 9:00 am - 4:00 pm
> 4:01 pm - 6:00 pm
> 6:01 pm - 11:59 pm
>
> Each entry is one order placed....
From: Herbert Seidenberg on 1 Jun 2010 15:02
Excel 2007 PivotTable
Odd groups
http://c0718892.cdn.cloudfiles.rackspacecloud.com/06_01_10.xlsx
Pdf preview:
http://www.mediafire.com/file/m1ymz3nin0k/06_01_10.pdf

 | 
Pages: 1
Prev: "Save file before closing"
Next: Hidding rows when sorting