How to convert date formats. A user has a project from his company and in that project, she has to enter several dates starting from month and year, example 092017, and the same date has to be converted into another format that is 30-Sep-2017. There are several dates she has to enter first in the “MONTH YEAR” format and later on the same dates have to be converted to the last day of that month I. e.
DD-MON-YEAR (E.g. 31-JAN-2018). So she has to achieve the following goals:
1. Month must be three letter format only (i.e. JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC).
2. Dash ( – ) must be used and NOT slash ( / )
3. Day must always be the last day of the month (i.e. either 28 or 29 for FEB, 30 for APR, JUN, SEP, NOV and 31 for JAN, MAR, MAY, JUL, AUG, OCT, DEC)
Watch the video below:
Watch his video on YouTube.
Here’s the complete VBA code:
Dim i As Long, lastRow As Long
lastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If Len(Cells(i, 1)) = 6 Then
Cells(i, 2) = MonthName(Mid(Cells(i, 1), 1, 2), True) & “-” & Right(Cells(i, 1), 4)
ElseIf Len(Cells(i, 1)) = 5 Then
Cells(i, 2) = MonthName(Mid(Cells(i, 1), 1, 1), True) & “-” & Right(Cells(i, 1), 4)
ElseIf Len(Cells(i, 1)) <= 4 Then
MsgBox “Date is not OK. Please check! The cell address is ” & Cells(i, 1).Address
Dim mystring As String
Cells(i, 3) = “=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,0)”
mystring = Cells(i, 3)
Cells(i, 2) = mystring
Selection.NumberFormat = “dd-mmm-yyyy”