15 min read

VBA Format Date: How to Format Dates in Excel (2026 Guide)

Udemy Team

Article Summary

Formatting dates in VBA means using the Format function to convert date values into any display pattern you need. This article covers VBA Format Date syntax, predefined and custom format options, supported characters, and practical code examples.

Key takeaways

  • The VBA Format function converts date and time values into formatted text.
  • A VBA Date variable stores a date/time value, not its display format.
  • Predefined formats such as “Long Date” and “Short Date” depend on regional or application settings.
  • Custom format codes such as dd, mmm, yyyy, and hh control how dates and times appear.
  • The m and mm codes represent minutes rather than months when immediately preceded by h or hh. Using n or nn for minutes avoids confusion.
  • DateSerial helps construct dates from year, month, and day components without relying on ambiguous date strings.

Formatting dates in Excel VBA helps you create readable reports, generate consistent file names, display timestamps, and convert dates between different layouts.

The easiest way to format a date in VBA is with the built-in Format function:

Format(Date, "dd/mm/yyyy")

This converts today’s date into a text string using the day/month/year pattern. The actual date separator may vary depending on your computer’s regional settings.

Whether you need dd/mm/yyyy, yyyy-mm-dd, or a date with a written month name, this tutorial explains VBA date formatting syntax, common format codes, and practical examples you can use in Excel.

How to assign a date to a variable in VBA

Before formatting a date, you need a date value.

VBA includes a built-in Date data type for storing dates and times. You can assign a date using a date literal or the DateSerial function.

For example:

Sub AssignDateExample()

    Dim myDate As Date

    myDate = #3/24/2026#

    Debug.Print myDate

End Sub

The hash symbols (#) identify the value as a date literal.

Important: Numeric VBA date literals commonly use the U.S. month/day/year convention. For example, #3/24/2026# represents March 24, 2026. Don’t assume that a day-first literal such as #24/3/2026# will be interpreted as intended. VBA’s recognized date-literal formats can also depend on the code’s locale, so avoid ambiguous numeric literals.

For greater clarity, especially when writing code for people in different regions, use DateSerial:

Sub DateSerialExample()

    Dim myDate As Date

    myDate = DateSerial(2026, 3, 24)

    Debug.Print Format(myDate, "yyyy-mm-dd")

End Sub

Output:

2026-03-24

The DateSerial function accepts three arguments in this order:

  1. Year
  2. Month
  3. Day

The main distinction is that a Date variable stores the underlying date/time value, while Format converts that value into text.

For example:

Sub StoreFormattedDate()

    Dim myDate As Date

    Dim formattedDate As String

    myDate = DateSerial(2026, 3, 24)

    formattedDate = Format(myDate, "dd mmm yyyy")

    Debug.Print formattedDate

End Sub

On an English-language system, the output is:

24 Mar 2026

VBA Format Date syntax

The VBA Format function uses the following syntax:

Format(Expression, [Format], [FirstDayOfWeek], [FirstWeekOfYear])

The function converts the supplied expression into formatted text. You can store its result in a String variable.

Technically, Format returns a Variant containing a string. VBA also provides Format$, which returns a String directly.

Format function parameters

ParameterDescription
ExpressionRequired. The value you want to format.
FormatOptional. A predefined format name or custom format pattern.
FirstDayOfWeekOptional. Specifies the first day of the week for week-related formatting.
FirstWeekOfYearOptional. Specifies how the first week of the year is determined.

In most date-formatting tasks, you only need the first two parameters.

For example:

Sub BasicFormatExample()

    Dim result As String

    result = Format(DateSerial(2026, 3, 24), "yyyy-mm-dd")

    Debug.Print result

End Sub

Output:

2026-03-24

Predefined VBA date and time formats

VBA provides named formats that you can use without specifying individual date or time characters.

FormatDescription
General DateDisplays the date, time, or both, depending on the value and system settings.
Long DateUses the system’s long date format.
Medium DateUses the medium date format appropriate for the language version of the host application.
Short DateUses the system’s short date format.
Long TimeUses the system’s long time format, including seconds.
Medium TimeDisplays hours and minutes using a 12-hour clock with AM/PM.
Short TimeDisplays time using a 24-hour clock.

Here is an example:

Sub NamedDateFormats()

    Dim myDate As Date

    myDate = DateSerial(2026, 3, 24)

    Debug.Print Format(myDate, "Long Date")

    Debug.Print Format(myDate, "Short Date")

    Debug.Print Format(myDate, "Medium Date")

End Sub

On an English/U.S. system, possible results include:

Tuesday, March 24, 2026

3/24/2026

24-Mar-26

These are illustrative outputs. Your results may differ depending on regional and application language settings.

FirstDayOfWeek values

The optional FirstDayOfWeek parameter controls which day is treated as the beginning of the week for week-related formatting.

ConstantValueMeaning
vbUseSystem0Uses the system’s first-day-of-week setting
vbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday

Microsoft’s VBA Format documentation uses vbUseSystem for value 0.

FirstWeekOfYear values

The optional FirstWeekOfYear parameter determines how week 1 is defined.

ConstantValueMeaning
vbUseSystem0Uses the system setting
vbFirstJan11The week containing January 1 (default)
vbFirstFourDays2The first week with at least four days in the year
vbFirstFullWeek3The first full week of the year

These two optional arguments are mainly relevant when using week-related formatting codes such as w and ww. They generally aren’t needed for everyday patterns such as dd/mm/yyyy.

Custom VBA date format codes

Custom date formatting lets you control how individual parts of a date or time are displayed.

Date formatting characters

CodeDescriptionExample
dDay without a leading zero4
ddDay with a leading zero04
dddAbbreviated weekdayTue
ddddFull weekdayTuesday
mMonth without a leading zero3
mmMonth with a leading zero03
mmmAbbreviated month nameMar
mmmmFull month nameMarch
yyLast two digits of the year26
yyyyFour-digit year for a 2026 date2026

These examples illustrate the codes individually and do not all refer to the same date. Month and weekday names also depend on the language settings.

Other supported date codes include q for the quarter, y for the day of the year, and ww for the week of the year.

Time formatting characters

CodeDescriptionExample
hHour without a leading zero9
hhHour with a leading zero09
nMinute without a leading zero4
nnMinute with a leading zero04
sSecond without a leading zero5
ssSecond with a leading zero05
AM/PMDisplays AM or PM with a 12-hour clockPM

Without an AM/PM designator, h and hh use a 24-hour clock. With an AM/PM designator, they use a 12-hour clock.

One important rule: In VBA, m and mm represent minutes instead of months when immediately preceded by h or hh.

To avoid confusion, use n or nn for minutes:

Sub TimeFormatExample()

    Dim exampleTime As Date

    exampleTime = TimeSerial(15, 4, 23)

    Debug.Print Format(exampleTime, "hh:nn:ss")

End Sub

Expected output on a system using colons as time separators:

15:04:23

How regional settings affect date separators

The / and : characters in VBA format expressions are special date and time separator symbols.

Their actual output can depend on regional settings.

For example:

Format(DateSerial(2026, 3, 24), "dd/mm/yyyy")

might use a different date separator from the one shown in the format expression.

If you require a literal slash, you can escape it in the format string:

Format(DateSerial(2026, 3, 24), "dd\/mm\/yyyy")

Output:

24/03/2026

This is especially useful when exporting data that must follow a particular format.

VBA Format Date examples

Let’s use a fixed date and time so the examples produce consistent results.

The following macro uses March 24, 2026, at 3:04:23 PM.

Sub FormatDateExamples()

    Dim exampleDate As Date

    exampleDate = DateSerial(2026, 3, 24) _

                + TimeSerial(15, 4, 23)

    Debug.Print Format(exampleDate, "m")

    Debug.Print Format(exampleDate, "mm")

    Debug.Print Format(exampleDate, "mmm")

    Debug.Print Format(exampleDate, "mmmm")

    Debug.Print Format(exampleDate, "dd\/mm\/yyyy")

    Debug.Print Format(exampleDate, "yyyy-mm-dd")

    Debug.Print Format(exampleDate, "dddd, mmmm d, yyyy")

    Debug.Print Format(exampleDate, "hh:nn:ss")

    Debug.Print Format(exampleDate, "hh:nn:ss AM/PM")

End Sub

Expected output in an English/U.S. environment using colons as time separators:

3

03

Mar

March

24/03/2026

2026-03-24

Tuesday, March 24, 2026

15:04:23

03:04:23 PM

To run the example in Excel desktop:

  1. Open the Visual Basic Editor (Alt + F11 on Windows).
  2. Select Insert > Module.
  3. Paste the code into the new module.
  4. Open the Immediate window (Ctrl + G).
  5. Run FormatDateExamples using F5.

The Debug.Print statements display their results in the Immediate window.

These macros are designed for desktop Excel with VBA support. Excel for the web cannot execute VBA macros.

How to write a formatted date into an Excel cell

You can use VBA to place a formatted date into an Excel worksheet.

For example:

Sub WriteFormattedDate()

    Dim myDate As Date

    myDate = DateSerial(2026, 3, 24)

    With ThisWorkbook.Worksheets("Sheet1").Range("A1")

        .NumberFormat = "@"

        .Value = Format(myDate, "yyyy-mm-dd")

    End With

End Sub

This writes the text 2026-03-24 into cell A1 of the worksheet named Sheet1.

Setting .NumberFormat = “@” first tells Excel to treat the cell as text.

Using ThisWorkbook.Worksheets(“Sheet1”) also specifies exactly where the value should be written instead of relying on whichever worksheet happens to be active.

Note: ThisWorkbook is the workbook containing the VBA code, and “Sheet1” must match an existing worksheet name. Change that name if your worksheet is called something else.

Keep the value as a real Excel date

If you want Excel to retain the date for calculations, sorting, or date arithmetic, write the underlying Date value to the cell and apply a number format:

Sub WriteRealExcelDate()

    Dim myDate As Date

    myDate = DateSerial(2026, 3, 24)

    With ThisWorkbook.Worksheets("Sheet1").Range("A1")

        .Value = myDate

        .NumberFormat = "yyyy-mm-dd"

    End With

End Sub

This preserves the underlying Excel date value while displaying it as 2026-03-24.

The distinction is important:

  • Use Format to produce formatted text.
  • Use Range.NumberFormat to change how an Excel cell displays its underlying value.

Also note that VBA Format and Excel’s NumberFormat do not use identical formatting rules for every code.

How to format a date as YYYYMMDD in VBA

The yyyymmdd format is useful for file names, report labels, and data exports.

Because it uses a fixed-width year-month-day sequence, strings for valid dates can be sorted chronologically using normal text sorting.

For example:

Sub FileDateFormat()

    Dim myDate As Date

    Dim dateStamp As String

    myDate = DateSerial(2026, 3, 24)

    dateStamp = Format(myDate, "yyyymmdd")

    Debug.Print dateStamp

End Sub

Output:

20260324

Create a date and time stamp

You can add hours, minutes, and seconds to generate more descriptive file names:

Sub DateTimeStamp()

    Dim myDateTime As Date

    Dim fileStamp As String

    myDateTime = DateSerial(2026, 3, 24) _

               + TimeSerial(15, 4, 23)

    fileStamp = Format(myDateTime, "yyyymmdd_hhnnss")

    Debug.Print fileStamp

End Sub

Output:

20260324_150423

Here, nn represents minutes and ss represents seconds.

Example: Format a report file name

Sub ReportFileName()

    Dim reportDate As Date

    Dim fileName As String

    reportDate = DateSerial(2026, 3, 24)

    fileName = "SalesReport_" & _

               Format(reportDate, "yyyymmdd") & _

               ".xlsx"

    Debug.Print fileName

End Sub

Output:

SalesReport_20260324.xlsx

The example constructs a file name. It does not save a workbook; saving would require additional code.

Because Format produces text, a String variable is appropriate for storing the file-name component.

How to convert DD/MM/YYYY to YYYY/MM/DD in VBA

Dates stored as text can be difficult to interpret when they come from different countries or applications.

For example, 03/04/2026 could mean March 4 or April 3.

The CDate function interprets date strings using applicable locale settings. If the source uses a different date convention, the result may not be what you intended.

A clearer approach is to extract the year, month, and day from a known input layout and construct the date with DateSerial.

Convert a fixed-width date string

This example assumes the source text follows DD/MM/YYYY:

Sub ConvertDateFormat()

    Dim txt As String

    Dim myDate As Date

    txt = "24/03/2026"

    myDate = DateSerial( _

        CInt(Right(txt, 4)), _

        CInt(Mid(txt, 4, 2)), _

        CInt(Left(txt, 2)) _

    )

    Debug.Print Format(myDate, "yyyy\/mm\/dd")

End Sub

Output:

2026/03/24

This approach is more predictable than passing an ambiguous string directly to CDate.

However, it assumes the input is valid and follows the expected layout.

Validate imported date strings

For production applications, you should check that the input has the correct structure and represents a real date.

This is important because DateSerial can normalize certain out-of-range values instead of rejecting them.

For example, passing February 30, 2026, to DateSerial produces a date in March rather than an error.

The following function accepts a fixed-width Gregorian DD/MM/YYYY date and raises an error when the value is invalid.

Function ConvertDDMMYYYY(ByVal txt As String) As String

    Dim yyyy As Integer

    Dim mm As Integer

    Dim dd As Integer

    Dim resultDate As Date

    If Not txt Like "##/##/####" Then

        Err.Raise 5, , "Expected DD/MM/YYYY."

    End If

    dd = CInt(Left$(txt, 2))

    mm = CInt(Mid$(txt, 4, 2))

    yyyy = CInt(Right$(txt, 4))

    ' Use four-digit years from 0100 to 9999.

    If yyyy < 100 Or mm < 1 Or mm > 12 _

       Or dd < 1 Or dd > 31 Then

        Err.Raise 5, , "Invalid date."

    End If

    resultDate = DateSerial(yyyy, mm, dd)

    If Year(resultDate) <> yyyy _

       Or Month(resultDate) <> mm _

       Or Day(resultDate) <> dd Then

        Err.Raise 5, , "Invalid date."

    End If

    ConvertDDMMYYYY = Format(resultDate, "yyyy\/mm\/dd")

End Function

To test the function, put it into a standard VBA module and run this separate procedure:

Sub TestDateConversion()

    Debug.Print ConvertDDMMYYYY("24/03/2026")

End Sub

Output:

2026/03/24

The function will reject invalid Gregorian dates such as 31/04/2026 and 29/02/2026.

This example assumes VBA’s Gregorian Calendar setting. VBA also supports Hijri calendar behavior, so applications that work with different calendar systems require additional handling and testing.

Why is my VBA date format not working?

If VBA displays an unexpected date, the cause is often a formatting, regional-setting, or data-type issue.

Here are six common problems and how to resolve them.

1. You’re storing formatted text instead of a date

The Format function returns a formatted string.

For example:

Dim formattedDate As String

formattedDate = Format(DateSerial(2026, 3, 24), _

                       "dd\/mm\/yyyy")

This is appropriate for messages, labels, and exports.

However, if you want Excel to calculate with the date, retain the original Date value instead.

2. You’re confusing months and minutes

The formatting codes m and mm generally refer to months. However, when immediately preceded by h or hh, they refer to minutes.

Use nn to make the distinction explicit:

Debug.Print Format(TimeSerial(15, 4, 23), "hhnnss")

Output:

150423

3. Regional settings are changing the output

Predefined formats such as “Short Date” can produce different results on different computers.

For example:

Debug.Print Format(DateSerial(2026, 3, 24), "Short Date")

The result depends on the system’s short-date convention.

If you require a particular layout, use a suitable custom format. Escape locale-sensitive separators when they must appear literally.

4. Your date string is being interpreted incorrectly

An input such as 04/03/2026 is ambiguous without knowing its date convention.

When reading dates from another system, document the expected input format and parse the date components explicitly.

For fixed-width DD/MM/YYYY input, the conversion approach demonstrated earlier is preferable to relying on regional interpretation.

5. There’s a naming conflict with Format

If your project defines a procedure or variable named Format, it may interfere with the built-in function.

Rename the conflicting identifier or explicitly refer to the VBA function:

VBA.Format(DateSerial(2026, 3, 24), "yyyy-mm-dd")

6. You’re using Visual Basic .NET code in Excel VBA

VBA and Visual Basic .NET share some syntax, but they’re separate programming environments.

A Windows Forms event handler using Handles Button1.Click, for example, isn’t valid inside a standard Excel VBA module.

For Excel, use VBA procedures:

Sub ShowFormattedDate()

    MsgBox Format(DateSerial(2026, 3, 24), _

                  "dd mmm yyyy")

End Sub

On an English-language system, this displays:

24 Mar 2026

Formatting dates vs. calculating dates in VBA

The Format function changes how a value is represented as text. It does not alter the underlying date.

To perform date calculations, VBA provides functions such as:

  • DateAdd, which adds a specified time interval to a date.
  • DateDiff, which returns the number of specified intervals between dates.
  • DateSerial, which constructs a date from components.
  • Year, Month, and Day, which extract date components.

For example, to add seven days to March 24, 2026:

Sub AddDaysExample()

    Dim startDate As Date

    Dim endDate As Date

    startDate = DateSerial(2026, 3, 24)

    endDate = DateAdd("d", 7, startDate)

    Debug.Print Format(endDate, "yyyy-mm-dd")

End Sub

Output:

2026-03-31

In this example, DateAdd performs the date calculation, while Format controls the displayed result.

Keeping calculations and formatting separate makes VBA code easier to understand and maintain.

Ready to Take Your Excel VBA Skills Further?

Learning to format dates in VBA is just the beginning. If you want to automate repetitive Excel tasks, write your own macros, and build more efficient workflows, consider enrolling in the following course on Udemy. Designed for beginners, this course covers VBA programming fundamentals, the Excel Object Model, variables, procedures, and practical automation techniques.

FAQ

How do I format a date in VBA as MM/YYYY?

Use:

Format(DateSerial(2026, 3, 24), “mm\/yyyy”)

Output:

03/2026

For a written month name, use:

Format(DateSerial(2026, 3, 24), “mmm yyyy”)

On an English-language system, this returns Mar 2026.

How do I write today’s date in VBA?

Use the Date function:

ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value = Date

To write the current date and time, use Now:

ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value = Now

Replace “Sheet1” with the name of your worksheet.

To display today’s date as formatted text:

Debug.Print Format(Date, “dd mmm yyyy”)

The result depends on the current system date and language settings.

How do I format a date in VBA as YYYY-MM-DD?

Use:

Format(DateSerial(2026, 3, 24), “yyyy-mm-dd”)

Output:

2026-03-24

This year-first layout is useful for reports, filenames, and date stamps.

Does VBA Format change the original date value?

No. Format creates a formatted text representation of a value without modifying the original. To control how a real date appears in an Excel cell, use the cell’s NumberFormat property.

What is the difference between Format and CDate?

Format converts a value into formatted text. CDate converts an expression into a VBA Date value according to applicable conversion and regional interpretation rules. Use Format when you need a formatted string. Use CDate when conversion is appropriate and the input is unambiguous.

What is the difference between Format and Format$?

Both can apply a format pattern to a value. The Format function returns a Variant containing a formatted string, while Format$ returns a String directly. For most beginner VBA tutorials, Format is sufficient.

What are the optional parameters of the Format function?

The three optional arguments are Format, FirstDayOfWeek, and FirstWeekOfYear. The only required argument is the expression. When omitted, the default first day of the week is Sunday, and the default first week is the week containing January 1.

Share article: