Excel VBA Weekday: Syntax, Return Values, and Examples

VBA & macros · Updated November 18, 2026

In Excel VBA, use Weekday(date, firstdayofweek) to get a date’s position in the week as a number from 1 to 7. For example, Weekday(someDate, vbMonday) makes Monday 1 and Sunday 7; if you omit the second argument, VBA uses Sunday as day 1.

What the VBA Weekday function does

Weekday answers a specific calendar question: which day of the week is this date, using the week start you choose? It does not return the day of the month, such as the 3 in March 3, and it does not calculate how many days have passed between two dates. Its result is an integer from 1 through 7, with the meaning of each number determined by the first-day setting.

The function is useful when a macro needs to branch on a weekday, group records by day, or apply a rule such as treating Saturday and Sunday as the weekend. The date can be a VBA Date variable, a date returned from a worksheet cell, or another valid date expression. A valid Excel date is stored as a date value even though the worksheet displays it in a human-readable format.

One detail often causes confusion: the default is not necessarily the first day preferred by a particular user. If you do not supply firstdayofweek, VBA uses vbSunday. Specify the argument when your code relies on Monday-first numbering or when its results must be consistent on different computers.

Syntax and return values

The VBA syntax is Weekday(date, [firstdayofweek]). The square brackets indicate that the second argument is optional; do not type the brackets into your code. The first argument must resolve to a valid date. The second argument is a VBA constant that sets which weekday receives the number 1.

With the default Sunday-first setting, Sunday returns 1, Monday 2, and Saturday 7. With vbMonday, Monday returns 1, Tuesday 2, and Sunday 7. This numbering is different from the ISO weekday convention only if you choose a different arrangement: ISO numbering also makes Monday 1 and Sunday 7, so vbMonday gives the same weekday numbers.

For example, March 3, 2025 was a Monday. In VBA, Weekday(DateSerial(2025, 3, 3), vbMonday) returns 1, while Weekday(DateSerial(2025, 3, 3)) returns 2 because the omitted argument uses Sunday-first numbering. DateSerial builds the date from year, month, and day components, avoiding ambiguous text such as 03/04/2025.

Choosing the first day of the week

The optional argument accepts these built-in VBA constants. Choose a named constant rather than a bare number so the code communicates its intention:

  • vbUseSystem: use the first day specified by the computer’s regional settings.
  • vbSunday: Sunday is 1. This is also the default when the argument is omitted.
  • vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, or vbSaturday: the named day is 1, and the remaining days follow in calendar order.

Use a fixed constant such as vbMonday when a workbook’s logic must return the same answer regardless of who runs it. vbUseSystem can suit a locally tailored report, but it makes results dependent on each computer’s regional settings. That dependency can surprise someone sharing the workbook, especially if formulas or reports assume a particular numbering scheme.

For weekend detection, the selected week start matters. With Monday-first numbering, Saturday and Sunday are 6 and 7. With Sunday-first numbering, they are 7 and 1. A test that checks only whether the result equals 6 or 7 will therefore be wrong if the code sometimes uses Sunday-first numbering.

Use Weekday in a VBA macro

This macro reads a date from cell A2 on Sheet1 and writes its Monday-first weekday number to B2. It checks for an error, an empty cell, or a value that VBA does not recognize as a date before calling Weekday. The example assumes the worksheet exists and that A2 contains a real date or an unambiguous date value.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a macro can change or overwrite workbook data. Save a macro-enabled workbook as .xlsm if you need to retain its VBA code.

Sub WriteWeekdayNumber()
    Dim ws As Worksheet
    Dim sourceValue As Variant
    Dim dayNumber As Long

    ' Read the date from the specified worksheet.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    sourceValue = ws.Range("A2").Value

    ' Reject errors, blanks, and values that are not dates.
    If IsError(sourceValue) Then
        MsgBox "Cell A2 contains an error."
        Exit Sub
    End If
    If Len(Trim$(CStr(sourceValue))) = 0 Then
        MsgBox "Cell A2 is blank."
        Exit Sub
    End If
    If Not IsDate(sourceValue) Then
        MsgBox "Cell A2 does not contain a valid date."
        Exit Sub
    End If

    ' Monday is 1; Sunday is 7.
    dayNumber = Weekday(CDate(sourceValue), vbMonday)
    ws.Range("B2").Value = dayNumber
End Sub

To adapt the macro, change the input or output cell references, or replace vbMonday with another first-day constant. For a column of dates, the same check-and-calculate pattern can be applied row by row. It is sensible to decide what the macro should do with blanks or invalid values before processing a large range, rather than letting one bad cell stop the whole run unexpectedly.

Return a weekday name instead of a number

Weekday returns a number, but VBA also has WeekdayName, which returns the corresponding day name. It accepts the weekday number, an optional abbreviation setting, and an optional first-day setting. Keep the settings aligned: if you calculate a number with vbMonday, pass vbMonday to WeekdayName as well.

For instance, WeekdayName(Weekday(DateSerial(2025, 3, 3), vbMonday), False, vbMonday) returns Monday in the language used by the computer’s regional settings. Set the abbreviation argument to True for a short form such as Mon, although the exact localized text can vary. If a report needs a particular language regardless of computer settings, use an explicit mapping or a carefully designed worksheet lookup instead of assuming the operating system’s language.

VBA Weekday versus the Excel worksheet function

Excel also has a worksheet function named WEEKDAY. It performs a similar calculation in a cell, but it is not VBA code and its second argument is a numeric return-type code rather than a VBA constant. Choose the worksheet function when the answer should update automatically in the grid; use VBA when a macro needs to make a decision or write results as part of a procedure.

With a real Excel date in A2 representing March 3, 2025, enter this formula in B2 to number Monday as 1 and Sunday as 7: =WEEKDAY(A2,2). It returns 1 for that example. In a macro, the comparable setting is Weekday(dateValue, vbMonday). The worksheet function also has other return-type options, so check its numbering convention before copying a formula’s logic into VBA.

For a simple display-only weekday name, cell formatting may be enough: a date format such as dddd displays the full weekday name, while ddd displays an abbreviated name. This changes how the date appears, not the underlying date value. Formatting is often preferable to a macro if the goal is only to show a weekday beside a date.

Common date and numbering problems

  • Unexpected result by one: Check whether the code omitted the optional argument. The default is Sunday-first, so Monday is 2 rather than 1.
  • Weekend logic fails: Verify that the test uses the same first-day convention as the calculation. Saturday and Sunday do not have the same numbers under every convention.
  • A date-looking cell is rejected: The cell may contain text that is not recognized as a date, or a formula error. Prefer actual date values and check the cell’s value rather than relying on how it looks.
  • Dates are interpreted differently: Text such as 04/05/2025 is ambiguous between month/day and day/month conventions. Use real date cells or construct dates with DateSerial(year, month, day) when writing VBA.
  • The result differs between computers: This can happen if code uses vbUseSystem, or if text dates are interpreted according to different regional settings. Use a fixed first-day constant and avoid ambiguous date text.

Another misconception is that changing a cell’s number format changes what Weekday returns. Formatting affects display, not the calendar date supplied to the function. If a cell displays a date with a weekday name but the calculation is unexpected, inspect the actual stored date and the chosen first-day argument.

Where Weekday is useful

Weekday is commonly used in scheduling and reporting rules. A macro can skip weekend entries, label records by day, or apply a different action on a specified weekday. For example, a Monday-first result of 1 can identify Monday, but that rule should be paired with an explicit vbMonday argument so it remains clear to future readers of the code.

For business-day calculations, Weekday is only one part of the problem. It can identify weekend days, but it does not know which dates are public holidays, company closures, or special working days. A process that needs accurate working-day counts must also consult a holiday list or use an appropriate workday calculation method. Do not treat every weekday from Monday through Friday as a working day in every calendar or organization.

Frequently Asked Questions

What does Weekday return in VBA?
It returns a number from 1 to 7. Which day gets 1 depends on the optional first-day argument; if that argument is omitted, Sunday is 1.

How do I make Monday equal 1?
Pass vbMonday as the second argument, as in Weekday(myDate, vbMonday). The result is Monday 1 through Sunday 7.

Does VBA Weekday return the day name?
No. Weekday returns a number. Use WeekdayName to get a localized weekday name, or use the worksheet’s date formatting to display one.

What is the difference between VBA Weekday and Excel WEEKDAY?
Both number a date’s weekday, but one is a VBA function used in code and the other is a worksheet function used in a cell. VBA takes constants such as vbMonday; the worksheet function uses numeric return-type codes.

Can I run a Weekday VBA macro in Excel for the web?
No. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros. Use the Excel desktop app for Windows or Mac to work with VBA; some Excel features and object-model behavior can differ between platforms.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.