VBA convert text date to date format

[GTretick]

I have a report that I export from my software to Excel.
For whatever reason the report has two date columns that it shows as text rather than a date format and both text layouts are different!

I want to run a macro that among other things converts these dates to Excel formatted dates but I can't get it to do so.

I've included a stripped down copy of the report output.
I can get Excel to do so using formulas within Excel. I have shown these in columns K and L marked in red font as my desired output.
(note that the formulas shown are for demonstration purposes, actual output would be as numbers, not an actual formula).

I cannot work out how to get VBA to do this for me.
Can anybody shed some light as to how to achieve this?
I consider myself as a beginner VBA user. I've developed some decent VBA routines but usually with the help of referencing examples on the internet. If you may need to tailor your response with this in mind.

Thanks in advance

 

[p45cal]

Select a single column of dates and

 Selection.TextToColumns FieldInfo:=Array(1, 5)

should do the accrued date in situ…

 

[GTretick]

I tried putting your code in as such

DatePost = Selection.TextToColumns FieldInfo:=Array(1, 5)

It showed up in Red in the coding area. What did I do wrong? (DatePost is a variable defined as Date)

Also what do you mean by doing accrued date in situ. Does this mean within the excel sheet as an actual formula?
Is this because it is not possible or for some other reason. I would prefer to make the process automatic without any manual involvement.

 

[p45cal]

Dim cll As Range, a
For Each cll In Selection.Cells
  a = Application.Trim(cll.Value)
  cll.Value = DateSerial(2000 + Mid(a, 7, 2), Application.Match(UCase(Left(a, 3)), Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"), 0), Mid(a, 4, 2))
Next cll
End Sub

Should do it for Posted Dates in situ.

re:

I tried putting your code in as such

DatePost = Selection.TextToColumns FieldInfo:=Array(1, 5)

It showed up in Red in the coding area. What did I do wrong? (DatePost is a variable defined as Date)

Also what do you mean by doing accrued date in situ. Does this mean within the excel sheet as an actual formula?
Is this because it is not possible or for some other reason. I would prefer to make the process automatic without any manual involvement.

Leave out the DatePost =
In situ means that the cells are themselves changed (no formula) (although the code could be made to put the values elsewhere on the sheet). You won't be able to assign the values directly to a variable. Do you need the dates in a vba variable?!
TextToColumns is the vba equivalent to Text To Columns in the Data Tools section of the Data tab of the ribbon.

Power Query converts both sets of dates correctly.

 

[GTretick]

I tried to incorporate the above but couldn't get it to work. As mentioned I am too new at this that there are obviously things I'm not understanding about molding this coding to my specific situation. I will have to go down another route it think.
Thanks for your response though.

 

[p45cal]

See attached which includes instructions.

 

[GTretick]

Ok , that was quite useful.
I believe I have the proper understanding of what the routine is doing.
Thank you p45cal!

 

[GTretick]

One more problem has popped up.
I am incorporating these routines into my main Excel sheet where the arrangement of the data is slightly different.
The actual data can have empty spaces between the rows of data. The "blah2" routine doesn't seem to like processing blank information. Is there a way to get it to ignore those items?

 

[p45cal]

One more problem has popped up.
I am incorporating these routines into my main Excel sheet where the arrangement of the data is slightly different.
The actual data can have empty spaces between the rows of data. The "blah2" routine doesn't seem to like processing blank information. Is there a way to get it to ignore those items?

If blah is operating on a single cell and it's blank yes there'll be an error. If that cell is one of many in a column of cells that are being processed it should be OK, but if all the cells are empty in that column yes, there's a problem. Let's have a look at your actual code using texttocolumns

 

[GTretick]

Here is a mostly intact version of my workbook. I had to sanitize some of the data for confidentiality reasons.
The code in question is right near the bottom.
I've changed some of your routine to duplicate the text date columns to different columns then change the new column to a date format.
I want to preserve the original data as is so that the routine can be run several times as new data is added. Maybe how I populated the new columns is part of my problem?

 

[p45cal]

Try this snippet instead:

For Each cll In Selection.Cells
  a = Application.Trim(cll.Value)
  If Len(a) > 7 Then
    cll.NumberFormat = "General"
    cll.Value = DateSerial(2000 + Mid(a, 7, 2), Application.Match(UCase(Left(a, 3)), Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"), 0), Mid(a, 4, 2))
  End If
Next cll

 

[GTretick]

It all works perfectly! Much appreciated!