Excel SAS addin - date format won't change

[sam5]

Hi,

I have an excel 2016 spreadsheet which creates a pivot table using the SAS addin. 

There is a problem with the way one of the dates is formatted. Even though the table on SAS has the date9. format, the date exports as a number and not the date itself. When I try to change the format on excel to mmm-yy the format will only change if the date is in the row, column or value sections of the pivot table. However, I want the date to be a filter and changing the format when the date is a filter does nothing.

On top of this it works as expected for another date in the table.

[ChrisNZ]

It's hard to debug such things remotely, but several factors in your description give a hint that the misbehaviour might be due to a bad (i.e. invalid) value for that variable somewhere in the data.

[sam5]

Thanks for replying. 

Would mising values be an example of an invalid value? 

[ChrisNZ · Accepted solution]

Not too sure how excel see these.

The best way to go the the bottom of this is to lowly reduce the amount of data until you isolate the culprit.

Or conversely, start with one working row, and add data until you isolate the culprit.

[sam5]

Thanks for your help. Once I changed a missing value to 0 I was able to format the dates without any issue.