How to display the Excel day of the week from a date with a formula?
With the Excel software, it is more convenient to display the Excel day of the week 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?
Table of Contents
Display Excel day of the week using the TEXT 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.
Display the Excel weekday name in all letters
To display the Excel day of the week in 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")
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.
Display the Excel weekday 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
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")
To conclude, this article presents how to display the Excel day of the week. And in a much more explicit way with the list of days in letters.
Note that this formula 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.
Be the first to comment