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

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

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

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

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