Convert string dates to proper numerical date format

[Codpops]

Hello, Everyone!

I am trying to convert text 'dates' like Sat. 1/05/2007 to real dates. I need to find all 'date' cells in range B4:B100 (they are filled in yellow), strip off the offending text (i.e. Sat.), and use DateValue to turn the rest into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place.

I've spent days trying to write this macro. I've had help from experts in forums, but whenever I need to make any slight modification, a
trainwreck ensues. Please see the code below.

Sub switchDate()
Dim MyDate As String
For Each cell In ActiveSheet.Range("B4:B100")
    If cell.Interior.ColorIndex <> xlNone Then
       cell.Value = MyDate.Value
            MyDate = Trim(MyDate)
            If InStr(MyDate, " ") > 0 Then
            MyDate = Mid(MyDate, InStr(MyDate, " ") + 1)
            End If
        MyDate = DateValue(MyDate)
    End If
Next
End Sub

This doesn't even begin to work and I can't begin to guess why. Could
somebody out there please make the tweaks and tell me what I did wrong, that I might not do it hence?

I thank you greatly, and have a terrific day.

Arlen

[ShaunM]

Hi Arien

I have altered your code and provided some comments (you should be able to compare the code below with your code to see where you went wrong):

Sub switchDate()
    Dim MyDate As String 'Declare your vaiable
    
    For Each cell In ActiveSheet.Range("B4:B100") 'Set range for your loop
        
        If cell.Interior.ColorIndex <> xlNone Then '(first if)If  the cell contains a colour do the next statment
            MyDate = cell.Value 'pass the value of the cell to the variable
            MyDate = Trim(MyDate) 'Remove any leading or trailing spaces from the variables value
            
            If InStr(MyDate, " ") > 0 Then '(second if) If there are any spaces in the variables value do the next statement ie. proceed
                MyDate = Mid(MyDate, InStr(MyDate, " ") + 1) 'Get anything after a space and pass it back to the variable
            End If ' End of second if statement
            MyDate = DateValue(MyDate) 'Convert variable value to a date
        
            cell.Value = MyDate 'Write variables value back to the cell from the information was collected
        End If ' End of first if statement
    Next ' loop
End Sub

Cheers
Shaun

[rylo]

Arlen

Can you please take time to review the forum rules, specifically the rules regarding searchable titles, and code wrapping.

I've edited your post this time for you.

rylo

[protonLeah]

1. The rules request that you wrap your code...
2. (a) The variable MyDate is declared as string-type; but in line #5 you wrote: MyDate.Value, which should have given you a compile error because strings have no .Value qualifier.
(b) Line #5 is trying to write the contents of the variable to the cell:

(Let the) cell.Value = MyDate.Value

3. Since your For..Each loop is testing the formatting of a cell, line #5 is meant to load the variable MyDate with the contents of the cell. It should be:

MyDate = Cell.Value

Note: MyDate is empty (you did not initialize it prior to line #5) so that the code:

cell.Value = MyDate

would have erased the contents of the test cell!

[Codpops]

Shaun and Proton,

The code is perfect. You're both awesome for taking the time to explain this. This is the only way I can really learn VBA at all.

Now, I've rounded up this final bit of code to format the dates after converting so that they look like they did as text...Arial, 10, bold and centered. Do I use cell As Range, or create a new Range, r? And where does it all go...

             Dim r as Range (is up top)

and this goes...
               With r
                .NumberFormat = "ddd mm/dd/yy"
                With .Font
                    .Bold = True
                    .Name = "arial"
                    .Size = 10
                End With

And, truly, that is all.

Thanks guys.

[ShaunM]

Hi Arlen

I have modified your code to inculde your formatting statements:

Sub switchDate()
    Dim MyDate As String 'Declare your vaiable
    
    For Each cell In ActiveSheet.Range("B4:B100") 'Set range for your loop
        
        If cell.Interior.ColorIndex <> xlNone Then '(first if)If  the cell contains a colour do the next statment
            MyDate = cell.Value 'pass the value of the cell to the variable
            MyDate = Trim(MyDate) 'Remove any leading or trailing spaces from the variables value
            
            If InStr(MyDate, " ") > 0 Then '(second if) If there are any spaces in the variables value do the next statement ie. proceed
                MyDate = Mid(MyDate, InStr(MyDate, " ") + 1) 'Get anything after a space and pass it back to the variable
            End If ' End of second if statement
            MyDate = DateValue(MyDate) 'Convert variable value to a date
        
            cell.Value = MyDate 'Write variables value back to the cell from the information was collected
            
            cell.NumberFormat = "ddd mm/dd/yy"
            
            With cell.Font
                .Bold = True
                .Name = "arial"
                .Size = 10
            End With

        
        End If ' End of first if statement
    Next ' loop

Your were very close all you needed to do was supply the sub with the reference to what you wanted formated. (there was no need to redeclare the range)

Cheers

Shaun

[jindon]

Shaun and Proton,

The code is perfect. You're both awesome for taking the time to explain this. This is the only way I can really learn VBA at all.

Now, I've rounded up this final bit of code to format the dates after converting so that they look like they did as text...Arial, 10, bold and centered. Do I use cell As Range, or create a new Range, r? And where does it all go...

             Dim r as Range (is up top)

and this goes...
               With r
                .NumberFormat = "ddd mm/dd/yy"
                With .Font
                    .Bold = True
                    .Name = "arial"
                    .Size = 10
                End With

And, truly, that is all.

Thanks guys.

You [Offensive comment removed by Forum Administrator].
That's part of my code in your CROSS POST (MrExcel)

[Codpops]

Jindon,

I replied to you in the other forum.

I said thank you for helping me. You provided 1/2 of the puzzle. In this forum, I said I 'rounded up' the code, so I could get help figuring out the other half. I didn't say I created it.

I'm now aware that most of the experts show up on multiple forums, so next time I will give you props and I'm sure they'll know who you are.

Shaun,

I haven't tried the code yet, but again I thank you, especially for the explanation, because without that, I'm just typing but not necessarily knowing why.

Have a great day, both of you.

Arlen

[Paul]

Codpops,

While we don't condone cross-posting, we understand users are going to do it. Please review the rules, and if you plan to cross-post in the future be sure to post a link to the other post.

Also, if you get a resolution to your problem on the other site be sure to include that in this forum.

Thank you,
Paul