+ Reply to Thread
Results 1 to 4 of 4

Pivot Table Date changing to 00/01/1900

  1. #1
    Registered User
    Join Date
    05-24-2022
    Location
    North East, England
    MS-Off Ver
    Version 2204
    Posts
    2

    Angry Pivot Table Date changing to 00/01/1900

    Hi,

    Just teaching myself Pivot Tables and hit a wall.

    My Aim - To show the number of days between two dates (Context - Data table shows date an enquiry was received and the date it was called, I want the Pivot Table to show the response time in days

    Data...

    Attachment 781535

    1st attempt: PivotTable Analysis > Field Items and Sets > Creating Formula. As Shown below...

    Attachment 781533

    and the following happened...

    Attachment 781536

    I know if I change the Format It will display as the number of days but the issue is that G5 should have the same answer as G4 and it's showing 00/01/1900.

    I thought maybe if the Pivot Table pulled through the data I could just do a Formula to calculate the difference but doing this still some how changes the date for Douglas to 00/01/1900 (as below)

    Attachment 781537

    Any help with this would be much appreciated. Lots of hits on google about this date but not in this way.

    TIA

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pivot Table Date changing to 00/01/1900

    Hi and welcome to the forum

    Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-24-2022
    Location
    North East, England
    MS-Off Ver
    Version 2204
    Posts
    2

    Re: Pivot Table Date changing to 00/01/1900

    Can my post still be seen? And do I have to make them changes? The pictures are there to prevent people recreating my data as someone might know a quick fix?

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: Pivot Table Date changing to 00/01/1900

    Your pictures don't show up for me (and for many browsers) and the links simply show invalid attachment if you click them.

    00/01/1900 is just the value 0 formatted as a date.
    Rory

+ 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. Date function with 5 and 6 digit dates - changing to 1900's - SOLVED
    By thmsnmg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2017, 08:56 AM
  2. date pasted as 1/2/1900 in excel instead of 1/1/1900
    By ekkslatha in forum Excel General
    Replies: 4
    Last Post: 10-25-2014, 08:34 PM
  3. Changing a pivot chart column color based on the pivot table category
    By Shawne in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2014, 09:12 AM
  4. Changing pivot table date formatting
    By DANDERSEN in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-22-2013, 05:59 PM
  5. Macro for changing the date from 02-Jan-1900 to 01-Jan-1900
    By Lavanya Anandan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2013, 07:08 AM
  6. Issues with Pivot Table data displaying 01-01-1900
    By plantronics#23 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-04-2013, 10:12 AM
  7. Excel 2007 : changing a defualt date (00/00/1900) to a blank
    By Icehockey44 in forum Excel General
    Replies: 9
    Last Post: 03-08-2012, 11:24 AM

Tags for this Thread

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