How Excel shows the day of the week of a date
You need the right day of the week for a date? No problem, Excel can easily calculate it. However, the function WEEKDAY does not help, although it sounds so logical.
To calculate the day of the week for a date
- Assuming cell A1 contains the date for which you want to determine the day.
- Go to the cell where you want the day of the week to appear.
- Enter the following formula there:
=TEXT(A1; "DDD")
- As a result, the weekday of the date in A will appear in this cell
The TEXT() function helps to display the day of the week of a date.
Why the function WEEKDAY() does not fit here? This function does not output day names, but numbers from 1 to 7, which stand for a certain day. This is how WEEKDAY() throws out the number 1 for a date if it is a Sunday.
For a quick weekday calculation you don’t need to work with two cells at all, just enter a formula with a fixed date like
=TEXT("1.12.1979"; "TTTT")
Display the day of the week for a fixed date. Advertising
show weekday with date in one field
- Enter the date 24 in any position, for example.12.2017 a.
- Click once on the date so that the field is highlighted.
- Press the key combination [Ctrl + 1] around the dialog Format cell display.
- In the following dialog box on the left, select Category the entry Custom.
- Normally on the right side at Type already the format TT.MM.YY marks. If not, look for this entry and click on it once, so that it is highlighted blue.
- The marked entry also appears directly in the row under Type:. Exactly in this line you click now and press the key [Pos1], to set the insertion point to the far left.
- Type: TTT. followed by a space, so that the field contains a total: TTT. DD.MM.YYYY
- At Example you will see now: Like this. 13.11.2017.
Using a user-defined formatting, Excel also shows you the day of the week for the date.
- After confirming with OK the display of the date changes as desired.
Notes
- If in step 7 you select TTTT the full name of the weekday appears, i.e Saturday 13.11.2000.
- The tip Excel: Show day of the week shows you another method to see the day of the week for a date. Because a special function is used there, however, you need a separate field with it, in order to indicate the weekday.
- If you want to output the respective calendar week instead of the weekday, this can be done with a simple function.
- If you would like to have a colored marker instead of the name, which indicates whether a date is on the weekend or not, then read on here: Excel 2010: Weekend and weekdays visually distinguished.
- Also very simple possible: An automatically filled list with dates without weekends, i.e. only the days Monday to Friday.