If then statement with date in VBA

Question

I asked a question this morning regarding changing the date format in VBA, and I've found a partial solution from other discussions:

So I have the following dates (in ddmmyyyy format) in Excel

   A
1/7/2018
2/7/2018
19/5/2018
2/12/2018
31/12/2018
17/6/2018
12/3/2018
17/6/2018
12/3/2018

I use the following code in VBA to change them to date:

Sub convert()

Dim i As Long


finalrow = Cells(Rows.Count, 1).End(xlUp).Row

 For i = 1 To finalrow

Cells(i, 2).Value = CDate(Cells(i, 1))
Next i

End Sub

and obtained the following:

  A           B
1/7/2018    1/7/2018
2/7/2018    2/7/2018
5/19/2018   19/5/2018
2/12/2018   2/12/2018
12/31/2018  31/12/2018
6/17/2018   17/6/2018
12/3/2018   12/3/2018
6/17/2018   17/6/2018
12/3/2018   12/3/2018

So my problem now is the original ddmmyyyy now becomes a messy combination of mmddyyyy and ddmmyyyy.

I know the cdate have effectively changed the entire date format to mmddyyyy, but it confuses the date and month for some of the entries.

NOTE: I also tried the popular jeeped's solution but it gives the same messy result.

So now I'm thinking of a solution to use the if-then statement.

If the DAY in the B column (contain mmddyyyy date) exceeds 12, then change the format to ddmmyyyy.

I guess it's something like

With ActiveSheet.UsedRange.Columns("A").Cells
.TextToColumns Destination:=.Cells(2), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
.NumberFormat = "mm/dd/yyyy"   'change to any date-based number format you prefer the cells to display
End With

if (**the dd in mmddyyyy exceeds 12**) then 
numberformat = "ddmmyyyy"

end sub

So how do I set about doing that?

Answer 1

Avoid number-only date formats

I have often fallen into the trap of thinking I understand that dates are dd/mm or mm/dd, but I have in fact misunderstood and it is doing something odd like using different conventions on different rows.

I now never use number-only date formats, if I can avoid it. Not only in Excel but elsewhere. Unless it is ordered as yyyy mm dd, which is unlikely to be misentered, misdisplayed or misunderstood.

Column A begins as text entered in the spreadsheet, not as a number or date

So where you have written 1-7-2018 and are happily assuming this to be 1 July, it is just text. Excel interprets in its default way (for someone in North America) as 7 Jan. Only when the mm-dd interpretation doesn't work, does Excel try to interpret it as dd-mm. Unfortunately, Excel does this silently.

To avoid this trap, use your second approach, but apply it to the original text formatted dates and explicitly tell Excel to interpret them (all) as dd/mm/yyyy.

First tell Excel to read the input text as though it is in dd/mm/yyyy format. Do this with the xlDMYFormat option. Note xl DMY Format, not YMD.

More details here https://msdn.microsoft.com/en-us/library/office/aa215749(v=office.11).aspx

Then tell Excel how to format the display of the resulting date, i.e. mm-dd-yyyy, using the ".NumberFormat" option.

An example to try

With ActiveSheet.UsedRange.Columns("A").Cells
.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlDMYFormat)
.NumberFormat = "mm-dd-yyyy"  

This short and easy to follow. Note that:

FieldInfo tells Excel how to interpret the text string into a date in internal format.

NumberFormat tells Excel how to display the date, i.e. convert internal format date into a text string.

Answer 2

There is no foolproof method of detecting which format the dates are in. The macro below depends on there being at least one date in text format. That will happen if and only if the format of the dates being input differ from the window regional short date setting of the computer, and the "Month" portion is > 12, and also if there are some dates which have been converted (albeit incorrectly) to real dates, and other dates which are text strings.

If your original data source is really all text in DMY format, then all you need to do is select the Text-to-Columns wizard, and, at the appropriate step, select DMY for the column format.

If the original data is mixed text and "real dates", you might find the below macro helpful. It was written to try to handle that issue, which is what you are running into.

The foolproof solution is to ensure that your DMY sales person inputs dates correctly. Exactly how to do that depends on how he is inputting dates now.

For this macro, the results will be in the numberformat determined by the Windows Regional Short Date settings. But you can change it as required.

Option Explicit
Sub ConvertDates()
    'converts dates that have been mismatched MDY / DMY
    'Assumes dates are all in selected column
    '   Only need to select a single cell in the column
    '   will place results in a column next to original data
    ' If adjacent column is not blank, a column will be inserted
    'Figures out the original format by analyzing a "text" date
    'Time components are converted directly.  This might be OK unless
    ' in a non standard format such as 1400Z

Dim R As Range, C As Range
Dim sDelim As String
Dim FileDateFormat As String * 3
Dim I As Long, J As Long, V As Variant
Dim vDateParts As Variant
Dim YR As Long, MN As Long, DY As Long
Dim TM As Double
Dim vRes As Variant 'to hold the results of conversion

Set R = Selection

'Test that selected cell contains a date
If Not IsDate(R(1)) Then
    MsgBox "Select a cell containing a date"
    Exit Sub
End If

Set R = Intersect(R.EntireColumn, ActiveSheet.UsedRange)
ReDim vRes(1 To R.Rows.Count, 1 To 1)

'Find a "text date" cell to analyze
For Each C In R
    With C
    If IsDate(.Value) And Not IsNumeric(.Value2) Then
        'find delimiter
        For I = 1 To Len(.Text)
            If Not Mid(.Text, I, 1) Like "#" Then
                sDelim = Mid(.Text, I, 1)
                Exit For
            End If
        Next I

        'split off any times
        V = Split(.Text & " 00:00")
        vDateParts = Split(V(0), sDelim)

        If vDateParts(0) > 12 Then
            FileDateFormat = "DMY"
            Exit For
        ElseIf vDateParts(1) > 12 Then
            FileDateFormat = "MDY"
            Exit For
        Else
            MsgBox "cannot analyze data"
            Exit Sub
        End If
    End If
    End With
Next C

If sDelim = "" Then
   MsgBox "cannot find problem"
   Exit Sub
End If

'Check that analyzed date format different from Windows Regional Settings
Select Case Application.International(xlDateOrder)
    Case 0 'MDY
        If FileDateFormat = "MDY" Then
            MsgBox "File Date Format and Windows Regional Settings match" & vbLf _
                & "Look for problem elsewhere"
            Exit Sub
        End If
    Case 1 'DMY
        If FileDateFormat = "DMY" Then
            MsgBox "File Date Format and Windows Regional Settings match" & vbLf _
                & "Look for problem elsewhere"
            Exit Sub
        End If
End Select

'Process dates
'Could shorten this segment but probably more understandable this way
J = 0
Select Case FileDateFormat
    Case "DMY"
        For Each C In R
        With C
            If IsDate(.Value) And IsNumeric(.Value2) Then
            'Reverse the day and the month
                YR = Year(.Value2)
                MN = Day(.Value2)
                DY = Month(.Value2)
                TM = .Value2 - Int(.Value2)
            ElseIf IsDate(.Value) And Not IsNumeric(.Value2) Then
                V = Split(.Text & " 00:00") 'remove the time
                vDateParts = Split(V(0), sDelim)
                YR = vDateParts(2)
                MN = vDateParts(1)
                DY = vDateParts(0)
                TM = TimeValue(V(1))
            Else
                YR = 0
            End If

            J = J + 1
            If YR = 0 Then
                vRes(J, 1) = C.Value
            Else
                vRes(J, 1) = DateSerial(YR, MN, DY) + TM
            End If
        End With
        Next C
    Case "MDY"
        For Each C In R
        With C
            If IsDate(.Value) And IsNumeric(.Value2) Then
            'Reverse the day and the month
                YR = Year(.Value2)
                MN = Day(.Value2)
                DY = Month(.Value2)
                TM = .Value2 - Int(.Value2)
            ElseIf IsDate(.Value) And Not IsNumeric(.Value2) Then
                V = Split(.Text & " 00:00") 'remove the time
                vDateParts = Split(V(0), sDelim)
                YR = vDateParts(2)
                MN = vDateParts(0)
                DY = vDateParts(1)
                TM = TimeValue(V(1))
            Else
                YR = 0
            End If

            J = J + 1
            If YR = 0 Then
                vRes(J, 1) = C.Value
            Else
                vRes(J, 1) = DateSerial(YR, MN, DY) + TM
            End If
        End With
        Next C
End Select

With R.Offset(0, 1).EntireColumn
    Set C = .Find(what:="*", LookIn:=xlFormulas)
    If Not C Is Nothing Then .EntireColumn.Insert
End With

R.Offset(0, 1).Value = vRes

End Sub

Results in MDY format

Answer 3

You could try the following

sub convert()

Dim i as Long
Dim d as Integer
Dim m as Integer
Dim y as Integer
Dim holder as String
Dim output as Date

finalrow = cells(Rows.Count,1).End(xlUp).Row

For i = 1 to finalrow

     holder = cells(i,1).value
     d = Left(holder,2)
     m = Mid(holder,4,2)
     y = Right(holder,4)

     output = DateSerial(y,m,d)

     cells(i,2).value=output
     cells(i,2).NumberFormat="mm/dd/yyyy"

Next i

End Sub

Answer 4

You can change the date to ddmmyyyy using format function.

See below code

Sub convert()

Dim i As Long


finalrow = Cells(Rows.Count, 1).End(xlUp).Row

 For i = 1 To finalrow

Cells(i, 2).Value = Format(CDate(Cells(i, 1)), "d/m/yyyy")
Next i

End Sub

Alternatively, try this

Sub convert()

Dim i As Long
Dim d As Long
Dim m As Long
Dim y As Long


finalrow = Cells(Rows.Count, 1).End(xlUp).Row

 For i = 1 To finalrow

d = Day(CDate(Cells(i, 1)))
m = Month(CDate(Cells(i, 1)))
y = Year(CDate(Cells(i, 1)))

Cells(i, 2) = d & "/" & m & "/" & y

Next i

End Sub