Excel Day Based on Date: Find the Weekday

To show the weekday for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains a valid Excel date; for November 2, 2026, the result is Monday. If you need a weekday number instead, use =WEEKDAY(A2,2), which returns 1 for Monday through 7 for Sunday.

Choose the result you need

“Day based on date” can mean the day of the week, such as Monday, or the day number within the month, such as 2. Excel has different tools for each. Use TEXT or a date format to display a weekday name, WEEKDAY to get a numeric weekday, and DAY to get the calendar day of the month.

For the examples below, assume cell A2 contains the date November 2, 2026, entered as a real Excel date. That date is a Monday. Excel may display dates differently depending on regional settings, but the underlying date value is what the formulas use.

For the full weekday name, use =TEXT(A2,"dddd"). The result is Monday.
For an abbreviated name, use =TEXT(A2,"ddd"). The result is Mon.
For a Monday-based weekday number, use =WEEKDAY(A2,2). The result is 1.
For the day of the month, use =DAY(A2). The result is 2.

These formulas return different kinds of results. TEXT returns text, WEEKDAY returns a number from a chosen numbering system, and DAY returns a number from 1 to 31. Choosing the right one matters if you plan to sort, calculate, or use the result in another formula.

How Excel stores dates

Excel works with dates as serial numbers, rather than storing a date as a weekday word. A cell can display a date such as 11/2/2026 while holding a numeric value that Excel can use in calculations. A date format controls how that value looks; formulas such as WEEKDAY interpret the value as a date and calculate from it.

This distinction explains why a date may appear as a number after changing its cell format. The value has not necessarily changed; Excel is simply displaying it with a General or numeric format. Select the cell and choose a date format to display it as a calendar date again.

For a reliable date entry when regional date order could be ambiguous, with the year, month, and day specified directly in the formula, use =DATE(2026,11,2). The result is the Excel date November 2, 2026. DATE is useful in formulas because its arguments are year, month, and day, regardless of whether a user normally writes dates as month/day/year or day/month/year.

Excel workbooks can use different date systems, including the 1900 and 1904 systems. Most Windows workbooks use the 1900 system. The systems assign different serial values to some dates, but formulas that operate on valid dates within the same workbook generally produce the expected weekday. The distinction matters most when moving or linking older workbooks and comparing raw serial numbers.

Use TEXT to return the weekday name

When A2 contains November 2, 2026, =TEXT(A2,"dddd") returns Monday. The format code dddd requests the full weekday name; ddd requests an abbreviated name. The formula is convenient for reports, labels, and exports where the weekday should appear beside the date.

TEXT turns the formatted result into text. That is useful for presentation, but it means the output is not a date value or a numeric weekday. For instance, text weekday names may sort alphabetically—Friday, Monday, Saturday—instead of in calendar order. Keep the original date or a numeric weekday column if you need chronological sorting or calculations.

The wording may follow Excel’s language and regional settings. A workbook opened in another language or locale may display a localized weekday name. If a report must show weekday names in a particular language, verify the workbook’s regional behavior and the recipient’s Excel settings rather than assuming that a formula will produce identical text in every environment.

Use WEEKDAY for a number

With November 2, 2026 in A2, =WEEKDAY(A2,2) returns 1. The second argument, also called the return type, sets the numbering convention. Using 2 makes Monday 1, Tuesday 2, and so on through Sunday 7. This is often the most intuitive choice for work schedules that treat Monday through Friday as the workweek.

If the second argument is omitted, =WEEKDAY(A2) returns 2 for the same Monday because Excel’s default convention assigns Sunday 1, Monday 2, and Saturday 7. The date is unchanged; only its numeric label differs. Being explicit with the second argument makes a formula easier to read and reduces mistakes when other people use it.

WEEKDAY can support logic as well as display. With a valid date in A2, =IF(WEEKDAY(A2,2)<=5,"Weekday","Weekend") returns Weekday for November 2, 2026. The comparison works because the Monday-based numbering puts weekdays at 1 through 5 and Saturday and Sunday at 6 and 7. This simple test assumes a standard Saturday–Sunday weekend.

Display a weekday without a formula

If you only want the date cell to show its weekday, change its number format instead of adding a formula. The cell remains a date, so it can still be sorted and used in date calculations. In Excel for Microsoft 365 on Windows, select the date cell, press Ctrl+1, choose Number, then Custom, and enter dddd in the Type box. Choose ddd for an abbreviated weekday and select OK.

On Excel for Mac, select the cell and press Command+1 to open Format Cells; then use the Number and Custom options similarly. Excel for the web has date-format controls on the Home tab, though the available interface can differ from the desktop dialog. If you do not see a custom format option in the web app, use a TEXT formula or open the workbook in desktop Excel.

A custom display format changes only what the cell shows. For example, formatting a date as dddd displays Monday, but the cell still contains the full date. By contrast, replacing the cell with a TEXT formula produces text. Use formatting when you need to retain the date for calculations; use TEXT when you need the weekday name as a separate result.

Handle weekends and workday questions

A weekday name alone does not determine whether a date is a business day for every organization. Many workplaces treat Saturday and Sunday as weekends, but some schedules use different days off, and public holidays can also affect working-day calculations. WEEKDAY can identify a standard weekend; it does not know an organization’s holiday calendar.

For a Monday-through-Friday test, with a valid date in A2, =IF(WEEKDAY(A2,2)<=5,"Working day","Weekend") returns Working day for November 2, 2026. If the relevant weekend is different, adjust the logic to match the schedule. For business-day counts across a range, Excel also has NETWORKDAYS, which can account for a supplied holiday list; that is a separate calculation from simply returning the weekday name.

For example, a project tracker might keep the actual dates in column A, show weekday names in column B, and use a separate holiday list when counting workdays. Keeping those jobs separate makes the sheet clearer: the date remains the source value, the weekday is a readable label, and the holiday list records the business rules.

Common errors and how to fix them

The formula shows a number or an error: Check that A2 contains a valid date value, not text that only looks like a date. If Excel cannot interpret the entry under the current regional settings, re-enter it using an unambiguous date or construct it with DATE.
The weekday is one day off: Check the WEEKDAY return type. WEEKDAY(A2) and WEEKDAY(A2,2) use different numbering systems, so their numeric results are not supposed to match.
The cell displays the formula itself: The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell to General, re-enter the formula, and check the Formulas tab for the Show Formulas setting.
The name appears in another language: TEXT uses the workbook’s formatting and locale behavior. Check regional settings or use a numeric result if language-independent weekday logic is more important than a localized label.
Dates sort strangely after adding weekday names: A TEXT result is text, not a date. Sort by the original date column, or retain a numeric weekday value for sorting weekdays in calendar order.

Also check for hidden time values if a date came from imported data. A value can include both a date and a time, even when the cell displays only the date. WEEKDAY still evaluates the date portion, so the time normally does not change the weekday result; unexpected results are more likely to come from text parsing, an incorrect date, or a misunderstood numbering convention.

Which method should you choose?

Choose the method based on what the result must do, not just how it should look. Use a cell format when the date should remain intact and only its appearance needs to change. Use TEXT when a separate weekday label is needed. Use WEEKDAY when a formula needs a number for comparisons, grouping, or conditional logic.

Calendar or schedule view: Format the date as dddd or ddd if the date itself should remain available.
Report label: Use TEXT to put a weekday name in a neighboring cell.
Weekend test: Use WEEKDAY with return type 2 and test whether the result exceeds 5.
Day within the month: Use DAY, not WEEKDAY. November 2, 2026 has a day of month of 2 and a Monday weekday.

It is common to confuse DAY with “day of the week.” In Excel, DAY extracts the day number from the date’s month, while WEEKDAY identifies the position in the week. If a result is unexpectedly 2 for a Monday, confirm whether the formula uses DAY or WEEKDAY and whether the required output is a name, a weekday number, or a day-of-month number.

Frequently Asked Questions

How do I get the day name from a date in Excel?
With a valid date in A2, enter =TEXT(A2,"dddd") in another cell for the full weekday name. Use ddd in place of dddd for a shorter label.

How do I return Monday as 1 and Sunday as 7?
With the date in A2, use =WEEKDAY(A2,2). The return type 2 sets Monday to 1 and Sunday to 7.

What is the difference between DAY and WEEKDAY?
DAY returns the date’s position within its month, from 1 to 31. WEEKDAY returns the day’s position within the week according to a selected numbering convention. For November 2, 2026, DAY returns 2, while WEEKDAY with return type 2 returns 1.

Can I show the weekday in the date cell itself?
Yes. Apply a custom number format such as dddd to the date cell. This displays the weekday while retaining the underlying date value, unlike a TEXT formula that returns text in a separate cell.

Why does WEEKDAY return a different number than expected?
The optional return type controls the numbering system. If omitted, Sunday is 1; with return type 2, Monday is 1. Specify the return type explicitly so the formula matches the convention you intend.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.