Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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 <finish> 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

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  8. #8
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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, <back>, set the new range, <finish>

    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  11. #11
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank You Steve. i managed to do that.

  12. #12
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    Right-click the pivot table
    Wizard...
    Drag [Total Turnaround] {note looks like [Total TU]}
    underneath "sum of Net P/L"
    <Finish>
    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

  14. #14
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •