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)

Watch the video below:


Watch his video on YouTube.

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

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

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)


Leave a Reply

Your email address will not be published. Required fields are marked *