Weekday Name in Excel: Paste This Easy Formula into Excel Cell

weekday, day, name, monday, sunday

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

weekday, weekday name, day, name, excel


The WEEKDAY formula has 2 arguments (the numbers are referring to the picture on the right hand side/above):

  1. 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.
  2. 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_typeNumber returned
1 or omittedNumbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2

Numbers 1 (Monday) through 7 (Sunday).
3Numbers 0 (Monday) through 6 (Sunday).
11 Numbers 1 (Monday) through 7 (Sunday).
12Numbers 1 (Tuesday) through 7 (Monday).
13Numbers 1 (Wednesday) through 7 (Tuesday).
14Numbers 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

weekday, name, date, link, reference, excel, day
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")
copy, paste, weekday, name, excel, day, date, choose,
Return the day 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 returns the name of the day. It can be that simple. Try it for free with the download button below.


Professor Excel Tools Box

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.


Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *