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?
No comments:
Post a Comment