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

Implementing VBA macro is the most effective, quickest and safest method to run any operation in Excel. In this article, we will show you how to format the date in Excel using VBA.

4 Methods to Format Date in Excel with VBA

Look at the following example. We stored the same dates in both Column B and C so that when we format the date in Column C, you will know from the B Column in which format the date was before.

1. VBA to Format Date from One Type to Another in Excel

First let’s know how to format the date from Cell C5 in our given dataset with VBA to “Tuesday-January-2022”.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

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

• Copy the following code and paste it into the code window.

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

Your code is now ready to run.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just 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 format of date into many other formats. Just follow the code below to transform the date into the format you require.

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

Read more: Now and Format Functions in Excel VBA

2. Embed VBA to Convert Date with FORMAT Function

Excel has its own serial number regarding individual dates. If you want to know the serial number of any

specific date, you have to apply the DATEVALUE function.

Suppose, you want to know the serial number of the date “11 January 2022”, then you need to write the formula as,

Excel will give you the serial number 44572 of this date.

Now we will transform this number into its relevant date format.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

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

Read more: How to Use VBA DateValue Function in Excel

3. VBA to Transform Date Based on a Specific Part in Excel

Suppose, you want to format a specific part of the date, for instance, only the day/month/year, then write the code as,

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

Your code is now ready to run.

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

As the date is “11 January 2022” so this piece of code will return “January”.

You can Implement this code to format and extract any specific part that 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

4. Insert VBA to Format Date in a Specific Worksheet in Excel

If you want to format a date based on a specific worksheet, then first you need to set the worksheet name in the code, then format the date according to the type you require.

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

Look at the 3rd line of the code where first we set the “Example” worksheet then format the date of that certain Excel sheet.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to format the date in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Further Readings

• Use the Day Function in Excel VBA
• How to Get the Day of Week Using VBA
• Convert Date from String Using VBA
• How to Use the VBA DateAdd Function in Excel