Converting Dates

[tobermory]

Hello Ladies and Gentlemen,

I have a column of dates, that I want in UK date format (DD/MM/YYYY)

Occasionally, the data I receive the dates are muddled up, some are in DD/M/YYYY and some are in MM/DD/YYYY

The ones with a star are incorrect, and come in 'General' format.

[TABLE="width: 114"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Date
[/TD]
[/TR]
[TR]
[TD="align: right"]03/06/2017[/TD]
[/TR]
[TR]
[TD]07/18/2017*
[/TD]
[/TR]
[TR]
[TD]03/16/2018*
[/TD]
[/TR]
[TR]
[TD]02/26/2018*
[/TD]
[/TR]
[TR]
[TD="align: right"]03/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]04/11/2018[/TD]
[/TR]
[TR]
[TD]05/13/2017*

[/TD]
[/TR]
</tbody>[/TABLE]
I CAN already convert these, by selecting MDY in the 'Text To Columns' Wizard.

However, occasionally, my columns are dates that ARE already in the correct format. So if I run the wizard, it converts some from DD/MM/YYYY to MM/DD/YYYY.

Would there be some wizardy, where it looks at a column, and if the dates are all already in DD/MM/YYYY, don't do anything, but if there are some in MM/DD/YYYY, then convert these to DD/MM/YYYY?

Thank you.

 

[Rick Rothstein]

However, occasionally, my columns are dates that ARE already in the correct format.

I don't think that is necessarily true. If all the dates came from the same source, then all of them had their month/day positions reversed from what you want. Just because Excel made some of them actual dates does not mean they are the correct dates. If the day number is 12 or less, then Excel will make that date into a "real" date, but it will not be the corrected date. You should check some of those "real dates" back to the source to see if the month/day order actually matches what you think they should have converted to.

 

[MARK858]

I think exactly the same as Rick suspects and if he is correct then the formula below should correct it (I am sure Rick will give a VBA solution once you confirm back).

=IFERROR(IF(ISNUMBER(A1),VALUE(TEXT(A1,"mm/dd/yyyy")),DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,2))),DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,1)))

 

[tobermory]

You gentlemen are so clever, both completely correct and I'm completely wrong!