Help in formatting mixed dates

Hello peeps

I am importing data from a system into Excel, but the date formatting for records is mixed - the data has both date and date-as-text string records. As an aside, the data format is in mm/dd/yyyy, while I am using a system date of d/mm/yyyy, and require to reformat the dates also to d/mm/yyyy.

I have attempted a multi-step approach but encounter an issue in formatting the date-as-text strings. Here's a sample of the data I'm working with:

dateformats.PNG

As indicated, I'm working with data that has records formatted as dates as well as dates-as-text.

My formula in Column D is: =TEXT((LEFT(F47,10)),"d/mm/yyyy")

My formula in Column B is: =DATE((RIGHT(D47,4)),(LEFT(D47,2)),(MID(D47,4,2)))

Column F contains the imported data.

My aim is to convert all dates into the format of d/mm/yyyy, and to lose the timestamps. It seems that I am close but encounter a #Value error for record examples from lines 62 onwards, for the dates-as-text records where the month has a value of less than 10 (ie. October converts just fine, but September does not).

Please try

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT(TEXT(F25,"dd/mm/yyyy"),10),"/",REPT(" ",10)),10,10)&REPLACE(SUBSTITUTE(LEFT(TEXT(F25,"dd/mm/yyyy"),10),"/",REPT(" ",10)),10,10,""))," ","/")

I advice you to change system date format.
>Start > Control Panel > Region Language > Additional Setting > Date Tab > Short Date Format as: dd-MM-yyyy > Long Date Format as: dd MMMM yyyy
Then importing data from a system into Excel.

Try again

D25
=TRIM(TEXT((LEFT(F25,10)),"dd/mm/yyyy"))

B25
=DATE(RIGHT(D25,4),LEFT(D25,FIND("/",D25)-1),MID(D25,FIND("/",D25)+1,2))

Or revise Post#4 formula without helper column
H25
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT(TEXT(F25,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9)&REPLACE(SUBSTITUTE(LEFT(TEXT(F25,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9,""))," ","/")