Mixed date format in excel

A very difficult problem in spreadsheets is addressing a mixed date format in excel. This is where Excel seems to recognise some dates in a series but others are assumed to be text (to understand how dates work in Excel follow this link). This is often the reason why dates don’t sort correctly either.

The reason for this is that the computer and the data are set-up using different standards i.e. one is using US date format (mm/dd/yyyy) and the other is using the Rest of World format (dd/mm/yyyy).

You will know you have this problem when all the days and months work as a date up to 12 and then from there it is formatted as text.

Excel recognises 5/1/2014 in either format and considers it either the 5th January or the 1st May. However, when the numbers get higher it cannot process the months. So if I entered 25/12/2014 meaning the 25th December and Excel is using US date format, it will refuse to format as a date as there is no such thing as the 25th month.

This problem is serious in that both the formatted dates and the unformatted dates are incorrect.

  1. The formatted dates are typically the wrong way around and need to be converted to the correct date by swapping the Day and Month around using DAY, MONTH and DATE functions .
  2. The unformatted dates also need to be corrected but in this case you will need to use the LEFT, RIGHT and MID functions as Excel sees these as text. Only then can you use the DATE function.

To automate this for ease of use on large data sets, you will need to use an IF function to check whether the date you are looking at is a number (ISNUMBER). If it is a number (and therefore a date although wrong) then use the 1st set of formula, if it is not a number use the 2nd set of formula.