Excel Day of Week from Date: Formulas and Formatting

If your date is in A2, enter =TEXT(A2,"dddd") to show its full weekday name; for example, an actual date of April 15, 2024 returns Monday. To return a weekday number instead, use =WEEKDAY(A2,2), which returns 1 for Monday through 7 for Sunday.

Choose the result you need

There are two common meanings of “get the day of the week” in Excel: show a readable label such as Monday, or calculate a number such as 1. A third option is to change how the date looks without creating a formula. The best choice depends on what you will do with the result afterward.

Use TEXT when you want a weekday name in another cell, such as a report column.
Use WEEKDAY when you need a number for sorting, counting, or other calculations.
Use a custom number format when you only want the date cell to display its weekday while retaining the original date value.

These approaches assume Excel recognizes the cell contents as dates. If a value is stored as text, Excel may not calculate its weekday correctly until the value is converted.

How Excel stores dates

Excel dates are stored as serial numbers, not as weekday names. The number represents a day in Excel’s date system; the cell’s number format controls how that value appears. That is why the same underlying date can display as 4/15/2024, 15-Apr-2024, or Monday without changing the date itself.

Excel’s date system also makes date arithmetic possible. For example, adding one to a date serial moves the date forward by one day, and WEEKDAY can use the serial to determine which day of the week it represents. In Windows workbooks, the 1900 date system is typical. Excel also supports a 1904 date system for compatibility with some older workbooks. When workbooks using different systems are exchanged, dates can display differently, so check the workbook’s date-system setting if dates appear shifted by several years.

A date must be a valid Excel date value for these calculations to be dependable. Formatting a text string to look like a date does not necessarily turn it into a date; changing appearance and converting the underlying value are separate operations.

Get the weekday name with TEXT

With the date in A2 and the desired weekday name in another cell, use =TEXT(A2,"dddd"). If A2 contains April 15, 2024, the result is Monday. TEXT converts the date into text using the format pattern inside its second argument.

For a compact label, use =TEXT(A2,"ddd"). With the same April 15, 2024 date in A2, this returns Mon. The three-letter form is useful in narrow tables or chart labels; the four-letter pattern displays the full weekday name.

TEXT is convenient when the label needs to be a separate result, but its output is text, not a date. That distinction matters if you later sort or calculate with the output. Alphabetical sorting of weekday names, for instance, puts Friday before Monday rather than arranging the days in calendar order. Keep the source date or a numeric weekday value if you need chronological ordering.

The displayed language usually follows Excel’s language and regional settings. A format pattern that produces English day names in one installation may produce localized names in another. If a workbook must always show a particular language regardless of the reader’s settings, consider using a fixed-language mapping or a carefully designed lookup table rather than relying on localized date formatting.

Return a weekday number with WEEKDAY

With the date in A2, =WEEKDAY(A2,2) returns a number from 1 to 7, with Monday as 1 and Sunday as 7. For April 15, 2024, the result is 1 because that date was a Monday. The second argument, called the return type, sets which day begins the numbering.

If you omit the return type, =WEEKDAY(A2) uses the default Sunday-first numbering: Sunday is 1, Monday is 2, and Saturday is 7. State the return type explicitly when the numbering convention matters, so a later reader does not have to guess what the numbers mean.

Monday-first numbering is often useful for work schedules and weekday calculations. Sunday-first numbering may suit calendars that display Sunday in the first column. Neither system is inherently more correct; choose the one that matches the task, and label numeric results clearly. A number 1 could mean Monday or Sunday depending on the return type.

WEEKDAY returns a number, not a name. If you need both, keep a WEEKDAY result for calculation and use TEXT or a number format for the human-readable label. This avoids treating text labels as though they were ordered numbers.

Display the weekday in the date cell

If you want the date cell itself to show a weekday, apply a custom number format instead of using a formula. Select the date cells, press Ctrl+1 on Windows to open Format Cells, choose the Number tab and Custom category, enter dddd in the Type box, and select OK. The date value remains unchanged even though the cell now displays only its full weekday name.

Use ddd as the custom format for a shortened name such as Mon. To show both the date and weekday, use a pattern such as ddd, mmm d, yyyy; an April 15, 2024 date then appears as Mon, Apr 15, 2024. Custom date-format codes can be combined, so you can choose a display that fits a report without adding helper columns.

On Excel for Mac, Command+1 opens Format Cells in current desktop versions; the ribbon-based route is also available. Excel for the web offers number-format controls, but its dialogs and options can differ from the Windows desktop steps. Custom formatting changes presentation only: formulas still refer to the original date serial, and sorting still uses the date value.

Use the right method for sorting and analysis

For a simple report, TEXT is often the quickest route. For analysis, WEEKDAY is generally more flexible because numeric results can be compared, counted, or used in conditions. A custom format is ideal when the date itself remains important and you only want the display to communicate the day.

For example, suppose A2:A8 contains one date for each day from Monday, April 15, through Sunday, April 21, 2024. With the date in A2, =WEEKDAY(A2,2) returns 1; fill the formula down and the results run from 1 to 7. You can use those numbers in a summary, while keeping the date column as the reliable basis for chronological sorting.

If you instead use =TEXT(A2,"dddd") for the same sequence, the labels read Monday through Sunday, but sorting that text column alphabetically will rearrange the days. A practical report may therefore include both the date and the weekday label, or include a numeric weekday helper column and hide it when it is not needed.

When using formulas in an Excel Table, enter the formula in the first row of the result column; Excel will usually fill the calculated column automatically. In a regular range, drag the fill handle or copy the formula down. Relative reference A2 adjusts to A3, A4, and so on as the formula is filled.

Fix dates that return the wrong result

If the formula shows an error, an unexpected name, or a result that seems one day off, check the source value before changing the formula. Common causes include dates stored as text, a time component, a regional date-order mismatch, and a cell formatted to display something other than its actual value.

Check whether the date is numeric. Select the cell and change its format temporarily to General. A real Excel date appears as a serial number; text typically remains text. A value that looks like 04/05/2024 may also be interpreted differently in regions that use month/day versus day/month order.
Convert text dates carefully. If Excel recognizes the text, re-entering the value or using Data > Text to Columns may help convert it. Confirm the interpretation before applying formulas, especially when both the day and month are 12 or lower.
Check for hidden time values. A date-time value still has the correct weekday for its calendar date, but a time near midnight can make a value imported from another time zone appear to belong to a different date. Inspect the underlying data if the source includes times.
Check the WEEKDAY return type. A Monday result of 2 is expected with the default Sunday-first system; it is 1 with return type 2. Make sure the formula’s numbering convention is the one you intended.

Blank cells need special care because Excel can treat a blank reference like zero in date-related calculations. If A2 may be blank and you want the output cell to stay blank rather than show a weekday label, use =IF(A2="","",TEXT(A2,"dddd")). With A2 empty, the formula returns an empty string; with A2 containing April 15, 2024, it returns Monday.

Frequently Asked Questions

How do I get the day name from a date in Excel? If the date is in A2, enter =TEXT(A2,"dddd") for the full name or =TEXT(A2,"ddd") for an abbreviation. The source must be a recognized Excel date.

How do I get Monday as 1 and Sunday as 7? With the date in A2, use =WEEKDAY(A2,2). The second argument sets Monday-first numbering; the default numbering starts with Sunday instead.

Can I show the weekday without a formula? Yes. Apply a custom format of dddd to the date cell. This changes what you see but preserves the date value for sorting and calculations.

Why does my weekday formula return a number or the wrong day? Confirm that the source is a genuine date rather than text, and check the formula’s return type. Also inspect imported date-times and regional date interpretation if the displayed calendar date is unexpected.

Can I sort dates by weekday? Sort the original date column to keep dates chronological. For grouping or weekday order, create a WEEKDAY helper column with an explicitly chosen return type; sorting weekday-name text alone sorts alphabetically, not Monday through Sunday.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.