Share via

Pivot stable STILL doesn't recognize dates as dates

Anonymous
2021-06-10T16:53:28+00:00

I cannot create a pivot table that treats dates as dates.

Source table says they're dates. Source table says TYPE() returns 1 so it's a number, which is probably why I can't DATEVALUE() it, because it already is a date in the source and not text. Probably why I can do math with it. Source table lets me sort OLDEST TO NEWEST. Pivot table is a piece of useless garbage.

Data text to column does not work any format. Probably because it is already actually a date.

Changing display to YYYY-MM-DD does not work and the pivot table won't even allow me to do that so at least sorting A to Z would work because it's not recognizing a date as a date.

My regional systems settings are US Gregorian

There's 20000 lines and I expect to add 5-7k more in the next week. This isn't random data copy pasted from random programs. This is data from other excel files, entered with data validation no less.

This problem is recurring for apparently thousands and thousands of people over the course of at least 10 years according to forum entries, perhaps MS should get around to fixing it.

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2021-06-10T18:36:32+00:00

    First, work on a copy of your workbook. Then delete your existing pivot table. Make sure that there are no non-date values in your column - a single cell that has text - even an extra space - rather than an actual date will often mess up the pivot table field.

    You can add a column of formulas like

    =ISERROR(YEAR(A2))

    and then filter to show TRUE results to isolate the date errors.

    Once you are absolutely certain that you only have dates in that field, re-build your pivot table. And keep your fingers crossed while you do it.

    10+ people found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-06-10T22:16:00+00:00

    If the source data was text it would remain text if you copied it. The following are all non-dates:

    5/1/21
    Apr 5, 2021
    2021-05-01
    5/1
    1-May
    1-Jun-21
    Jun-21
    August-2020
    1-January-2021
    F-20
    March

    Why? Because they are text. A date can be text because 1. The cell into which the original user typed the date had already been formatted as text (most likely). 2. The dates could have been entered with a leading apostrophe (less likely). 3. The data could have been downloaded from a system that treats dates as text (some database systems).

    In many cases you can correct the problem quickly without reentering the data: 1. Select an empty cell, 2. Press Ctrl+C, 3. Select all the "supposed dates", 4. Press Ctrl+Ald+V (paste special short cut), 5. Choose Add and click OK. All text dates will be converted to numbers of the form 44291. Choose Format, Number, Date and choose the date format you want, (it won't make any difference to Excel what you choose).

    Note: If a date has been entered as text, changing the Format will not change the text to a date.

    If the data you have been receiving has been coming in as text, you may need to do the procedure every time you receive new data. Note this is NOT an Excel problem.

    Shane Devenshire

    9 people found this answer helpful.
    0 comments
  2. Anonymous
    2021-06-10T20:51:53+00:00

    I would expect that at one point there was a non-date value in the data set, and that "corrupted" the date field until your rebuild of the pivot table cleared that out. Just like phantom categories that once existed - pivot table caches are notorious for hanging onto outdated values.

    3 people found this answer helpful.
    0 comments
  3. Anonymous
    2021-06-10T19:28:43+00:00

    Hilariously returned all FALSE as expected. But I rebuilt the pivot table again anyway after running that, despite changing nothing at all in my original data and having rebuilt the tables before with the same error, it works. Apparently I just needed someone somewhere else in the universe to confirm that these were actually dates. I'll add this to the ever growing list of things to try the next time this happens. Lol thank you, this was days of frustration at this point.

    3 people found this answer helpful.
    0 comments
  4. Anonymous
    2021-06-11T12:46:34+00:00

    The eternal mysteries of Excel :(

    2 people found this answer helpful.
    0 comments