Excel VBA - Extracting correct dates from poorly formatted dates?

Question

I am currently studying VBA programming, and faced with a situation in which I would appreciate your help. Ideally, not just finding a solution, but also understanding how and why the solution works.

Say there is a database from which you can export a data spreadsheet. One of the columns has date values, but they are poorly formatted from export. The system sends dates as mm / dd / yyyy hh: mm AM / PM, for example, 04/11/2014 09:24 AMbut the table has this designation as dd / mm / ..., which means that it enters 04 as the day and 11 as the month.

In this column, if the day precedes or includes 12 months, the cell is formatted as a date. If the day has passed on the 12th, the cell is formatted in a common format.

My question is: can I write a VBA macro that could change the day and month values ​​and create the correct dates in a new column? I would think that first you need to determine if the cell is formatted as a date, and then somehow display the date and month in the correct positions or format it as a common format, and then use a different code to extract the correct date.

If this is too common a problem for this community and another community is more suitable there, I will gladly post my question there.

EDIT:

After my comment below, I played with functions and searched for other similar functions that can help do what I need, switch the day value with the month value, and so far I have:

'for dates with general format: 04/14/2014 11:20 AM
=DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)) 'in a column for the date
=TIME(MID(A1,12,2),MID(A1,15,2),"00") 'in a column for time, since I may need this

'for dates with a date format: 4/11/2014  7:35:00 PM
=DATE(TEXT(A1,"yyyy"),TEXT(A1,"dd"),TEXT(A1,"mm")) 'in a column for the date
=TEXT(A1,"hh:mm AM/PM") 'in a column for time

, , A.

VBA? , , , . - VBA "", . , , - .

EDIT2:

, . , , FIND , , IFERROR Excel, = IF.

'to get the correct date
=IF(IFERROR(FIND("/",A1),0)>0,DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)),DATE(TEXT(A1,"yyyy"),TEXT(A1,"dd"),TEXT(A1,"mm")))
'to get the correct time
=IF(IFERROR(FIND("/",A1),0)>0,TIME(MID(A1,12,2),MID(A1,15,2),"00"),TEXT(A1,"h:mm AM/PM"))

, , , VBA .

Answer 1

. , , :

=IF(IFERROR(FIND("/",A1),0)>0,DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)),DATE(TEXT(A1,"yyyy"),TEXT(A1,"dd"),TEXT(A1,"mm")))

VBA:

= Instr
= DateSerial
= ( , )

:

Dim mydate As Date
Dim myformat As String

myformat = "mm/dd/yyyy hh:mm AM/PM"
If InStr(1, [A1], "/") > 0 Then
    mydate = DateSerial(Mid(Format([A1], myformat), 7, 4), _
        Left(Format([A1], myformat), 2), Mid(Format([A1], myformat), 4, 2))
Else
    mydate = DateSerial(Year([A1]), Month([A1]), Day([A1]))
End If
[B1] = mydate

, [A1] - Evaluate, Evaluate("A1").
Cell A1, . Range Object : Range("A1"). , . .

:

=IF(IFERROR(FIND("/",A1),0)>0,TIME(MID(A1,12,2),MID(A1,15,2),"00"),TEXT(A1,"h:mm AM/PM"))

:

Dim mytime As Date
If InStr(1, [A1], "/") > 0 Then
    mytime = TimeValue([A1])
Else
    '~~> myformat is declared above
    mytime = TimeValue(Format([A1], myformat))
End If
[C1] = mytime

, :

Select Case True
Case [A1].NumberFormat = "General"
    mydate = DateSerial(Year([A1]), Month([A1]), Day([A1]))
    mytime = TimeValue(Format([A1], myformat))
Case [A1].NumberFormat = myformat '~~> again this is declared above
    mydate = DateSerial(Mid(Format([A1], myformat), 7, 4), _
        Left(Format([A1], myformat), 2), Mid(Format([A1], myformat), 4, 2))
    mytime = TimeValue([A1])
Case Else
    MsgBox "Invalid Format. Cannot be evaluated"
End Select
[B1] = mydate: [C1] = mytime

, .
, .
- , , .