The Tyranny of the US date format


When trying to work with a CSV (Comma Separated Value) file that one of my American colleagues sent to me I came across this curious issue when I tried to open it in Excel. The dates were in the American format of MM/DD/YYY and opening it on a computer with UK settings meant that Excel tried to automatically convert the dates which meant that every date was wrong. If the day was before the 13th, it thought it was a month, if it was greater than 12 then it came in as a string. Regardless, all of the dates were wrong during import. There is no easy way to stop Excel from automatically trying to interpret what a cell value could be, either number or text or date. So every time you open a file with a date in this format, it makes some wrong assumptions. Once a date is automatically converted, you can't run a formula on the column to convert it to the right format because some cells will be in String format while others will be in the Date format.

The Fix

  1. Open the file in a text editor.
  2. Look for the date separator. It is usually / but can also be -
  3. Do a search and replace of the date separator character with an uncommon character like ~.
  4. Open the file in Excel. The dates will now not be processed during open and brought in as String.
  5. Write the formula for the date using the LEFT, RIGHT and MID commands. See below for the formulas.
  6. Replace the old date columns with the corrected columns.

Some formulas

Using the Excel DATE(year, month, day) function to create a date, you need to supply the numbers and this can easily be done by string functions. If the dates are fixed width, the formulas will be easier but this isn't always the case. For example the date 03/05/2019 versus 3/5/2019. You are not really sure how many digits everything except the year is going to be. Here the FIND() function comes to the rescue. Just look for the first ~ (assuming that was the replacement character you used) and then the next one. The formulas to extract the dates are...

'Assuming the field as in cell A1
'Example date
A1 = 03~25~2019

'Month
=INT(LEFT(A1,FIND("~", A1)-1))

'Day
=INT(MID(A1, FIND("~", A1)+1, FIND("~",A1, FIND("~", A1)+1)-FIND("~", A1)-1))

'Year
=INT(RIGHT(A1,4))

'Full Date
=DATE(INT(RIGHT(A1,4)),INT(LEFT(A1,FIND("~", A1)-1)),INT(MID(A1, FIND("~", A1)+1, FIND("~",A1, FIND("~", A1)+1)-FIND("~", A1)-1)))

You can copy these formulas to any other cell as long as the date is in cell A1.