CODEWITHSUNDEEP
excelformatdate-formatvba
thuglifefligh thug
thuglifefligh thug

Reputation: 23

VBA Excel date yyyy/mmm/dd to yyyy/mm/dd

I'm writing small VBA to manage and convert some date from external tables and I'm stuck on some strange date format like: yyyy/mm/dd What I really need is to convert date from a1 and paste it to b2

As a tamplate I will use to columns A1 and B1: A1= date to be converted B1= Paste Destinaton of converted dat

What I tested for now is:

  1. Formula to convert (with created dictionary) - Cannot use it in case of many dates to convert
  2. VBA for date convert - Cannot see my date from A1 as a proper date and do nothing
  3. Format this date in excel build-in function "formating" - Do nothing
  4. Also function "text-to-column" was tested - Still do nothing

It is looking like I'm just stuck on something really simple but it is really an hard to move forward for now so this is why I'm asking for help.

Thanks for any ideas.

Upvotes: 2

Views: 656

Answers (1)

Axel Richter
Axel Richter

Reputation: 61985

I would use the following formula in B1:

=DATE(LEFT(A1,4),MATCH(MID(A1,6,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dct"},0),RIGHT(A1,2))

What looks strange is the abbreviation "Dct" for December. So maybe you have to change the array Literal {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dct"} according to your language.

Using VBA following Function could be used:

Function convert_YYYY_MMM_DD_Text2Date(sText As String) As Variant
 Dim iYear As Integer
 Dim iMonth As Integer
 Dim sMonth As String
 Dim iDay As Integer
 Dim aMonths As Variant
 Dim bFound As Boolean

 aMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dct")

 On Error GoTo errHandler
 iYear = CInt(Left(sText, 4))
 sMonth = Mid(sText, 6, 3)
 iDay = CInt(Right(sText, 2))

 For iMonth = 0 To UBound(aMonths)
  If aMonths(iMonth) = sMonth Then
   bFound = True
   Exit For
  End If
 Next

 If bFound Then
  convert_YYYY_MMM_DD_Text2Date = DateSerial(iYear, iMonth + 1, iDay)
 Else
  GoTo errHandler
 End If

 On Error GoTo 0

 Exit Function

errHandler:
 convert_YYYY_MMM_DD_Text2Date = "conversion not possible"

End Function

Upvotes: 2

Related Questions