To show a date as weekday text in Excel, use =TEXT(A2,"dddd") when A2 contains a real Excel date; it returns the full day name, such as Monday. Use =TEXT(A2,"ddd") for an abbreviated name, such as Mon. These formulas work in Microsoft 365 for Windows, Mac, and Excel for the web.
What “weekday text” means in Excel
A date in Excel is generally stored as a number, with a date format controlling how that number appears on screen. Weekday text is the day name—Monday, Tuesday, and so on—shown either by a formula or by a display format. The distinction matters: a formula using TEXT produces a text result, while a custom number format changes the appearance of the original date without replacing its underlying value.
For example, a cell might contain the date November 2, 2026. That date falls on a Monday. A formula can return the word Monday, while a custom format can make the date cell display Monday even though it still contains the complete date. Choose based on what you need to do next: a text result is useful for labels and reports, and a date with a weekday display is usually better for sorting and date calculations.
Excel’s date system also explains a common surprise: a cell that looks like a date may actually contain text copied from another application. The TEXT formula expects a date value, not merely characters that resemble one. Check the input before troubleshooting the weekday formula.
Use TEXT for a full or abbreviated weekday
Assume A2 contains the real Excel date November 2, 2026, and enter the formula in B2. The result is Monday:
=TEXT(A2,"dddd")
To return the short weekday name instead, use the three-d form. With the same date in A2, the result is Mon:
=TEXT(A2,"ddd")
The number of d characters controls the usual weekday display: dddd gives the full name and ddd gives an abbreviation. These are format codes inside the TEXT function, not separate Excel functions. They are not case-sensitive in the way the day-name result is typically displayed, but using the familiar lowercase forms makes formulas easy to recognize.
To apply the result to a list, put dates in A2:A20, enter the formula in B2, and fill it down. On Windows, drag the fill handle at the bottom-right corner of B2, or double-click it if the adjacent column has continuous data. Excel adjusts the reference for each row, so the next formula refers to A3, then A4, and so on. On Mac, the fill handle works similarly; Excel for the web also supports filling formulas down.
Choose the right result for reports and data
The TEXT method is a good fit when the weekday itself is the output you need—for example, when adding a “Day” column to an exported schedule or creating a heading for a summary. The returned value is text, so it can be concatenated with other text and used as a label. It is not a replacement date, however. Sorting a column of weekday names sorts alphabetically, not in calendar order: Friday can appear before Monday.
If you need records to remain in chronological order, keep the actual dates in a separate column and sort by that column. You can hide the date column or use it as the basis for a report while displaying weekday text elsewhere. For a schedule grouped by day of the week, an explicit weekday sort order is also needed; ordinary alphabetical sorting does not follow Monday-to-Sunday order.
A date can include a time as well as a day. With a valid date-time in A2, the weekday formula still returns the day name for that date. It ignores the time portion for this display, so a value representing Monday at 4:30 p.m. returns Monday. If a calculation crosses midnight, the underlying date-time determines which weekday is shown.
Keep the date value and change only its display
If you want the cell to look like a weekday but still behave as a date, apply a custom number format instead of using TEXT. Select the date cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose the Custom category, and enter dddd as the format. The selected cells then display full weekday names while retaining their original date values. To show abbreviations, use ddd.
This approach is useful when the same cells must still support date arithmetic, chronological sorting, or date-based filters. Changing the display format does not alter the date, and selecting the cell still reveals its underlying value in the formula bar. If you need both the readable date and the weekday, choose a format that includes both, such as a weekday followed by a date, rather than removing the date from the display entirely.
Formatting is different from converting the value to text. If you copy a custom-formatted date cell, the copied value can remain a date; if you copy a TEXT formula’s result as a value, the result is the weekday text. That difference can affect imports, pivots, sorting, and later calculations. It is worth deciding which kind of output a downstream report or system expects before converting a whole column.
Get a weekday number or choose custom labels
Sometimes a day number is more useful than its name. With a real date in A2, this formula returns 1 for Monday, 2 for Tuesday, through 7 for Sunday:
=WEEKDAY(A2,2)
The second argument, 2, sets Monday as the first day of the week. If you omit that argument, Excel uses its default numbering in which Sunday is 1 and Saturday is 7. WEEKDAY returns a number rather than a name, which can be handy for rules, calculations, or sorting when you want the week to start on Monday.
You can also map that number to your own labels. Assuming A2 is a real date, this formula returns Monday for November 2, 2026:
=CHOOSE(WEEKDAY(A2,2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
CHOOSE is useful if a report needs deliberately selected labels, such as “Mon” or “Workday,” but the list is fixed in the formula and must be maintained if the labels change. For a standard day name, TEXT is shorter and easier to read. WEEKDAY is preferable when the numeric day position matters; custom formatting is preferable when the original date should remain the displayed value.
Fix text dates and unexpected results
If TEXT returns an error or an unexpected day, first check whether the input is a real date. One simple check is to select the cell and temporarily choose General from the Number Format list. A real date will usually display as a number; text that looks like a date will remain text. Restore the desired date format afterward. Imported values may also have leading spaces or an apostrophe, which can prevent Excel from interpreting them as dates.
For a text date that Excel can interpret under the computer’s regional date settings, DATEVALUE can convert the text before TEXT formats the weekday. For example, suppose A2 contains the text 11/2/2026 and the workbook’s regional settings interpret that as November 2, 2026. Enter this formula in B2; the result is Monday:
=TEXT(DATEVALUE(A2),"dddd")
That example is locale-dependent: in another region, 11/2/2026 may mean February 11 rather than November 2. When imported data uses ambiguous date strings, confirm the source convention before converting it. A reliable conversion cannot be chosen from the characters alone if the source could mean more than one date.
Other common causes of a wrong-looking result include referencing the wrong row, entering a date as text, or expecting a different language’s weekday spelling. The TEXT format follows Excel’s language and regional settings, so the formula may return a localized day name rather than English. If the workbook will be shared internationally, test the display on the target system or use a deliberate mapping formula when fixed English labels are required.
Regional settings and compatibility
TEXT, WEEKDAY, CHOOSE, and DATEVALUE are established Excel functions and are available in Microsoft 365 for Windows, Mac, and the web. The basic formulas behave the same across those platforms. Excel may display day names according to the application or operating-system language and regional settings, so users can see different localized names even when the date and formula are identical.
Formula separators can also depend on regional settings. The examples use commas, as is standard in many installations. If Excel rejects a formula despite correct cell references, the local list separator may be a semicolon; use the separator Excel expects in that installation. Do not change the date format code to fix a separator error, because the separator and the weekday format code serve different purposes.
For files that must travel between computers, keep actual dates in the data and avoid treating displayed names as the sole record of the day. A name such as Monday does not identify a particular calendar date, and language or sort order can vary. Retaining the source date makes later checks and recalculations more dependable.
Frequently Asked Questions
What is the Excel formula for the day name?
With a real date in A2, use =TEXT(A2,"dddd") for the full weekday name. Use =TEXT(A2,"ddd") for an abbreviation.
How do I show the weekday without changing the date?
Format the date cell with the custom format dddd. The cell will display the weekday, but it will still contain the original date and can still be used in date calculations.
Why does my weekday formula return the wrong day?
Check that the input is a real date, that the formula points to the intended cell, and that an imported text date has been interpreted using the correct regional convention. A date such as 11/2/2026 can be ambiguous between locales.
How can I get Monday as 1 and Sunday as 7?
With the date in A2, use =WEEKDAY(A2,2). The second argument sets Monday as the start of the week; the function returns a number, not a day name.
Can I sort weekday text in calendar order?
Not with ordinary A-to-Z sorting, which orders the names alphabetically. Keep and sort by the original date, or use a separate Monday-to-Sunday sort key if the list must be grouped by weekday.
Related
- Excel Not Showing All Rows? Find and Restore Hidden Rows
- Excel Not Pulling Data From Other Sheets? Fixes
- Excel Not Pasting All Cells Copied? Fixes That Work
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting Based on Value in Excel: Rules and ExamplesIn Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home >…
- How to Archive Emails in Outlook 365To archive email in Outlook 365, select a message and choose Archive on the toolbar or ribbon. Outlook…
- Archive Large Amounts of Emails in OutlookTo archive a large number of emails in Outlook, find the messages you want to keep, select them…
- Median in Google Sheets: Formula and Median IfIn Google Sheets, calculate a median with =MEDIAN(B2:B7). For a “median if” calculation—finding the median only for rows…
- Find Year in Excel: Extract a Year from a DateTo find the year in an Excel date, enter =YEAR(A2) in an empty cell, replacing A2 with the…
- How to Create Mailing Labels in WordTo create mailing labels in Word, open the Mailings tab, choose Start Mail Merge > Labels, select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.