0

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:

This is a screenshot

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?

3
  • 1
    Looks like your Excel is set to interpret dates as yy/mm/dd. Those cells that aren't changing aren't seen as dates since the second number (for months) exceeds 12. This question has been asked before and an online search for "superuser excel dates problem" yields several questions including this one – cybernetic.nomad Feb 5 '19 at 16:57
  • I have already attempted the text to columns fix and it does not work. The cells with month and year switched are merely reformatted to, for example, '02/16/2002'. It seems to me like this question has not been previously asked as I haven't come across any posts where the raw string itself is being affected. – Mallika Feb 5 '19 at 17:10
  • Where does you data come from? sounds like it's not entered directly in Excel – cybernetic.nomad Feb 5 '19 at 17:14
0

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.

enter image description here

I hope this helps, Brad

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.