Excel / VBA Date Function with 7 Formatting Examples
Explore the power of the VBA Date Function with our comprehensive guide at Excel-Learn.com. Learn how to leverage this essential tool for date and time manipulation in Excel programming.
Date function in VBA
- VBA Date function returns the current date of the system.
- This is a variant type.
- It can be used with the format function to get the date in the desired format.
- We will show this in examples.
An example of using the Date function
We will simply display the Date in the message box to get the current date:
Sub Date_ex() MsgBox (Date) End Sub
Output:
You can see, it displays the current date of the system – in the set format.
Format date in day, full month name, and two-digit year format
- Using Date() alone might not be as meaningful as you might want to display in your Excel sheet.
- Now let us have a look at formatting date by using the Date function with Format().
- So first, we will show you using a Date type variable and assigning it the Date() function and using it in the Format() function.
Code:
Sub Date_ex() Dim Dt As Date Dt = Date MsgBox (Format(Dt, "dd mmmm yy")) End Sub
Result:
To get the date with the full month name, use ‘m’ four times.
With a full year like 2021, 2022 etc.
Just adding two more ‘yy’ to the above example and it will get us a year in four digits.
Sub Date_ex() Dim Dt As Date Dt = Date 'Four digit year MsgBox (Format(Dt, "dd mmmm yyyy")) End Sub
Result:
Date in yyyymmdd format
Sub Date_ex() Dim Dt As Date Dt = Date 'Four digit year MsgBox (Format(Dt, "yyyymmdd")) End Sub
Result
Day, Short Month name with four digit year example
Sub Date_ex() Dim Dt As Date Dt = Date 'Short Month Name MsgBox (Format(Dt, "dd mmm, yyyy")) End Sub
Result:
Short Day name, Short Month, and Full year
Use ‘ddd’ for the short day name as follows:
Sub Date_ex() Dim Dt As Date Dt = Date 'Short Day Name, Short Month MsgBox (Format(Dt, "ddd, dd mmm, yyyy")) End Sub
Full Day, Short Mon and four-digit year formatting
By using Date() function with Format() function, we will display the date in Full day name, short month, and four-digit year:
Sub Date_ex() Dim Dt As Date Dt = Date 'Full Day Name, Short Month MsgBox (Format(Dt, "ddd, dd mmm, yyyy")) End Sub
