Need help on converting General data to Date format

[bobbyexcel]

help Me to convert below referred cell data to date format. Need excel vba script to convert the below format of entire column and place it in next column in date format (whichever format is fine).

Tue Nov 19 20:00:28 CST 2019

 

[DanteAmor]

How about, If the text is in A2 put the following formula in B2. Put the format you want in cell B2

=(MID(A2,9,2)&MID(A2,5,3)&RIGHT(A2,4))+0

 

[Joe4]

If you just want to show it in the next column, it can be done pretty easily with just a formula (no need for VBA), i.e.
=DATEVALUE(CONCATENATE(TRIM(MID(A1,5,6)),", ",RIGHT(A1,4)))

 

[bobbyexcel]

How about, If the text is in A2 put the following formula in B2. Put the format you want in cell B2

=(MID(A2,9,2)&MID(A2,5,3)&RIGHT(A2,4))+0

This worked Perfectly perfect.. Thanks for your quick reply. Can you also help me to write the same in a VB script coz this is the part of my other work which I need to include.

 

[bobbyexcel]

If you just want to show it in the next column, it can be done pretty easily with just a formula (no need for VBA), i.e.
=DATEVALUE(CONCATENATE(TRIM(MID(A1,5,6)),", ",RIGHT(A1,4)))

Sorry it didn't work. It gives error but appreciate your reply.

 

[Joe4]

Sorry it didn't work. It gives error but appreciate your reply.

Are you located outside of US?
If so, we may have different regional settings, so DATEVALUE may behave a bit differently for you.

 

[DanteAmor]

This worked Perfectly perfect.. Thanks for your quick reply. Can you also help me to write the same in a VB script coz this is the part of my other work which I need to include.

Try this, Assuming your data from A2 and to the last cell with data from column A. The result will be in column B

Sub Converting_Date()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=(Mid(A2, 9, 2) & Mid(A2, 5, 3) & Right(A2, 4)) + 0"
    .Value = .Value
  End With
End Sub

 

[bobbyexcel]

Try this, Assuming your data from A2 and to the last cell with data from column A. The result will be in column B

Sub Converting_Date()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=(Mid(A2, 9, 2) & Mid(A2, 5, 3) & Right(A2, 4)) + 0"
    .Value = .Value
  End With
End Sub

It worked perfect.. I have added .NumberFormat = "yyyy-mmm-dd" though since it is coming in number format. Anyhow, thanks much for your help.

 

[bobbyexcel]

Are you located outside of US?
If so, we may have different regional settings, so DATEVALUE may behave a bit differently for you.

Yes, I'm in different Timezone. But it worked if I change the parameters.. Thanks to you too.

 

[Rick Rothstein]

Here is another macro that you can try (also assumes data in Column A starting on Row 2 with output to Column B)...

Sub FixDates()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 1).Value = Evaluate("IF({1},REPLACE(MID(" & .Address & ",5,99),7,13,"",""))")
    .Offset(, 1).NumberFormat = "yyyy-mmm-dd"
  End With
End Sub