+ Reply to Thread
Results 1 to 4 of 4

Pivot Table - Howto Group by Month

  1. #1
    Registered User
    Join Date
    12-12-2014
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    43

    Pivot Table - Howto Group by Month

    I created a pivot table and the dates are shown as sequential numbers instead of 5/18/2015 format in the first column (row labels). No problem, I go into the pivot table's field setting and change to the date format. Now the pivot table is formatted correctly with the 5/18/2015 format.

    When I right click to group the dates by month I am again presented with a sequential days format instead of "Weekly" "Monthly" "Yearly" etc grouping options. I get three options "starting at" [41662] and "ending at" [42140] with a default "by" value of [100] days.

    Why doesn't excel recognize my first column as dates like I told it in the field settings. I want to group by month and can't just simply use a "30 days" BY value because different months have different quantity of days. There must be a simple checkbox I missed somewhere. What is it?

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Pivot Table - Howto Group by Month

    Try formatting the original dates as dates instead of numbers.

    EDIT: Your Pivot Table is looking at a set of data. I only get this problem when the dates in the original data are formatted as numbers instead of dates.
    Last edited by nigelbloomy; 05-18-2015 at 10:13 AM.

  3. #3
    Registered User
    Join Date
    12-12-2014
    Location
    NYC
    MS-Off Ver
    2010
    Posts
    43

    Re: Pivot Table - Howto Group by Month

    Unfortunately the dates in the original are formatted as "X/X/XX". I'm not sure why Excel is forcing them to number of days after Jan 1st, 1900. What a pain.

  4. #4
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    726

    Re: Pivot Table - Howto Group by Month

    You may use a helper column and use month function there.

    Then group on that field

    Regard
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Group dates in Excel pivot table by month using VB.NET?
    By OldManExcellor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2015, 09:11 AM
  2. [SOLVED] run time error creating pivot table and group dates by year and month
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2013, 07:25 AM
  3. Pivot Table - Need to group transactions by non standard month
    By chris.tinta in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-19-2012, 12:05 PM
  4. Pivot table - group dates per week or month
    By digicat in forum Excel General
    Replies: 1
    Last Post: 01-08-2006, 04:50 PM
  5. [SOLVED] Can't group pivot table items by month in Excel
    By scott_ensley in forum Excel General
    Replies: 1
    Last Post: 02-01-2005, 05:06 PM

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