VBA Weekday & DatePart in Excel — Extract Date Parts, and Why Monday Isn't 1

TL;DR — Year(d), Month(d), Day(d), Hour(t), Minute(t), Second(t) each return one integer out of a date — simple and unambiguous. Weekday(d) is the trap: it returns 1–7, but the numbering depends on a second argument that defaults to vbSunday (1), so by default Monday is 2, not 1. Pass vbMonday (or compare against the vb* constants) and weekend logic stops being quietly wrong.

Sub PartsDemo()
    Dim d As Date: d = #2026-08-26#        ' a Wednesday
    Debug.Print Year(d), Month(d), Day(d)  ' 2026  8  26
    Debug.Print Weekday(d)                 ' 4  — default Sunday=1, so Wed=4
    Debug.Print Weekday(d, vbMonday)       ' 3  — Monday=1, so Wed=3
    Debug.Print DatePart("ww", d)          ' 35 — week number (no dedicated function)
End Sub

Extracting parts is the reverse of building a date from parts: you have the Double, and you want the year, the month, the day of the week. Most of the functions are exactly as boring as they should be — Year, Month, Day just read the number. Weekday is the one that has ended more macros than it should, entirely because of a default nobody remembers.

What you'll learn

The simple extractors — Year, Month, Day, Hour, Minute, Second — one integer each
Why Weekday returns 2 for Monday by default, and the one argument that fixes it
How to write weekend and weekday tests that are not secretly wrong
DatePart — the general extractor that reaches the week number and quarter
Why DatePart("ww", …) week numbers can be off by one at the year boundary (ISO 8601)
WeekdayName and MonthName return localized names — use for display, not parsing

The simple extractors: one integer each

Year, Month, and Day split a date into its calendar components; Hour, Minute, and Second split the time fraction. Each returns a plain Integer, and there is nothing to trip over:

Dim d As Date: d = Now                 ' 2026-08-26 14:30:07
Debug.Print Year(d)      ' 2026
Debug.Print Month(d)     ' 8   (a number 1–12, not the name)
Debug.Print Day(d)       ' 26
Debug.Print Hour(d)      ' 14  (24-hour)
Debug.Print Minute(d)    ' 30
Debug.Print Second(d)    ' 7

Month returns 8, not "August" — it is a number. These are the building blocks you feed back into DateSerial to construct related dates (first of the month, same day next year, and so on). No surprises here — which is exactly why Weekday catches people off guard.

Why Weekday returns 2 for Monday

Weekday(date, [firstDayOfWeek]) returns a number from 1 to 7 — but which day is 1 is set by the second argument, and when you omit it VBA uses vbSunday, so Sunday = 1 and Monday = 2:

Weekday(#2026-08-24#)             ' 2  — that Monday, because Sunday=1 by default
Weekday(#2026-08-24#, vbMonday)   ' 1  — now Monday=1 … Sunday=7

This is the headline bug of the whole family. Code like If Weekday(d) = 1 Then ' Monday is wrong — 1 is Sunday in a default call — and code like If Weekday(d) = 6 Or Weekday(d) = 7 Then ' weekend depends entirely on an assumption about the first day that is invisible at the call site. There are two robust fixes, and both remove the guessing:

' Fix 1 — set the first day explicitly, then Monday really is 1:
If Weekday(d, vbMonday) >= 6 Then MsgBox "weekend"   ' 6 = Sat, 7 = Sun

' Fix 2 — compare against the named constants, never magic numbers:
If Weekday(d) = vbSaturday Or Weekday(d) = vbSunday Then MsgBox "weekend"

The vb* constants (vbSunday, vbMonday, … vbSaturday) are correct regardless of the first-day argument, which is why comparing against them is the safest habit. The rule: never write a bare number for a weekday — either pass vbMonday so the numbering is what you expect, or compare to a vb* constant.

DatePart: the general extractor

DatePart(interval, date, [firstDayOfWeek], [firstWeekOfYear]) is the Swiss-army version: it takes the same interval codes as DateAdd and DateDiff and extracts that unit. For year, month, day, and time it duplicates the dedicated functions — but it is the only built-in way to reach a couple of parts that have no function of their own:

DatePart("q", d)     ' quarter of the year (1–4) — no Quarter() function exists
DatePart("ww", d)    ' week number of the year (1–53)
DatePart("y", d)     ' day of the year (1–366) — the "y" that means day-of-year
DatePart("m", d)     ' 8 — same as Month(d)

Reach for DatePart when you need the quarter or the week number, and use the plain Year / Month / Day functions for the everyday parts because they read more clearly. Note the same code trap from DateAdd: "y" is day-of-year and "ww" is week — say them out loud once.

The week-number trap at the year boundary

DatePart("ww", …) has its own default that surprises people: the first week of the year and the first day of the week both default to a US-style rule (vbSunday, vbFirstJan1), which is not ISO 8601 — the standard most of Europe and most business calendars use. So the last days of December and the first days of January can land in a week number you did not expect:

DatePart("ww", #2026-12-31#)                          ' 53  (US default rule)
DatePart("ww", #2026-12-31#, vbMonday, vbFirstFourDays) ' the ISO-style answer

If your week numbers matter — payroll weeks, sprint numbers, anything that must agree with a calendar on the wall — pass vbMonday as the first day of week and vbFirstFourDays as the first-week rule to match ISO 8601. The classic "my week numbers are off by one around New Year" bug is always these two defaults.

WeekdayName and MonthName are localized

To turn a weekday or month number into a name, use WeekdayName and MonthName. Both return the name in the machine's language, which makes them ideal for display and a mistake for anything you parse back:

WeekdayName(2)              ' "Monday" on an English machine, "Montag" on a German one
MonthName(8)               ' "August" / "August" / "août" depending on locale
WeekdayName(2, True)       ' abbreviated: "Mon"

Because the output follows the user's locale, never compare it to a fixed English string or write it into a file another system will read — that is a locale bug waiting to happen. For a stable label you control, format the date instead: Format(d, "dddd") gives the full weekday name and Format(d, "mmmm") the month name, still locale-aware but explicit, via Format. Use WeekdayName / MonthName for on-screen display; use numbers (Weekday, Month) for logic.

The honest verdict: the parts are easy, the defaults are not

Pulling parts out of a date is the least mysterious thing in VBA dates — with one asterisk that causes an outsized share of bugs:

Simple parts, simple functions → Year, Month, Day, Hour, Minute, Second. Each returns an integer; Month is a number, not a name.
Weekday has a hidden default → it is vbSunday, so Monday is 2. Pass vbMonday, or compare to vbSaturday / vbSunday — never a bare number.
DatePart for what has no function → the quarter ("q") and week number ("ww"); match ISO weeks with vbMonday + vbFirstFourDays.
Names are localized → WeekdayName / MonthName follow the machine language; use them to display, never to parse.

The one habit that prevents the most bugs: a weekday number should never be a literal. Say which day is first, or name the day you mean.

How ExcelMaster helps

Part-extraction bugs are subtle because the code looks obviously right: a weekend filter that checks Weekday = 1, a week-number column that disagrees with the payroll calendar every January, a report that writes WeekdayName to a file a system in another language then fails to read. Each is a default nobody remembered.

ExcelMaster writes the extraction that is right the first time. Describe the rule — "skip weekends," "group rows by quarter," "label each row with its ISO week" — and it uses Weekday(d, vbMonday) or the vb* constants for day-of-week logic, DatePart with the ISO arguments for week numbers, and keeps display names separate from the numbers you branch on. You describe the calendar rule; it writes the part-extraction that does not rely on a hidden default.

Frequently asked questions

Why does Weekday return the wrong day number in VBA?

Because Weekday numbers the week from its second argument, which defaults to vbSunday — so Sunday is 1 and Monday is 2. If you assumed Monday is 1, every result is off by one. Pass the first day explicitly, Weekday(d, vbMonday), so Monday is 1 through Sunday is 7, or compare the result against the named constants vbSaturday and vbSunday instead of hardcoded numbers.

How do I check if a date is a weekend in VBA?

Use the constants so the first-day default cannot bite you: If Weekday(d) = vbSaturday Or Weekday(d) = vbSunday Then …. The vb* constants are correct no matter what first-day-of-week argument is in play. Alternatively, Weekday(d, vbMonday) >= 6 treats 6 (Saturday) and 7 (Sunday) as the weekend — just be sure you passed vbMonday.

How do I get the quarter or week number of a date in VBA?

Use DatePart, because there is no dedicated Quarter or Week function. DatePart("q", d) returns the quarter 1–4, and DatePart("ww", d) returns the week number. For ISO 8601 week numbers, pass the extra arguments: DatePart("ww", d, vbMonday, vbFirstFourDays), or the week around New Year can be off by one.

What is the difference between Month and MonthName in VBA?

Month(d) returns the month as a number (1–12) for use in logic; MonthName(n) turns a month number into its name as text (MonthName(8) gives "August"). MonthName follows the machine's language, so use it only for display — for branching or comparisons use the number from Month.

Does WeekdayName return English names in VBA?

Only on an English-language machine. WeekdayName and MonthName return names in the system's locale, so the same code produces "Monday" or "Montag" or "lundi" on different machines. Never compare their output to a fixed English string; for a controlled label use Format(d, "dddd"), and for logic use the numeric Weekday.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-26.