Inconsistent date column

Question

Hi guys :-)

So here is the thing, I have some data that imported from a CSV file. The date column acts weirdly: some of the dates are formed in dd/mm/yyyy format and some in mm/dd/yyyy format.

Interestingly, all of the dates with "dd" numbers above 12 have dd/mm/yyyy format, and all of the dates with "dd" numbers below 12 have mm/dd/yyyy format.

Here is an example of a sequence of dates, I bolded the dates in MM/DD/YYYY

26/09/2016

27/09/2016

28/09/2016

10/05/2016

10/08/2016

10/08/2016

10/12/2016

24/10/2016

25/10/2016

30/10/2016

since I have 1,400 rows in this file, I need to find a way to convert those dates automatically. I tried text to column feature and it didn't work.

Any ideas?

Moved from: Office/Excel/Windows other/Office 2016

Answer 1

Convert them to what: mm/dd/yyyy or dd/mm/yyyy?!

You wrote: Interestingly, all of the dates with "dd" numbers above 12 have dd/mm/yyyy format, and all of the dates with "dd" numbers below 12 have mm/dd/yyyy format.

I suspect that they were input on a system where the actual regional Short Date form is mm/dd/yyyy. Thus, the dates in the form dd/mm/yyyy were treated as text values.

We cannot tell the difference between text and numeric data values in a CSV file because all data are written to the CSV file as they appear in the Excel cells.

Be that as it may, the issue now is: what is the regional Short Data form on your system?

If it is dd/mm/yyyy and you opened the CSV file directly in Excel instead of importing it and (perhaps) setting the column format to Text, then all of the data are numeric dates. Confirm by entering =ISNUMBER(A1) in a parallel column.

In that case, what you interpret as mm/dd/yyyy is actually treated as dd/mm/yyyy by Excel.  So you might enter the following in a parallel column:

=IF(DAY(A1)>12, A1, DATE(YEAR(A1),DAY(A1),MONTH(A1))

Copy the resulting values and paste-value in column A.

Answer 2

=IF(DAY(A1)>12, A1, DATE(YEAR(A1),DAY(A1),MONTH(A1))

did the trick! thank you so much :-)

Answer 3

Hi,

Try this on a copy of the worksheet

1. Select the entire dataset and sort on the Date column in ascending order
2. Select the entire dataset and get the filter icons
3. Click on the Date filter icon and go to Number Filter > Custom > Equals > * > OK
4. Select the dates and go to Data > Text to columns > Delimited > Next > Next > Date > MDY > Finish.

If you do not get the correct result, then try DMY in step 4.

Hope this helps.