Excel VBA Day From Date: Weekday Numbers and Names

In Excel VBA, use Day(dateValue) to return the day of the month, such as 14, and Weekday(dateValue, vbMonday) to return the day of the week as a number, with Monday as 1. To get a weekday name such as Tuesday, combine Weekday with WeekdayName, or format the date with Format(dateValue, "dddd").

Day of the month versus day of the week

The word “day” can mean two different things in a date question. VBA’s Day function extracts the calendar day within a month. For a date such as 14 June 2026, Day returns 14. It does not tell you whether that date is a Sunday, Monday, or another weekday.

Use Weekday when you need the day of the week. It returns a number from 1 to 7, and the numbering depends on the optional first-day-of-week argument. Add WeekdayName if your code needs a readable label. Keeping these functions distinct prevents a common bug: treating a day-of-month result as though it were a weekday number.

Day(dateValue): day of the month, 1 through 31.
Weekday(dateValue, vbSunday): weekday number with Sunday as 1, the VBA default.
Weekday(dateValue, vbMonday): weekday number with Monday as 1.
WeekdayName(number, False, vbMonday): full weekday name using Monday-based numbering.

How Weekday numbering works

The second argument to Weekday sets which weekday counts as 1. If you omit it, VBA uses Sunday as the first day. For example, the date’s weekday number can therefore differ depending on whether you use the default or specify vbMonday. Choose a convention deliberately, especially if you compare results with a report, a database, or an international team’s workweek.

With Monday as the first day, Monday is 1, Tuesday is 2, and Sunday is 7. With Sunday as the first day, Sunday is 1 and Saturday is 7. The constants such as vbMonday and vbSunday make code easier to read than unexplained numeric arguments. For a Monday-to-Friday workday test, the Monday-based convention is particularly straightforward: values 1 to 5 are weekdays and 6 to 7 are weekend days.

VBA also has vbUseSystem, which uses the system’s first-day-of-week setting. That can be useful when a program is intended to follow a user’s regional settings, but it can make results vary across computers. If a workbook has a defined business rule—for example, weeks always start Monday—specify that constant instead of relying on the user’s settings.

Get a weekday name from a worksheet cell

For a quick macro, this example reads the first selected cell, checks that it contains a date-like value, then displays the weekday name and Monday-based number. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code, and run the procedure with F5 while a date cell is selected. On a Mac, open the Visual Basic Editor from the Tools > Macro menu or use Option+F11; the keyboard combination can depend on the keyboard’s function-key settings.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. To keep VBA code in the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm), rather than a standard .xlsx file.

Option Explicit

Sub ShowWeekdayForSelectedCell()
    Dim target As Range
    Dim dateValue As Date
    Dim weekdayNumber As Long
    Dim weekdayText As String

    ' Use the first cell if the selection contains multiple cells.
    Set target = ActiveCell

    If IsError(target.Value) Then
        MsgBox "The selected cell contains an error."
        Exit Sub
    End If

    If Not IsDate(target.Value) Then
        MsgBox "Select a cell containing a date."
        Exit Sub
    End If

    dateValue = CDate(target.Value)
    weekdayNumber = Weekday(dateValue, vbMonday)
    weekdayText = WeekdayName(weekdayNumber, False, vbMonday)

    MsgBox weekdayText & " (weekday number " & weekdayNumber & ")"
End Sub

The example uses ActiveCell, so it processes the cell where the selection cursor is, even if several cells are selected. If you want to process every date in a range, loop through the range and apply the same validation and conversion to each cell. Avoid assuming that every selected cell contains a valid date; blank cells, error values, and ordinary text need appropriate handling.

Use the result in a worksheet formula

If the goal is just to display weekday names in cells, a worksheet formula is often simpler than VBA. With a date in A2, the formula =TEXT(A2,"dddd") displays the full weekday name, while =TEXT(A2,"ddd") displays an abbreviated name. These formulas use Excel’s date formatting and do not require a macro. The displayed language generally follows the applicable Excel or regional language settings.

VBA can also expose a custom function to the worksheet. With the date in A2 and the function below saved in a standard VBA module, enter =DayNameFromDate(A2) in another cell. For a date that falls on Monday, the function returns Monday. Save a copy before adding VBA code, and save the workbook in .xlsm format so the function remains available.

Option Explicit

Public Function DayNameFromDate(ByVal inputDate As Variant) As Variant
    ' Return an Excel error for errors or values that are not dates.
    If IsError(inputDate) Then
        DayNameFromDate = CVErr(xlErrValue)
        Exit Function
    End If

    If Not IsDate(inputDate) Then
        DayNameFromDate = CVErr(xlErrValue)
        Exit Function
    End If

    DayNameFromDate = WeekdayName( _
        Weekday(CDate(inputDate), vbMonday), False, vbMonday)
End Function

A custom worksheet function is useful when the result should update as the source date changes. A regular macro is more suitable for actions such as writing fixed values into a report or showing a message. In Excel for the web, VBA macros cannot be created, edited, or run; use a desktop version of Excel for this VBA approach. A workbook containing VBA can still be opened in the web app, but its VBA code does not run there.

Choose between WeekdayName, Format, and DatePart

There is more than one way to produce a weekday label, but each approach serves a slightly different purpose:

WeekdayName(Weekday(d, vbMonday), False, vbMonday) makes the numbering convention explicit and returns a weekday name.
Format(d, "dddd") formats a date as a full weekday name; use "ddd" for an abbreviation. It is concise when formatting is the main task.
DatePart("w", d) returns a weekday number, but its default first-day convention is Sunday. Supply the optional first-day argument if you need a different convention.

For calculations, use Weekday and compare its numeric result. For display, use WeekdayName or Format. Formatting a date does not change the underlying date value; it only creates a text representation. If your later code needs to sort, subtract, or compare dates, keep the original date as a date rather than replacing it with a weekday-name string.

Dates, times, and dependable inputs

A VBA Date can contain both a calendar date and a time. Day and Weekday use the date portion, so a time such as 3:45 PM does not change the weekday. A cell may also contain a number formatted to look like a date, because Excel stores dates as serial values. The cell’s appearance alone does not prove that the underlying value is a valid date.

Use IsDate before converting uncertain input with CDate. Text dates can be interpreted according to regional settings: a string with ambiguous month and day values may be understood differently on different computers. For reliable VBA-created dates, use DateSerial(year, month, day) with separate numeric components rather than assembling an ambiguous text string. If dates come from imported files, verify the source format and convert the values consistently before using them.

Another practical issue is a blank cell or a formula that returns an empty string. Neither should automatically be treated as a meaningful date. The sample macro checks for an error and then checks IsDate, but a production routine may also want to test for an empty cell explicitly and report which row needs attention. This is especially helpful when processing a long list, where silently skipping a bad entry can conceal a data-quality problem.

Common mistakes and troubleshooting

Getting 14 when expecting Wednesday: the code probably uses Day. Replace it with Weekday to get a weekday number, or use WeekdayName for a name.
The weekday number is off by one: check the first-day-of-week argument. The default is Sunday, not Monday.
A date-looking value fails validation: it may be text in an unexpected format, an error, or an empty result. Inspect the cell value and use a consistent conversion method.
The returned name is in another language: formatting and name functions can reflect locale settings. If a fixed language is required, define how labels should be supplied rather than assuming every computer uses the same locale.
The macro is missing after saving: an .xlsx file does not retain VBA projects. Save the workbook as .xlsm, and follow your organization’s macro-security rules.

Do not enable all macros globally just to run one workbook. Use Excel’s security prompts and only run code from a source you trust. On Windows, macro controls are under File > Options > Trust Center > Trust Center Settings > Macro Settings; organizational policy may restrict the available choices. Excel for Mac has different settings and menus, but the same caution applies. A macro that merely reads a selected date is small, yet any VBA project can potentially contain other code, so review the full workbook’s source.

Frequently Asked Questions

How do I get the weekday name from a date in VBA?
Use WeekdayName(Weekday(dateValue, vbMonday), False, vbMonday). The matching first-day arguments make the weekday number and name use the same Monday-based convention.

What does Day(date) return in Excel VBA?
Day(date) returns the day of the month as an integer from 1 to 31. It does not return Monday, Tuesday, or another weekday.

How do I make Monday equal 1 in VBA?
Pass vbMonday as the second argument: Weekday(dateValue, vbMonday). This produces Monday = 1 through Sunday = 7.

Can I use VBA to show the day name in a cell?
Yes. A custom VBA function such as DayNameFromDate can return a name to a worksheet cell. If VBA is unnecessary, Excel’s TEXT worksheet function can display a date as a weekday name without a macro.

Why does my VBA weekday result differ from Excel’s?
Check whether the VBA code and worksheet calculation use the same week-start convention. VBA’s Weekday defaults to Sunday as 1, while a formula or a different function may be configured to start on Monday.

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.