I’ve had things like this happen to me. Long story short, there’s some meta-data or something inside the column’s data that’s getting in the way.
Try this… and it sucks, but it works:
- Insert a new temporary column
- Inside this column, use the formula:
- where “column” is a cell reference to the one with the serial number date.
- So, for example, if my dates are in column D, and I added a new column E, my formula would be:
- Copy this formula all the way down the sheet for each row you have data.
- set the type of your new column to date
- this should give you your dates as seen as dates.
- Copy the entire new column you made, and paste it OVER the original column - replacing it’s data.
- So, continuing our example from before, after dragging the formula down all the rows
- I copy the entire column E
- and paste is over the entire column D.
- Then delete your temporary column.
This should result with the same data, but in the appropriate format for your sheet.