+ Reply to Thread
Results 1 to 3 of 3

fixing dates stored as text for pivot tables

  1. #1
    Registered User
    Join Date
    08-23-2012
    Location
    Omaha, NE
    MS-Off Ver
    MS 365
    Posts
    58

    fixing dates stored as text for pivot tables

    I have an excel file that's an export out of a phone system. The dates aren't recognized as dates by a pivot table. I need them to be dates in order to group them. I simply want to look at average call durations over a grouping of dates. Pretty simple but what's the best way to convert the dates to dates that are usable by pivot tables? I've attached the file in question.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: fixing dates stored as text for pivot tables

    Hi,

    Add another column to your table and add the formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Have you checked with the provider of the file whether they can export it in an Excel format. Most back office systems allow this sort of flexibility. Tthat would avoid you needing to add the helper column.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-23-2012
    Location
    Omaha, NE
    MS-Off Ver
    MS 365
    Posts
    58

    Re: fixing dates stored as text for pivot tables

    Oh man, that was easy. I'm an idiot. I created a bunch of fields to split out like you did then was trying text() to get it into date format and it wasn't working. Thank you so much.

+ 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. Array changes dates stored as dates into text.
    By KNevland in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2014, 08:56 AM
  2. [SOLVED] Marco to convert numbers stored as text to numbers and dates stored as text to numbers
    By a2424 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-22-2014, 10:19 AM
  3. Calculate orders past agreed dates in pivot table and averages in pivot tables
    By applesandpears in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-18-2012, 05:26 PM
  4. Replies: 8
    Last Post: 01-16-2011, 07:27 PM
  5. Pivot Tables and dates
    By JeffCPA in forum Excel General
    Replies: 3
    Last Post: 08-22-2010, 01:52 AM
  6. [SOLVED] Converting numbers stored as dates to text in Excel
    By David from Furdale in forum Excel General
    Replies: 1
    Last Post: 07-17-2006, 01:40 PM
  7. [SOLVED] Dates in Pivot Tables
    By Zani in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2006, 10:15 AM

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