+ Reply to Thread
Results 1 to 6 of 6

Unable to group dates in a pivot table despite formatting the column

  1. #1
    Registered User
    Join Date
    11-27-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Unable to group dates in a pivot table despite formatting the column

    I have a database of roughly 9000 rows. My first sheet is this data in a raw format (Masterdata!). Each row is a fire-rescue response call and therfore has a time stamp. The raw data uses different formats to provide the time stamp. My second sheet is my filtered data (Filtereddata!). In order to make these time stamps uniform I have a formula that looks at the Masterdata! timestamp column and if it is in a numeric format changes it to a text date and if it is a text date perserves it.

    =IF(ISNUMBER(Masterdata!K:K),TEXT(Masterdata!K:K,"dd/mm/yyyy hh:mm"),Masterdata!K:K)

    This gives me the "timestamp" which has both the incident start time and the date. I have =left and =right formulas breaking apart the date and the time. Therefore I have two columns on my Filtereddata! for the date of the incident and the time the call came in. What I would like to do is run some pivot tables and group incidents by month. I am unable to group them once I run the pivot table. I get a "Unable to group these items" (or something like this) message. I have assured multiple times that the date column is in DATE format. However, when I change the date to long date or short date I see no changes in the column so I am assuming there is something happening that I'm missing. The formula populating the date column on Filtereddata! is as such:

    =LEFT(frfiltereddata[[#This Row],[Timecode]], FIND(" ", frfiltereddata[[#This Row],[Timecode]], 1))

    Any ideas or suggestions are appreciated.

    Thanks in adavance.

  2. #2
    Registered User
    Join Date
    11-27-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Unable to group dates in a pivot table despite formatting the column

    Having looked at it closer it appears to be an issue with the Masterdata! column more than anything else. It appears that the column alternates between MM/DD/YYYY and DD/MM/YYYY with excel only recognizing the latter. I have put together a sample workbook.

    Again any ideas are greatly appreciated.

    Sample Workbook.xlsx

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Unable to group dates in a pivot table despite formatting the column

    This is because your dates are actually text. In order to group them you need to convert them. You can do that by making a small adjustment in the formula by adding -- to at the beginning of the formula. Then format it as date.

    =--LEFT([@Timecode], FIND(" ", [@Timecode], 1))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    11-27-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Unable to group dates in a pivot table despite formatting the column

    That works to help the formatting. Thanks AlKey. However the problem reamins, I have about half of the rows that are formatted as Month first and that formula only returns a #VALUE result for those.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Unable to group dates in a pivot table despite formatting the column

    To convert those dates follow these steps:

    1. Highlight data with the dates
    2. Click on Data located on the ribbon and click on Text to Columns feature.
    3. Then click Next, Next, select radio button for Date and from Drop-down menu select DMY and click Finish.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Unable to group dates in a pivot table despite formatting the column

    On Masterdata Timecode column, select the column the go to Data, Text to columns and click next until you get the choice of data type. Select DATE click finish. This at least with your sample changed the "dates" to real Excel dates.

    Repeat the procedure on the Filtereddata sheet.

    Here is your sample that I continued by changing the formulas in the date and time columns and then changed the formatting.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Pivot Group Dates - how to remove start and end dates from table
    By markoloughlin in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-21-2012, 04:19 AM
  2. Pivot Table - Group Individual Dates Into Months
    By Kolin in forum Excel General
    Replies: 0
    Last Post: 07-15-2010, 11:02 AM
  3. [SOLVED] Pivot table group dates
    By RD Wirr in forum Excel General
    Replies: 3
    Last Post: 07-22-2006, 03:10 PM
  4. Group Dates in Pivot Table
    By tufmarkerr in forum Excel General
    Replies: 0
    Last Post: 04-23-2006, 03:42 PM
  5. Pivot table - group dates per week or month
    By digicat in forum Excel General
    Replies: 1
    Last Post: 01-08-2006, 04:50 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