| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to pick the first day in and the last day out in a PivotTabel?
I have created a PivotTable with datas looking like the following, i could
only manually pick out the first date received and the last date out, and sum the no of networkdays, however it involves a lot of work since there are many companies, wonder if anyone has a better idea. Thank you very much! Company Date Received Date Out No. of Networkdays ANY 10/12/2005 12/12/2005 0 12/12/2005 13/12/2005 1 15/12/2005 16/12/2005 1 BEG 02/12/2005 05/12/2005 1 05/12/2005 07/12/2005 2 08/12/2005 09/12/2005 1 12/12/2005 13/12/2005 1 The final result should look like ANY 10/12/2005 16/12/2005 2 BEG 02/12/2005 13/12/2005 5 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to pick the first day in and the last day out in a PivotTabel?
I think you can get most of the way there by using your Pivot Table.
Try this: Right-click on your Pivot Table and select Wizard Dbl-Click on the Date Recieved field and select MIN Dbl-Click on the Date Out field and select MAX Dbl-Click on the No. of Networkdays field and select SUM Now for the tricky part.... Right-click and HOLD on the DATA heading Drag it to the right until it covers the Total heading...Then release the mouse button. That should summarize just the way you described. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Desmond" wrote: I have created a PivotTable with datas looking like the following, i could only manually pick out the first date received and the last date out, and sum the no of networkdays, however it involves a lot of work since there are many companies, wonder if anyone has a better idea. Thank you very much! Company Date Received Date Out No. of Networkdays ANY 10/12/2005 12/12/2005 0 12/12/2005 13/12/2005 1 15/12/2005 16/12/2005 1 BEG 02/12/2005 05/12/2005 1 05/12/2005 07/12/2005 2 08/12/2005 09/12/2005 1 12/12/2005 13/12/2005 1 The final result should look like ANY 10/12/2005 16/12/2005 2 BEG 02/12/2005 13/12/2005 5 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to pick the first day in and the last day out in a PivotTa
DARN! A mis-type!
The tricky part is a LEFT-CLICK and hold NOT a right-click and hold My apologies. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: I think you can get most of the way there by using your Pivot Table. Try this: Right-click on your Pivot Table and select Wizard Dbl-Click on the Date Recieved field and select MIN Dbl-Click on the Date Out field and select MAX Dbl-Click on the No. of Networkdays field and select SUM Now for the tricky part.... Right-click and HOLD on the DATA heading Drag it to the right until it covers the Total heading...Then release the mouse button. That should summarize just the way you described. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Desmond" wrote: I have created a PivotTable with datas looking like the following, i could only manually pick out the first date received and the last date out, and sum the no of networkdays, however it involves a lot of work since there are many companies, wonder if anyone has a better idea. Thank you very much! Company Date Received Date Out No. of Networkdays ANY 10/12/2005 12/12/2005 0 12/12/2005 13/12/2005 1 15/12/2005 16/12/2005 1 BEG 02/12/2005 05/12/2005 1 05/12/2005 07/12/2005 2 08/12/2005 09/12/2005 1 12/12/2005 13/12/2005 1 The final result should look like ANY 10/12/2005 16/12/2005 2 BEG 02/12/2005 13/12/2005 5 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to pick the first day in and the last day out in a PivotTa
Thanks for your message, I forgot to say that the No. of networkdays is a
Dataitem, and i have already selected SUM. With only one field in the Drop Data Items, i could not get Data and Total all at the same time, or i am wrong? "Ron Coderre" wrote: I think you can get most of the way there by using your Pivot Table. Try this: Right-click on your Pivot Table and select Wizard Dbl-Click on the Date Recieved field and select MIN Dbl-Click on the Date Out field and select MAX Dbl-Click on the No. of Networkdays field and select SUM Now for the tricky part.... Right-click and HOLD on the DATA heading Drag it to the right until it covers the Total heading...Then release the mouse button. That should summarize just the way you described. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Desmond" wrote: I have created a PivotTable with datas looking like the following, i could only manually pick out the first date received and the last date out, and sum the no of networkdays, however it involves a lot of work since there are many companies, wonder if anyone has a better idea. Thank you very much! Company Date Received Date Out No. of Networkdays ANY 10/12/2005 12/12/2005 0 12/12/2005 13/12/2005 1 15/12/2005 16/12/2005 1 BEG 02/12/2005 05/12/2005 1 05/12/2005 07/12/2005 2 08/12/2005 09/12/2005 1 12/12/2005 13/12/2005 1 The final result should look like ANY 10/12/2005 16/12/2005 2 BEG 02/12/2005 13/12/2005 5 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to pick the first day in and the last day out in a PivotTa
You are right, I only realised what you meant after i change the Date
Received and Date Out from Rowfield to Datafield. "Ron Coderre" wrote: DARN! A mis-type! The tricky part is a LEFT-CLICK and hold NOT a right-click and hold My apologies. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: I think you can get most of the way there by using your Pivot Table. Try this: Right-click on your Pivot Table and select Wizard Dbl-Click on the Date Recieved field and select MIN Dbl-Click on the Date Out field and select MAX Dbl-Click on the No. of Networkdays field and select SUM Now for the tricky part.... Right-click and HOLD on the DATA heading Drag it to the right until it covers the Total heading...Then release the mouse button. That should summarize just the way you described. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Desmond" wrote: I have created a PivotTable with datas looking like the following, i could only manually pick out the first date received and the last date out, and sum the no of networkdays, however it involves a lot of work since there are many companies, wonder if anyone has a better idea. Thank you very much! Company Date Received Date Out No. of Networkdays ANY 10/12/2005 12/12/2005 0 12/12/2005 13/12/2005 1 15/12/2005 16/12/2005 1 BEG 02/12/2005 05/12/2005 1 05/12/2005 07/12/2005 2 08/12/2005 09/12/2005 1 12/12/2005 13/12/2005 1 The final result should look like ANY 10/12/2005 16/12/2005 2 BEG 02/12/2005 13/12/2005 5 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PivotTabel Templates in Excel 2003 | Excel Discussion (Misc queries) | |||
| pick top 5 out of 8 numbers | Excel Discussion (Misc queries) | |||
| Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
| Is there a way to pick the cells to add using sum? | Excel Worksheet Functions | |||
| Pick a row | Excel Programming | |||
Linear Mode
