two rows of dates use in pivot table

M

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
172
dilshod_k
D
M
  • Question
Pivot Table help - Can I show certain data depending on value in another field
Replies
2
Views
281
Monty85
M
P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
224
POLACOLOCO
P
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
VBA for Web Scraping help
Replies
0
Views
606
epactheactor
E

Forum statistics

Threads
1,215,519
Messages
6,125,294
Members
449,218
Latest member
Excel Master

Share this page

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
Back
Top