two rows of dates use in pivot table

mish78

New Member
Joined
Apr 27, 2009
Messages
12
Hi,
I am stumped by this and cannot find any answer.

If I have a table as below. Can I combine the dates in a pivot chart??

<table style="border-collapse: collapse; width: 195pt;" border="0" cellpadding="0" cellspacing="0" width="260"><col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <col style="width: 66pt;" width="88"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">case</td> <td style="width: 81pt;" width="108">start</td> <td style="width: 66pt;" width="88">end </td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">aaaa</td> <td class="xl63" align="right">04/06/2010</td> <td class="xl63" align="right">11/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">bbb</td> <td class="xl63" align="right">11/06/2010</td> <td class="xl63" align="right">11/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">cccc</td> <td class="xl63" align="right">06/06/2010</td> <td class="xl63" align="right">11/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">dddd</td> <td class="xl63" align="right">07/06/2010</td> <td class="xl63" align="right">12/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">eee</td> <td class="xl63" align="right">08/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ffff</td> <td class="xl63" align="right">11/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ggg</td> <td class="xl63" align="right">10/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">hhh</td> <td class="xl63" align="right">11/06/2010</td> <td class="xl63" align="right">13/06/2010</td> </tr> </tbody></table>

so the pivot chart shows ( or normal chart ).....

<table style="border-collapse: collapse; width: 240pt;" border="0" cellpadding="0" cellspacing="0" width="321"><col style="width: 80pt;" span="3" width="107"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 80pt;" height="15" width="107">date</td> <td class="xl66" style="border-left: medium none; width: 80pt;" width="107">count of cases started</td> <td class="xl66" style="border-left: medium none; width: 80pt;" width="107">count of cases ended</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">04/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">05/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">06/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">07/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">08/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">09/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">10/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">11/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="height: 11.25pt; border-top: medium none;" align="right" height="15">13/06/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">4</td> </tr> </tbody></table>
I have figured out how to do it with lookup and count but would rather use a pivot as there may be alot of data.

Any advice apprecciated

mish
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

harshab6

Active Member
Joined
Oct 1, 2008
Messages
254
hi,
sorry unable to get U.what is that result that you want from the pivot table it would be helpful if you would place a example of the result that you would like to get from pivot table.are you having a problem with chart of table.

regards
harsha
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
I doubt you want to do this, but the only way I could get a Pivot Table to do what you want was to reorganize your data like this...
Excel Workbook
BCD
1startendAll Dates
204/06/201004/06/2010
311/06/201011/06/2010
406/06/201006/06/2010
507/06/201007/06/2010
608/06/201008/06/2010
711/06/201011/06/2010
810/06/201010/06/2010
911/06/201011/06/2010
1011/06/201011/06/2010
1111/06/201011/06/2010
1211/06/201011/06/2010
1312/06/201012/06/2010
1413/06/201013/06/2010
1513/06/201013/06/2010
1613/06/201013/06/2010
1713/06/201013/06/2010
...

Excel Workbook
FGH
2Data
3All DatesCount of startCount of end
404/06/20101
506/06/20101
607/06/20101
708/06/20101
810/06/20101
911/06/201033
1012/06/20101
1113/06/20104
Pivot Table result
 

mish78

New Member
Joined
Apr 27, 2009
Messages
12
AlphaFrog - me too. thats the closest I can get but you are correct I dont want that , I want to merge the dates. must be a way somegow....

harshab6 - i have data in the first table and i want to be able to show it in the format in the second table ( pivot table ).


The data is there so I must be able to do it somehow but it seems impossible
 

Watch MrExcel Video

Forum statistics

Threads
1,132,815
Messages
5,655,460
Members
418,203
Latest member
ElizabethCorrin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top