In Excel VBA, you can apply custom formatting to date data and handle it as a pre-formatted string.
In scenarios such as creating forms, generating file names, or recording logs, it is common to convert dates into formats like “yyyy/mm/dd,” “Japanese Era names,” or “Dates with the day of the week.”
In this article, I will introduce in detail how to use the Format function to apply any format to a date.
Basic Syntax: The Format Function
Format(TargetDate, "FormatString")
By specifying a format string, you can output the date as a string in any style you like.
Usage Example: Various Format Conversions
The following macro references a date entered in cell B2 and outputs it in various formats to other cells.
Sub FormatDateExamples()
Dim srcDate As Date
' Reference the date entered in cell B2
srcDate = Range("B2").Value
' Japanese Era + Year + Month + Day (Day of the week)
' Note: ggge is specific to Japanese Era names like "Reiwa"
Range("E2").Value = Format(srcDate, "ggge年m月d日(aaaa)")
' Month-Day
Range("E3").Value = Format(srcDate, "mm-dd")
' Numeric format (ideal for file names)
Range("E4").Value = Format(srcDate, "yyyymmdd")
End Sub
Main Types of Format Specifiers
| Format String | Meaning | Example (July 20, 2025) |
| yyyy | 4-digit year | 2025 |
| mm | Month (2 digits) | 07 |
| m | Month (1 or 2 digits) | 7 |
| dd | Day (2 digits) | 20 |
| d | Day (1 or 2 digits) | 20 |
| ggge | Japanese Era (Era + Year) | Reiwa 7 (令和7年) |
| aaaa | Day of the week (Full) | Sunday |
| ddd | Day of the week (Short) | Sun |
Practical Example: Applying to Filenames and Report Headers
Using the Format function allows for the automatic generation of strings for administrative tasks.
Dim logName As String
' Generates a filename like "OrderData_20250720.csv"
logName = "OrderData_" & Format(Date, "yyyymmdd") & ".csv"
Summary | Flexible Date String Conversion with the Format Function
By utilizing the Format function when converting dates to strings in VBA, you can format them into any required style.
- Displaying Japanese Era names or days of the week is easily achieved.
- The
"yyyymmdd"format is extremely useful for generating file names. - Customizing the format string allows for flexible output according to business specifications.
Date processing is a fundamental and important part of many VBA operations. Please use this article as a reference to master formatting date notations in a way that is easy to read and handle.
