Inconsistent date format

Hi everyone,

I have a date column but some have the dd/mm/yyyy in UK format separated by / and others have mm-dd-yyyy in US format separated by a -.

I've tried text to columns ensuring it's date DMY but that does nothing, I've tried converting to text then doing a =datevalue which just errors, =value doesn't work either on the US entries.

(btw I'm in the UK)

I think I'm going to need a column next to the date column which has a formula to make the dates all the same, but my tiny little mind doesn't know how.

Can someone please save me?

Attached is my date column of doom.

Jem

In B2

=IF(ISNUMBER(SEARCH("-",A2)),DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)),A2)

copy down

Format column as Date