Excel is a powerful tool for managing dates, and one of its useful features is determining the day of the week from a given date. Knowing the day of the week can be very helpful when tracking deadlines, scheduling events, or analyzing trends.
You can use the WEEKDAY or TEXT function to get the Day of the Week from a given date.
The WEEKDAY function gives you a number representing the day of the week for the given date.
But I prefer using the TEXT function, which quickly displays the day of the week.
In this article, we’ll explore how to get the day of the week from date in Excel using the WEEKDAY and TEXT functions. Click on the following links to jump to that particular section of the article.
- Getting the Day of the Week using the TEXT function
- Getting the Day of the Week using the WEEKDAY function
Download the Example Excel Sheet
Consider downloading the following Excel sheet, which contains all the datasets and tables we will use to demonstrate the steps to get the day of the week in this article.
Follow me along as we will move through this article. It will surely strengthen your skills and knowledge.
Also, it is fine if you have your dataset ready to practice the concepts and formulas we will discuss in the following section.
How to Get the Day of the Week using the TEXT function in Excel
Using the TEXT function is the quickest method to get the day of the week in Microsoft Excel.
The TEXT is a simple function with the following general syntax,
=TEXT(value, format_text)While you convert a date to the day of the week, each argument in the above formula must be replaced with proper information.
- “value” – This is the numeric value that is to be converted to text. It can be a number, date, a cell reference containing a numeric value, or another function that returns a number or date
- “format_text” – Here, you can define the required output format. Make sure you use the double quotation marks. For example, “mm/dd/yy” or “dd/mm/yyyy”
Now, let us learn to get the day of the week from a given date using the TEXT function. We will consider a dataset containing students’ full names and birth dates. Our task is to extract the day each student was born.
We have a total of 20 records to convert. Refer to the following image,
Here are the steps,
- Select the cell “C2”
- Type “=TEXT”
- Select the first option from the popup or press “Tab” on your keyboard
- Replace the “value” argument with the cell reference “B2”
- Press “,” on your keyboard to move to the next argument
- For the format_text argument, we will use “dddd”
(Make sure to enter the format within double quotation marks “ ”) - End the formula with a closing parenthesis “)”
- Press “Enter” to see the result
The final formula would be as follows,
=TEXT(B2, “dddd”)To apply this formula for the entire column, hover to the lower-rightmost corner of the current cell where we have used the TEXT function. You will see a “+” icon. Click on that icon and drag the formula to the end of the table, as shown in the above GIF.
How to Get the Day of the Week using the WEEKDAY function in Excel
The WEEKDAY function converts a date into a number representing the day of the week.
Next, to get the name of the day of the week, you will have to use the SWITCH function.
Don’t worry because both of these functions are easy. Let us first understand each function in detail.
Explained: The WEEKDAY Function in Excel
The WEEKDAY function in Excel returns a number representing the day of the week for a given date. Depending on the day, this number can range from 1 (Sunday) to 7 (Saturday).
The General Syntax of the WEEKDAY function is as follows,
=WEEKDAY(serial_number, [return_type])Where,
- “serial_number” – It is the date that you want to convert to the weekday number. You can use the cell reference containing a date or enter the date manually
- “return_type” – This is an optional argument that determines what day of the week to use as the first day. If this argument is not specified, then Excel takes the default Sunday-Saturday week
Here is a list of all supported return_type values,
| return_type | Number returned |
|---|---|
| 1 or omitted | From 1 (Sunday) to 7 (Saturday) |
| 2 | From 1 (Monday) to 7 (Sunday) |
| 3 | From 0 (Monday) to 6 (Sunday) |
| 11 | From 1 (Monday) to 7 (Sunday) |
| 12 | From 1 (Tuesday) to 7 (Monday) |
| 13 | From 1 (Wednesday) to 7 (Tuesday) |
| 14 | From 1 (Thursday) to 7 (Wednesday) |
| 15 | From 1 (Friday) to 7 (Thursday) |
| 16 | From 1 (Saturday) to 7 (Friday) |
| 17 | From 1 (Sunday) to 7 (Saturday) |
Note that return_type values 11 to 17 were introduced in Excel 2010 and cannot be used in earlier versions.
Explained: The SWITCH Function in Excel
The SWITCH function in Excel converts the number derived from the WEEKDAY function into the day’s name.
This function compares an expression against a list of values and returns the result according to the first matching value.
If no match is found, it returns an optional default value.
The Syntax of the SWITCH function is as follows,
=SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])Where,
- “expression” – Needs to be replaced with the logical expression or text string that will be compared against the following arguments, value1, value2, and so on
- “valueN” – It is the value you wish to compare against the expression
- “resultN” – It is the desired output returned by the function when the corresponding valueN argument matches the expression
- “default” – Here, you need to enter the value returned if no matches have been found in the valueN expressions. This argument doesn’t have a resultN and must be the final argument in the function
You can use up to 126 pairs of value and result arguments in this function.
Now, let us get the day of the week using both of these functions.
STEP #1 – Get the number for each day of the week using the WEEKDAY function
Let’s consider the usual schedule by assuming that our week starts on Monday and ends on Sunday.
We will consider the previous example, including student names and their birth dates.
Here are the steps,
- Select the cell “C2”
- Type “=WEEKDAY”
- Select the first option from the popup or press “Tab” on your keyboard
- Now, to convert the first date, we’ll provide the cell reference as “B2”
- Press “,” on your keyboard to move to the next argument
- As we plan to start our week on Monday, we will put “2” in the place of the “[type]” argument
- Complete the formula with a closing parenthesis “)”
- Press “Enter” to see the result
The final formula would be as follows,
=WEEKDAY(B2,2)Note that our week starts on Monday, which means 1 will be assigned to Monday, 2 to Tuesday, 3 to Wednesday, and so on till 7 to Sunday.
STEP #2 – Convert numbers to the Days of the Week using the SWITCH function
As we do not want the output in the number format, we will use the SWITCH function to convert the numbers to the corresponding day of the week name.
This function is very much similar to the IF function but differs in the number of times you specify the expression. In the SWITCH function, you just need to specify the condition, unlike the nested IF, which requires the criteria to be repeated again and again.
We will perform the following conversion using the SWITCH function,
- 1 to Monday
- 2 to Tuesday
- 3 to Wednesday
- 4 to Thursday
- 5 to Friday
- 6 to Saturday
- 7 to Sunday
For expression, we will use the WEEKDAY function from step 1. Let’s begin,
- Select the cell “D2”
- Type “=SWITCH”
- Select the first option from the popup or press “Tab” on your keyboard
- Next, select the cell reference value as “C2”
(Here, our first date is in the cell “B2”; we are using the WEEKDAY formula in “C2” to get the number for the day of the week) - Press “,” on your keyboard to move to the next argument
- Type “1” to replace the argument “value1”
- Press “,” and move to the next argument
- Now, as we plan to convert the number “1” to Monday, we will type “Monday” to replace the argument “result1”
(Make sure to enclose “Monday” in double quotation marks to avoid the error) - Press “,” to move to the next argument
- For the second conversion, we need to put “2” in the place of the “value2” argument
- Press “,” to move to the next argument
- As we plan to convert the number “2” to Tuesday, we will type “Tuesday” in place of “result2”, making sure that the quotation marks are typed properly
- For the next 5 days of the week, repeat the above steps from 5 to 8
- Complete the bracket using “)”
- Press “Enter”
The final formula would be as follows,
=SWITCH(C2,1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")Final Words
Extracting the day of the week from a date in Excel is a straightforward task. You need to use either the TEXT or WEEKDAY function.
The TEXT function offers a quick and simple method to display the name of the day.
On the other hand, the WEEKDAY function can be used in combination with the SWITCH function for more flexibility and customization.
Let me know in the comment section below if you are stuck somewhere or facing any difficulties converting dates to days of the week.
Also, make sure to explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.