If you have an Excel spreadsheet that has a formatted date column and you do a Save As into a Tab or CSV test file the dates are stored in US Format. It doesn’t pick up your region settings to do this conversion. Excel 2003.

Solution

Change the date formatted cell into text first. Just using the format cell will only result in a single number value.

1.      create new column and enter this formula =TEXT(D2,"dd/mm/yyyy hh:mm")  - using the D2 as your cell reference

2.      format a new column with text format

3.      copy the cell(s) from the formula column in step 2

4.      paste the result in to the cell(s) in step 3 using Paste Special and selecting “values”

5.      then delete original column and formula column.

6.      check any cells for those that were converted from NULL, so check for 00/01/1900 00:00 and replace them nothing