+ Reply to Thread
Results 1 to 8 of 8

Formatting the date column for grouping in a pivot table

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Formatting the date column for grouping in a pivot table

    Hi,

    I've imported a CSV file of expenses with dates in it (please see attached file). From the sheet "Expense.csv" I've created a pivot table "Pivot 1" but can't group the first column "Date" by month. I've recreated the data "Expense.csv (2)" replacing the date column with dates I've made up and it works fine - "Pivot 2" - so clearly the format of the date column in the first sheet is causing the problem. I tried to re-format it using custom date, re-create it using functions but couldn't fix it so the pivot table "Pivot 1" worked.

    Can anyone explain what the fundamental issue is?

    thanks,

    Neil
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Formatting the date column for grouping in a pivot table

    From a quick check, your Dates in Column A, Pivot 1 do not appear to be dates, but are text strings that look like dates.

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Formatting the date column for grouping in a pivot table

    Thanks for reply, I tried re-formatting them in date format but it wouldn't do so - that's what's bugging me! cheers, Neil

  4. #4
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Formatting the date column for grouping in a pivot table

    Using the Format function won't work.

    Try using Text To Columns function:

    First open up two new columns next to Column A
    so you have a Blank column B & C

    Select Column A
    On the Ribbon, Select, Data Tab,
    Select, Text to Columns

    When the Wizard comes up,
    Select the Delimited button, click Next
    Select the Space as your delimiter, click Next

    Your Date column should be Highlighted,
    if not, use your mouse the select the
    date column, and click the DATE button,
    make sure you have the correct format
    in the small drop box.

    Click Next

    Your Column A should now be formatted as a
    real DATE, and your Column B should have
    your TIMES.

    In your new, empty column C enter the formula:

    =A2+B2 <-- yes that is a PLUS sign

    Drag down as many rows as needed.

    Select your Column C,
    Do a Copy, Paste Special, Values

    You can now Delete your old column A & B

    See if that works.

  5. #5
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Formatting the date column for grouping in a pivot table

    Thanks, it works down to the instructions about column C in splitting the times out. I get an error when trying to follow your instructions about column C - what is that bit of your instructions trying to do, it seems to be re-creating the original formulae....? thanks for you help to date, regards, Neil

  6. #6
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Formatting the date column for grouping in a pivot table

    Have worked out how to make it do what I want to do now so in essence it's solved. However, still interested in my q's above and also why the text formatting won't work. Is it that the original data has times in it and so it can't be formatted as a date....? thanks, Neil

  7. #7
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Formatting the date column for grouping in a pivot table

    it works down to the instructions about column C in splitting the times out

    Column C, never splits the time,
    Column C combines the Date & Time into one cell.

    also why the text formatting won't work

    Because what you had was a String of Text, not a Date.

    Excel stores all DATES as integers and all TIMES as decimal fractions.

    Excel begins it DATES by counting the number of Days since 01/01/1900 12:00:00 a.m.
    TIMES are stored as decimal numbers between .0 and .99999, where .0 = 00:00:00 and .99999 = 23:59:59.

    So with the above, we can combine the two and create numbers that have a decimal and an integer portion.
    So, for example, the number 32331.06 represents the DATE and TIME: 7/7/1988 1:26:24 a.m.
    This is how it allows us to do all the math with Date & Times.

    For more info see here: http://www.cpearson.com/excel/datetime.htm#TOC

  8. #8
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Formatting the date column for grouping in a pivot table

    Excellent - many thanks for your efforts in explaining the detail to me, best regards, Neil

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1