October 7, 2017

Convert Date Formats

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)

Here’s the complete VBA code:

Sub convertDate()
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
Cells(i, 1).Select
Exit Sub
End If
Dim mystring As String
Cells(i, 3) = “=DATE(YEAR(RC[-1]),MONTH(RC[-1])+1,0)”
mystring = Cells(i, 3)
Cells(i, 3).Clear
Cells(i, 2) = mystring
Cells(i, 2).Select
Selection.NumberFormat = “dd-mmm-yyyy”
Next i

End Sub

Watch the video below:

One thought on “Convert Date Formats

  1. Hi Mr. Kumar,
    I get stack on a topic.
    Please make a training video on it. It will help me a lot.
    I have a database which I’m entering data using userform and userform has navigation button to drive one record to another.
    My problems is –
    I have a label control near to my Next & Previous navigation button. with this label I want to show “x of y” where “x” will represent my currentrow data position number and “y” will represent the total data of my sheet.
    example :
    12 of 1565, (click next)
    13 of 1565 (click next)
    14 of 1565 and so on.
    (same for click Previous)


Comments are closed.