How to display the Excel day of the week from a date with a simple formula?
With the Excel software, it is more convenient to display the Excel day of the week in all letters rather than the date in a numeric Date format. Indeed, when using dates with Microsoft Office and especially Excel to manage statistics and schedules tables, readability is important. So, how to display Excel weekday in letters from a date with a simple formula?
The date format of the day/month/year type in numeric, like for example “30/11/2017” is not the easiest to read to analyse data. Especially with days less than or equal to 12, because the month can be confused with the day. And the Anglo-Saxon format uses month/day/year instead, which can give dates like 11/11/2011. So here is how to convert the date to day name in Excel.
These two following concrete examples allow you to display the weekday written in all letters for the first case, or only the first three letters for the second option.
Table of Contents
1. Display the Excel weekday name in all letters
To display the Excel day of the week in all letters, one solution is to use the TEXT() Excel function with the day parameter on four letters: “dddd”. Simply add a formula in a cell, use the Text() function. Then add the TODAY() keyword as the first argument and the “dddd” as the second argument for the function.
=TEXT(TODAY(),"dddd")
2. Display the weekday from another cell in full letter
For example if the date to display in A1 cell, this formula do not use the current date with a time function, instead, is simply take a date, static or not, from another cell in the Excel Sheet.
=TEXT(A1,"dddd")
The result is Saturday for 01/01/2022 in A1. So, this formula will display the name of the day in all letters. That is to say the complete day. In this example the column will have the following format: Thursday
The formula arguments are from an English (UK) version of MS Excel, the syntax may vary depending on local language settings.
3. Display the Excel day of the week abbreviated in three letters
In other words, to display only the first three letters of the day, you have to use the TEXT() function with the keyword “dd”. Of course, if your source value is in another cell, then use the cell reference, here it is A1 in the second formula.
=TEXT(TODAY(),"ddd")
The result is Sat for Saturday. Use also this variation to get the date from another cell.
=TEXT(A1,"ddd")
In addition, the list of days of the week with the formula that uses the three letters keyword will display the abbreviated days as below:
- Mon
- Tue
- Wed
- Thu
- Fri
- Sat
- Sun
4. Excel formula to convert the day name to uppercase
Let’s convert today’s date into letters with Excel in uppercase this time. Always use the TEXT() formula, but this time with the parameter in uppercase: DDDD, as in this example it will display THURSDAY.
=TEXT(TODAY(),"DDDD")
5. Conclusion on displaying weekdays using Excel formulas
In conclusion, displaying the Excel day of the week in all letters or abbreviated form can greatly improve data readability. Simple formulas like TEXT() with the appropriate parameters can convert dates to the day name in Excel. You can use these formulas to display the day from a static or dynamic cell, or convert it to uppercase. Give them a try to save time and hassle when working with dates in your spreadsheets. They can help you better analyze your data and manage your schedules.
Note that this TEXT function also allows you to manage the format for different information. Among others: hours, weeks, months, and years but also percentages. And also, currencies with the symbol euro (€) or dollar ($) for example. To go further, here is a short MS Office tutorial on how to select an entire column in Excel using a shortcut.
