Converting Date stored as Text to Date Value

[evanmer]

I'm being given the format "mmm-dd-yyyy" (for ex: Jul-02-2019), however excel doesn’t interpret this text as a date value. I'd like to convert it a date value and format it as "dd/mm/yyyy" (ex: 02/07/2019); thoughts?

 

[Fluff]

Select the column with the dates, then on the data tab select text to columns > Delimited > next > clear all ceckboxes > next > select MDY (next to date) > Finish
And you should be ok

 

[evanmer]

I forgot to mention I would like to do this via VBA. I came up with the following and it worked:

FormulaR1C1 = "=DATEVALUE(LEFT(RC[-1],3)&"" ""&MID(RC[-1],5,2)&"", ""&RIGHT(RC[-1],4))"

(assumes the existing text is one cell to the right)

 

[Fluff]

Glad you figured it out & thanks for the feedback