How to Format Date with VBA in Excel (4 Methods)

In the dataset, the same dates are in Columns B and C. We will format the date in Column C.

Method 1 – Using VBA to Format Dates

Steps:

Press Alt + F11, or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

In the pop-up code window, click Insert -> Module from the menu bar.

Enter the following code:

Sub DateFormat()
    Range("C5").NumberFormat = "dddd-mmmm-yyyy"
   'This will format the date to "Tuesday-January-2022"
End Sub

Press F5, or select run from the menu bar Run -> Run Sub/UserForm. (You can also click on the small Play icon in the sub-menu bar to run the macro.)

This code will format the date “11-01-22” to “Tuesday-January-2022”.

You can also convert this date format into many other formats. Just follow the code below to transform the date into your required format.

Sub FormatDate()
'Original Date is "11-01-22"
   Range("C5").NumberFormat = "dd-mm-yyy"
   'This will format the date to "11-01-2022"
   Range("C6").NumberFormat = "ddd-mm-yyy"
   'This will format the date to "Tue-11-2022"
   Range("C7").NumberFormat = "dddd-mm-yyy"
   'This will format the date to "Tuesday-11-2022"
   Range("C8").NumberFormat = "dd-mmm-yyy"
   'This will format the date to "11-Jan-2022"
   Range("C9").NumberFormat = "dd-mmmm-yyy"
   'This will format the date to "11-January-2022"
   Range("C10").NumberFormat = "dd-mm-yy"
   'This will format the date to "11-01-22"
   Range("C11").NumberFormat = "ddd mmm yyyy"
   'This will format the date to "Tue Jan 2022"
   Range("C12").NumberFormat = "dddd mmmm yyyy"
   'This will format the date to "Tuesday January 2022"
End Sub

Overview

Method 2 – Embedding VBA to Convert Date Using the FORMAT Function

Steps:

Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Enter the following code:

Sub Format_Date()
  Dim iDate As Variant
  iDate = 44572 'Excel serial number of date "11 January 2022"
  MsgBox Format(iDate, "DD-MMMM-YYYY")
End Sub

You will get “11 January 2022” in the message box.

Method 3 – Using VBA to Transform Date-Based to a Specific Part

If you want to format a specific part of the date, for instance, only the day/month/year,

Steps:

Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Enter the following code:

Sub Date_Format()
    Range("C5").NumberFormat = "mmmm"
   'This will format the date to "January"
End Sub

The “mmmm” in this code means the long form of the month name.

You can Implement this code to format and extract any specific part you want from the date.

Sub FormatDateValue()
'Original Date is "11-01-22"
   Range("C5").NumberFormat = "dd"
   'This will format the date to "11"
   Range("C6").NumberFormat = "ddd"
   'This will format the date to "Tue"
   Range("C7").NumberFormat = "dddd"
   'This will format the date to "Tuesday"
   Range("C8").NumberFormat = "m"
   'This will format the date to "1"
   Range("C9").NumberFormat = "mm"
   'This will format the date to "01"
   Range("C10").NumberFormat = "mmm"
   'This will format the date to "Jan"
   Range("C11").NumberFormat = "yy"
   'This will format the date to "22"
   Range("C12").NumberFormat = "yyyy"
   'This will format the date to "2022"
   Range("C13").NumberFormat = "dd-mm"
   'This will format the date to "11-01"
   Range("C14").NumberFormat = "mm-yyy"
   'This will format the date to "01-2022"
   Range("C15").NumberFormat = "mmm-yyy"
   'This will format the date to "Jan-2022"
   Range("C16").NumberFormat = "dd-yy"
   'This will format the date to "11-22"
   Range("C17").NumberFormat = "ddd yyyy"
   'This will format the date to "Tue 2022"
   Range("C18").NumberFormat = "dddd-yyyy"
   'This will format the date to "Tuesday-2022"
   Range("C19").NumberFormat = "dd-mmm"
   'This will format the date to "11-Jan"
   Range("C20").NumberFormat = "dddd-mmmm"
   'This will format the date to "Tuesday-January"
End Sub

Overview

Method 4 – Inserting VBA to Format Date in a Specific Worksheet

Steps:

Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Enter the following code:

Sub Format_Date()
Dim iSheet As Worksheet
Set iSheet = ThisWorkbook.Sheets("Example") 'Set the worksheet named "Example"
iSheet.Range("C5").NumberFormat = "dddd, mmmmdd, yyyy"
End Sub