Pivot table not accepting a specific date.

[fawlty128]

Our workbook has a data sheet containing hundreds of rows with data including a start date and end date for each rebate period. This data is brought into a pivot table and everything works perfect except one of the start dates reflects a E64 in the pivot table. If we change date from 5/6/2022 to 5/5/2022, pivot table pulls in the 5/5/2022 date as expected. Since 5/6/22 is a valid calendar date does anyone know of any reason we are having this issue?

Thank you in advance for any insight as to what may be causing this problem.

 

[arthurbr]

Perhaps this one date is text looking like a date. Is it left aligned in the cell when you remove any manual alignment in the cell? If so it is text, not a date

 

[fawlty128]

Perhaps this one date is text looking like a date. Is it left aligned in the cell when you remove any manual alignment in the cell? If so it is text, not a date

It does not appear to have anything to do with formatting as any other date I put in that cell works fine with the pivot table

 

[arthurbr]

Formatting is how Excel shows things to the outside world. The underlying value of a date is a number ( e.g; today is 44996 to XL and can be formatted as a date or anything else, while the underlying value ( used for calculations) stays 44996). Not so with text. So again, is this "date" left or right aligned when manual alignment is removed?

 

[fawlty128]

General alignment is to the right. But to be honest, whether underlying value is text or in this case number, I do not see how changing a single digit (no formatting change is occurring) results in the pivot table pulling in 64E instead of the date. The pivot table is returning 64E only when the date 5/6/2022 is used the data sheet. There are only a handful of people who have access to the workbook and none of them have much knowledge with macros which was the only thing I could think of that could cause this issue.