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

  1. Assuming cell A1 contains the date for which you want to determine the day.
  2. Go to the cell where you want the day of the week to appear.
  3. Enter the following formula there:
    =TEXT(A1; "DDD")
  4. As a result, the weekday of the date in A will appear in this cellThe 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

  1. Enter the date 24 in any position, for example.12.2017 a.
  2. Click once on the date so that the field is highlighted.
  3. Press the key combination [Ctrl + 1] around the dialog Format cell display.
  4. In the following dialog box on the left, select Category the entry Custom.
  5. 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.
  6. 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.
  7. Type: TTT. followed by a space, so that the field contains a total: TTT. DD.MM.YYYY
  8. 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.
  9. 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.