How to format date with "." or "/" or "-" in vba?

0
votes

In VBA, I'm trying to format the date that I have in the cell C3 equal "01.30.18" to "MM/DD/YYYY".

Sometimes in the same cell I have the value like: "01.30.18" or "01/30/18" or "01/30/2018".

I need to create a formula to format the values that I'll receive in this cell to "mm/dd/yyyy" .

I tried cDate or Format function but didn't worked :

  Dim strBalancingBy As String

  Range("C3").Select

  strBalancingBy = ActiveCell.Value

   ActiveCell.Value = Format(DateValue(strBalancingBy), "mm/dd/yyyy")

Give me the wrong result = "12/30/1899".

OR 

ActiveCell.Value = CDate(strBalancingBy)

Give me the wrong result = "1/0/1900 1:30:18 AM"

Someone Can help me with this formula in vba?

I expect the result = "01/30/2018"

1
excelvba

1 Answers

1
votes

replace the . with /

With Range("C3")
    .Value = DateValue(Replace(.Value, ".", "/"))
    .NumberFormat = "mm/dd/yyyy"
End With