data summary: week,month,year (microsoft xp)

Topic

New Reply

hi. i want to make data summary from a column (Net P/L) which expands from B1:B4481.
A1:A4481 is Date. the date is daily date and would like to get summary data of Net P/L in week,month and year. My date is English(US) format (month/day/year) and use Microsoft Office XP.
i tried to group the date into 7 days interval in pivot table but it says the data cannot be grouped. what can i do? do you have suggestion to make the week summary data? attached is the file that has Date and Net P/L. Thanks.

Reply | Quote

Replies

Welcome to Woody’s Lounge.

It might be because I am using Excel on a system with a different language, but most dates in column A are seen as text on my system. Only A10 and the cells for May 2004 and later are seen as dates. Since the data are a mixture of text and data, it is not possible to group on them.

Reply | Quote

Welcome to Woody’s Lounge.

It might be because I am using Excel on a system with a different language, but most dates in column A are seen as text on my system. Only A10 and the cells for May 2004 and later are seen as dates. Since the data are a mixture of text and data, it is not possible to group on them.

Reply | Quote

Most of your “dates” are “text” and not “dates”. Excel groups numbers, it can not group text.

Select column A, choose data – text to columns, delimited and it will convert them all to numbers (dates) and the grouping should work.

Whenever you have no justification in a column, text is left justified and numbers are left justified. This is a good “giveaway” that your “numbers” are incorrectly formatted as text.

Steve

Reply | Quote

hi Steve, this is excatly where make me start using pivot table; “Select column A, choose data – text to columns, delimited “. THANK YOU VERY MUCH.

Reply | Quote

hi Steve, this is excatly where make me start using pivot table; “Select column A, choose data – text to columns, delimited “. THANK YOU VERY MUCH.

Reply | Quote

hi Steve, two more pivot table questions;
1. can the year format in 7 days interval days grouping be 85 instead 1985 and how to do that? e.g. 11/1/85-11/7/85 instead 11/1/1985-11/7/1985
2. can data range modified after a pivot table created and how to do that? e.g. original data range is A1:C10 and a pivot table created. when data range for the pivot table change to A1:C20, the pivot table does not counts C11:C20. if data range cannot be modified in the pivot table, a new pivot table has to be created with data range A1:C20.

THANKS.

Reply | Quote

1) Since your values are “dates” just change the format (format – cells – number – custom mm/dd/yy) instead of mm/dd/yyyy

2) right click the pivot table, wizard, , set the new range,

You can also use “dynamic range names” and have the pivot table based on a dynamic range (using OFFSET) so that it adjust automatically. See Chip Pearson for more info on dynamic ranges.

Steve

Reply | Quote

1) Since your values are “dates” just change the format (format – cells – number – custom mm/dd/yy) instead of mm/dd/yyyy

2) right click the pivot table, wizard, , set the new range,

You can also use “dynamic range names” and have the pivot table based on a dynamic range (using OFFSET) so that it adjust automatically. See Chip Pearson for more info on dynamic ranges.

Steve

Reply | Quote

hi Steve,
1. the pivot table is in F5 and i selected whole column F, Format – cells – number – custom m/d/yy:@ and the 7 days interval group dates remains m/d/yyyy and the dates resides in left as a text would be. if necessary, i will attach a new file as original A1:B4481 of Dates and Net P/L with 7 days interval group pivot table memory exceeds file attachement memory here.
2. Wizard is my first start to set new range in pivot table.
THANK YOU.

Reply | Quote

hi Steve,
1. the pivot table is in F5 and i selected whole column F, Format – cells – number – custom m/d/yy:@ and the 7 days interval group dates remains m/d/yyyy and the dates resides in left as a text would be. if necessary, i will attach a new file as original A1:B4481 of Dates and Net P/L with 7 days interval group pivot table memory exceeds file attachement memory here.
2. Wizard is my first start to set new range in pivot table.
THANK YOU.

Reply | Quote

I am unclear as to where you are having the problem.

I take your file, convert column A to “dates” as discussed earlier, (I can change the format as mentioned also).

When I create the Pivot table from this data, the format in the Pivot table matches the format in the original dataset, though I can change it directly in the pivot table to a different format.

Are you sure you converted the dates in the original data?

Steve

Reply | Quote

hi Steve, it is true daily dates in pivot table can be m/d/yy. somehow, i cannot do this with 7 days interval group data. maybe you want to look attached sample. Thanks for your attention.

Reply | Quote

That can be changed by changing your regional settings:
Start – settings – control panel – regional settings – date

though this will affect all the programs not just excel. The cells in the pivot are “text” not numbers so you must change how excel converts them and it uses the regional settings to key on.

Steve

Reply | Quote

Thank You Steve. i managed to do that.

Reply | Quote

Thank You Steve. i managed to do that.

Reply | Quote

That can be changed by changing your regional settings:
Start – settings – control panel – regional settings – date

though this will affect all the programs not just excel. The cells in the pivot are “text” not numbers so you must change how excel converts them and it uses the regional settings to key on.

Steve

Reply | Quote

hi Steve, it is true daily dates in pivot table can be m/d/yy. somehow, i cannot do this with 7 days interval group data. maybe you want to look attached sample. Thanks for your attention.

Reply | Quote

I am unclear as to where you are having the problem.

I take your file, convert column A to “dates” as discussed earlier, (I can change the format as mentioned also).

When I create the Pivot table from this data, the format in the Pivot table matches the format in the original dataset, though I can change it directly in the pivot table to a different format.

Are you sure you converted the dates in the original data?

Steve

Reply | Quote

hi all, hi Steve, hi anyone, i got the Excel list of Date, Net P/L and Total Turnround. as usual, the Date and Net P/L is done but total turnround left out. just Total Turnround sit besides Net P/L. can someone tell me which cell to place the Total Turnround item to get that? THANKS A LOT.

Reply | Quote

Right-click the pivot table
Wizard…
Drag [Total Turnaround] {note looks like [Total TU]}
underneath “sum of Net P/L”

left-Click and drag the [Data] slightly to the right (Over the “Total”), the status bar should say “Drop to place this field on the column axis” and then release the left button. You will now have 2 columns

Steve

Reply | Quote

wow, i get exactly the pivot table model. THANKS Steve.

Reply | Quote

wow, i get exactly the pivot table model. THANKS Steve.

Reply | Quote

Right-click the pivot table
Wizard…
Drag [Total Turnaround] {note looks like [Total TU]}
underneath “sum of Net P/L”

left-Click and drag the [Data] slightly to the right (Over the “Total”), the status bar should say “Drop to place this field on the column axis” and then release the left button. You will now have 2 columns

Steve

Reply | Quote

hi all, hi Steve, hi anyone, i got the Excel list of Date, Net P/L and Total Turnround. as usual, the Date and Net P/L is done but total turnround left out. just Total Turnround sit besides Net P/L. can someone tell me which cell to place the Total Turnround item to get that? THANKS A LOT.

Reply | Quote

hi Steve, two more pivot table questions;
1. can the year format in 7 days interval days grouping be 85 instead 1985 and how to do that? e.g. 11/1/85-11/7/85 instead 11/1/1985-11/7/1985
2. can data range modified after a pivot table created and how to do that? e.g. original data range is A1:C10 and a pivot table created. when data range for the pivot table change to A1:C20, the pivot table does not counts C11:C20. if data range cannot be modified in the pivot table, a new pivot table has to be created with data range A1:C20.

THANKS.

Reply | Quote

Most of your “dates” are “text” and not “dates”. Excel groups numbers, it can not group text.

Select column A, choose data – text to columns, delimited and it will convert them all to numbers (dates) and the grouping should work.

Whenever you have no justification in a column, text is left justified and numbers are left justified. This is a good “giveaway” that your “numbers” are incorrectly formatted as text.

Steve

Reply | Quote