0

I want to convert "Wed Dec 02 24:00:00 GMT 2020" to "02/12/2020" using VBA.

I created a custom function.

Function Get_date(CellRef As String)
    Dim Year_num As Integer, Month_name As String, Month_num As Integer, Date_num As Integer'''
    Year_num = Right(CellRef, 4)
    Month_name = Mid(CellRef, 5, 3)
    Month_num = Month(DateValue(Month_name&("1")))
    Date_num = Mid(CellRef, 9, 2)
    
    Get_date = DateSerial(Year_num, Month_num, Date_num)
    
End Function
Community
  • 1
  • 1
Anna
  • 29
  • 4
  • What precisely isn't working? What output do you get? – Rup Jan 18 '21 at 10:48
  • I'd be surprised if 24:00:00 was a valid time: isn't that midnight the next day? Shouldn't this be 03/12/2020 not 02/12? – Rup Jan 18 '21 at 10:49
  • Your function will work with this correction: `Month_num = Month(DateValue(Month_name & "-1"))`, if you have English months. Read [here](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) about why use `Long` instead of `Integer`. – VBasic2008 Jan 18 '21 at 12:58

1 Answers1

0

This will work:

Get-date = CDate(Right(CellRef, 4) & Mid(CellRef, 4, 7))
Gustav
  • 53,498
  • 7
  • 29
  • 55