[VBA Beginner] How to Apply Specific Formats to Dates and Retrieve as Strings | Using the Format Function

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

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.