Excel switching year and month for some cells but not others

Question

I have been given a spreadsheet with dates that are being inconsistently formatted by Excel. Since dates were entered into the spreadsheet as mm/dd/yy (i.e. no four-digit years), Excel has assumed the month is the year. So, 02/02/16 is being treated as Feb 16 2002, but this is only happening to some cells and not others. Cells where this is not happening do not seem to be autoformatted by Excel at all. I would like to format all dates consistently as mm/dd/yy or mm/dd/yyyy before carrying out further processing.

I have tried to select the cell and specify the formatting (both as date and as custom) but it does not have any effect. Changing my regional settings works for the date/year switched cells but it then reverses the order of the other cells that were not being formatted before.

This is a screenshot of what some of my data looks like:

So whereas ID 1-B is being read as Feb 16 2002, ID 3-B is only being read as 02/24/16.

I have tried multiple fixes but it looks like there's something funky going on in Excel that I can't access. The wonky formatting carries through when I try to import this spreadsheet into R to fix it there, so that didn't work for me either.

Please let me know if there's anything about this error I can clarify. It seems like there isn't much info about this error online (most people only have the day and month switched and that too happens consistently throughout the dataset). Is this something I can even fix?

Answer 1

You might need to format those dates as text first then apply a formula similar to the one I've used below as a demonstration below.

I hope this helps, Brad

Answer 2

Have a look at E column. Top two dates are appearing slightly shifted to right, where are rest of them are at somewhat left.

That indicates that top two dates have been interpreted correctly, and they are in Excel's Date format. But lower dates have not come correctly and Excel knows that 19, 30, 21, 20 can not be correct value for months, so it has taken them as text.

In C, D column, maybe your column with was just enough that is taken by Text or Date format, so the difference is not visible.

You increase column width for all columns, then text data will appear towards left and date data will appear somewhat right as is happening in E column.

That way you will be able to quickly visibly identify and will be able to take corrective action.