You want to know the weekday of a certain date? Excel provides the WEEKDAY formula. The formula returns the day of the week as a number, for example 1 for Sunday, 2 for Monday and so on. You can further modify, which weekday should start with the number one.
Return the weekday number (1-7) with the WEEKDAY formula
The WEEKDAY formula has 2 arguments (the numbers are referring to the picture on the right hand side/above):
- The first part of the WEEKDAY formula contains the date you want to get the weekday from. In the example it’s the cell reference to B4 which has a date.
- In the second part you need to specify, which system you’d like to use. In the default system the Sunday is set to number 1. In Europe, the week starts usually with Monday. In such case you should choose system 2.
The help function of Excel provides the following table. The first column shows the numbers available as the return type (number two in the picture). The second column describes the system:
| Return_type | Number returned |
|---|---|
| 1 or omitted | Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel. |
| 2 | Numbers 1 (Monday) through 7 (Sunday). |
| 3 | Numbers 0 (Monday) through 6 (Sunday). |
| 11 | Numbers 1 (Monday) through 7 (Sunday). |
| 12 | Numbers 1 (Tuesday) through 7 (Monday). |
| 13 | Numbers 1 (Wednesday) through 7 (Tuesday). |
| 14 | Numbers 1 (Thursday) through 7 (Wednesday). |
| 15 | Numbers 1 (Friday) through 7 (Thursday). |
| 16 | Numbers 1 (Saturday) through 7 (Friday). |
| 17 | Numbers 1 (Sunday) through 7 (Saturday). |
Example 1: The day number for the US system (Sunday = 1)
=WEEKDAY(A1)Example 2: The day number for the European system (Monday = 1)
=WEEKDAY(A1,2)How to return the WEEKDAY name instead of the number
Method 1: Use the VLOOKUP formula
In many cases, you don’t want to deal with numbers but rather the names of the weekdays (see number 3 in the image above). A simple VLOOKUP should help converting the number to a name. Therefore, you have to prepare a lookup table as shown in number (4): The numbers 1 to 7 in one column and the corresponding names in the next column. So the VLOOKUP formula could look something like this:
=VLOOKUP('your date',F:G,2,FALSE)Method 2a: Copy and paste this formula
If you want to get the weekday name of the date, given in another cell, please copy and paste this formula. Instead of “A1” within the WEEKDAY formula, just refer to the cell you like.
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")Method 2b: Copy and paste this formula for getting today’s weekday name
If you want to get todays weekday, just copy and paste this formula:
=CHOOSE(WEEKDAY(TODAY()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
The first part of the formula (WEEKDAY(TODAY())) returns the number of the current weekday. The corresponding name of the day will be returned from the second part. Please refer to this article for some explanation about the TODAY formula.
Expert tip: Use ‘Professor Excel Tools’
Our Excel add-in “Professor Excel Tools” comes with a useful formula: If you type =PROFEXWeekdayName() into a cell and link to a date (or provide a date within the brackets), it returns the name of the day. It can be that simple. Try it for free with the download button below.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
