Hi Sam,

You should use Power Query to import the data and then you can use the 'Change Type > Using Locale' menu to correct the date format.

See Option 2 in this tutorial: Fix Excel Dates Formatted as Text • My Online Training Hub

And if you have any trouble, this one explains the Change Type > Using Locale menu.

Mynda

Thanks, power query is not an option in this case, I ended up writing this formula which semed to work.

 =IF(ISNUMBER(C3)=FALSE,DATE(RIGHT(C3,4),LEFT(C3,2),MID(C3,4,2)),DATEVALUE(TEXT(C3,"mm/dd/yyyy")))

and rhen formatted the data as a date

Hi Sam, dates are still a p in the A when it comes to dates.
My solution is to always try and convert any data or text that is a date value to a serial date (the format yyyymmdd), it will take some thingkinh especially when the day is 12 or smaller, that's when it fails.

Once you manage that then the issues (most of them at least) are less.

I use these UDFs

Public Function Date2Serial(Optional mydate As Date, Optional inclTime As Boolean = False) As Double
If mydate = 0 Then mydate = Date
Select Case inclTime
Case Is = False
Date2Serial = DateSerial(Year(mydate), Month(mydate), Day(mydate))
Case Is = True
Date2Serial = DateSerial(Year(mydate), Month(mydate), Day(mydate)) + time2Serial(mydate)
End Select
End Function

Public Function time2Serial(Optional tTime As Date) As Double
If tTime = 0 Then tTime = Now()
time2Serial = TimeSerial(Hour(tTime), Minute(tTime), Second(tTime))
End Function

Public Function serial_TimeStamp(Optional mydate As Variant) As Double
If Not IsDate(mydate) Then Exit Function
Dim xDate As Date
If mydate = 0 Then xDate = Now()
serial_TimeStamp = Date2Serial(xDate) + time2Serial(xDate)
End Function

Hopw it helsp some