Excel VBA: Get the Day of the Week from a Date Value

In Excel VBA, use Weekday to get a date’s weekday number and WeekdayName to turn that number into a name. For example, WeekdayName(Weekday(CDate(Range("A2").Value), vbMonday), False, vbMonday) returns the full weekday name for the date in A2, using Monday as day 1.

The VBA functions that do the work

Weekday evaluates a date and returns an integer from 1 through 7. The starting day depends on the optional second argument: use vbSunday to number Sunday as 1, or vbMonday to number Monday as 1. Being explicit is useful because it makes the result predictable when a workbook moves between computers with different regional settings.

WeekdayName converts a weekday number into a text label. Its second argument controls whether the result is abbreviated, and its third argument specifies the first day of the week used to interpret the number. The following example assumes a valid date is stored in cell A2:

Dim dayNumber As Long
Dim dayLabel As String

' Treat Monday as the first day of the week.
dayNumber = Weekday(CDate(Range("A2").Value), vbMonday)
dayLabel = WeekdayName(dayNumber, False, vbMonday)

If A2 contains a Monday, dayNumber is 1 and dayLabel is “Monday.” Set the abbreviation argument to True for a shorter label such as “Mon.” The exact abbreviation and spelling can depend on the language settings of the computer running Excel.

Make a worksheet function you can reuse

If you want a weekday name to appear in a worksheet cell, create a user-defined function (UDF) in a standard VBA module. This example accepts a date, returns the full name by default, and optionally returns an abbreviated name. It also returns Excel’s #VALUE! error if the input is an error value or is not recognized as a date.

Public Function DayNameFromDate(ByVal dateValue As Variant, _
    Optional ByVal abbreviated As Boolean = False) As Variant

    Dim dayNumber As Long

    ' Pass worksheet errors through as a value error.
    If IsError(dateValue) Then
        DayNameFromDate = CVErr(xlErrValue)
        Exit Function
    End If

    ' Reject blanks and values VBA cannot interpret as dates.
    If Not IsDate(dateValue) Then
        DayNameFromDate = CVErr(xlErrValue)
        Exit Function
    End If

    ' Use the same Monday-first convention for both functions.
    dayNumber = Weekday(CDate(dateValue), vbMonday)
    DayNameFromDate = WeekdayName(dayNumber, abbreviated, vbMonday)
End Function

To add it, open the Visual Basic Editor from Developer > Visual Basic, choose Insert > Module, and paste the function into the new module. If the Developer tab is hidden in Excel for Microsoft 365 on Windows, enable it through File > Options > Customize Ribbon. On a Mac, open the Visual Basic Editor from the Developer tab or use Tools > Macro > Visual Basic Editor, depending on the ribbon layout. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the VBA code is retained.

With dates in A2:A20, enter this formula in B2 to show each full weekday name, then fill it down: =DayNameFromDate(A2). With a date in A2, use =DayNameFromDate(A2,TRUE) to request an abbreviation. The formula’s result is text, so it is convenient for labels, reports, or sorting by a separate weekday-name column. If Excel displays a security warning when you open the workbook, only enable macros when you trust the file and its source.

Choose a weekday numbering convention

The same date can produce different weekday numbers depending on the convention you choose. For example, 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, Monday is 2, and Saturday is 7. The name returned by WeekdayName depends on using a matching convention in both calls.

For many business schedules, Monday-first numbering is easier to read because weekdays run from 1 to 5. For a Sunday-first calendar, specify vbSunday in both functions instead. Avoid relying on omitted arguments: VBA has defaults, but an explicit setting makes code easier to review and less likely to be misunderstood when someone changes it later. Also distinguish “weekday number” from “day of the month”: Weekday answers which day of the week a date falls on, not whether it is the 1st, 2nd, or 3rd of the month.

Return a number instead of a name

A numeric result is often more useful than a label when code needs to make a decision. For example, a procedure can use the Monday-first result to identify a weekend: values 6 and 7 correspond to Saturday and Sunday. The following test assumes dateValue is a valid VBA Date variable and uses Monday-first numbering:

Dim dayNumber As Long

' Monday is 1, Saturday is 6, and Sunday is 7.
dayNumber = Weekday(dateValue, vbMonday)
If dayNumber >= 6 Then
    Debug.Print "Weekend"
Else
    Debug.Print "Weekday"
End If

Keep the number for calculations and conditions, and convert it to a name only when displaying it. Text names can vary by language, while the integer convention is stable as long as you specify the first day. If you need a different numeric mapping, such as Sunday = 1 but Monday through Saturday = 2 through 7, choose the corresponding first-day argument rather than trying to adjust labels after the fact.

Fill weekday names down a column with a macro

A UDF recalculates as part of a worksheet formula. If you instead want a one-time procedure to write results into cells, a macro can loop through the date column. This example reads dates from A2 down to the last used row in column A and writes full names in column B. It clears the matching output cell when the input is not a date.

Sub WriteWeekdayNames()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim cellValue As Variant

    ' Work on the active sheet; change this assignment if needed.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Row 1 is assumed to contain a heading.
    For rowNumber = 2 To lastRow
        cellValue = ws.Cells(rowNumber, "A").Value

        If Not IsError(cellValue) And IsDate(cellValue) Then
            ws.Cells(rowNumber, "B").Value = _
                WeekdayName(Weekday(CDate(cellValue), vbMonday), _
                False, vbMonday)
        Else
            ws.Cells(rowNumber, "B").ClearContents
        End If
    Next rowNumber
End Sub

Before running a macro that writes to a sheet, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z, and this procedure overwrites existing values in column B for the rows it processes. On Windows, run it from Developer > Macros, or press Alt+F8 and select WriteWeekdayNames. On a Mac, use Developer > Macros or Tools > Macro > Macros; the Windows Alt+F8 shortcut does not apply in the same way. Review the output column first if it contains data you need to keep.

Dates, times, and regional settings

Excel stores a date as a serial value, and a cell may also contain a time fraction. Weekday uses the date portion, so a date-time value such as Monday at 11:30 p.m. still has Monday’s weekday. You do not need to remove the time merely to get the day name. If you are comparing full date-time values elsewhere in a macro, however, remember that two values on the same calendar date can differ because their times differ.

Be cautious when the input is text rather than a real Excel date. A text value such as 03/04/2025 can be interpreted differently in different regional formats: it may mean March 4 or April 3. IsDate and CDate follow VBA’s interpretation rules and the computer’s locale; they cannot reliably infer which meaning you intended. For imported data, convert text to genuine dates using a known source format before calculating weekdays. A cell that only looks like a date may still contain text, so check its underlying value or test it in VBA.

Names returned by WeekdayName are also language-sensitive. A workbook may show weekday names in the language of the computer that recalculates the function. If a file must always display a particular language, create and use an explicit mapping of numbers to the desired labels rather than assuming the operating system’s language will be the same for every user.

VBA compared with a worksheet formula

For a workbook that does not need VBA, built-in worksheet functions can be simpler and do not require a macro-enabled file. With a genuine date in A2, =TEXT(A2,"dddd") returns the full weekday name, while =WEEKDAY(A2,2) returns a number with Monday = 1 and Sunday = 7. Fill either formula down to process more rows. These examples assume A2 contains an Excel date value, not ambiguous date text.

Choose VBA when you need to use the result inside a procedure, apply custom validation, or make a reusable function with behavior specific to your workbook. Choose a worksheet formula for straightforward cell-by-cell results that users should be able to inspect and recalculate without enabling macros. Note that Excel’s TEXT formula and VBA’s WeekdayName both produce labels influenced by regional language settings; neither automatically guarantees a fixed language across different computers.

Common problems and how to fix them

The cell shows #NAME? Check that the UDF is in a standard module, that the workbook is saved as .xlsm, and that macros are enabled for a trusted workbook. A function placed inside a worksheet’s code module may not be available in the same way as one placed in a standard module.
The result is one day out. Check the first-day argument. Mixing vbMonday for Weekday with vbSunday for WeekdayName can map the number to the wrong label.
A date is rejected or interpreted unexpectedly. Confirm the cell contains a real date, not text, and check the regional interpretation of imported date strings. Use unambiguous source data where possible.
The displayed name is in another language. That is usually a locale issue, not an incorrect weekday calculation. Use a fixed number-to-name mapping if every user must see the same wording.
The macro reports an error at a worksheet reference. Verify that the intended sheet is active and that column A holds the input dates. For a fixed workbook layout, assign ws to a named worksheet instead of relying on ActiveSheet.

Frequently Asked Questions

What VBA function gets the day of the week from a date? Use Weekday(dateValue, vbMonday) for a number or wrap that result in WeekdayName for a name. Choose a first-day constant explicitly so the numbering is clear.

How do I return Monday, Tuesday, or another full day name? Use WeekdayName(Weekday(dateValue, vbMonday), False, vbMonday). The False argument requests the full name; use True for an abbreviation.

How can I get the weekday in an Excel cell without VBA? If A2 contains a genuine Excel date, enter =TEXT(A2,"dddd") for a name or =WEEKDAY(A2,2) for a Monday-first number. These worksheet formulas do not require a VBA module.

Does Weekday account for the time in a date-time value? It returns the weekday for the calendar date; a time on the same date does not change the weekday. If a value crosses midnight, its date portion—and therefore its weekday—changes.

Why does the weekday name differ between computers? The weekday calculation can be correct while the displayed text differs because weekday names and abbreviations can follow the computer’s regional language settings. Use fixed labels in your code if the wording must be identical everywhere.

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.