Weekday Name in Excel: Paste This Easy Formula into Excel Cell
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.
Contents
Get 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 weekday number for the US system (Sunday = 1)
=WEEKDAY(A1)
Example 2: The weekday number for the European system (Monday = 1)
=WEEKDAY(A1,2)
Hold on a second. Was this information helpful so far?
If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
Subscribe now!
In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
How to get 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
Get the weekday name from any date given in another cell.
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")
Get the WEEKDAY name with this formula.
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’ll return the name of the weekday. 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'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
