If you are looking for an Excel function to get day of week from date, youve landed on the right page. This tutorial will teach you how to use the WEEKDAY formula in Excel to convert a date to a weekday name, filter, highlight and count weekends or workdays, and more.
There are a variety of functions to work with dates in Excel. The day of week function (WEEKDAY) is particularly useful for planning and scheduling, for example to determine the timeframe of a project and automatically remove weekends from the total. So, lets run through the examples oneatatime and see how they can help you cope with various daterelated tasks in Excel.
Working with dates in Excel? You’ll often need to find the corresponding day of the week for reporting and analysis.
Fortunately, Excel provides simple formulas to extract the weekday from a date.
In this comprehensive guide you’ll learn
 How to get the day name from a date in Excel
 Formulas to return the day name or abbreviation
 Options for customizing the day name format
 How to create a dropdown list of days
 Creative ways to use Excel’s weekday functions
Follow along to master various methods for getting the day of the week from any date in Excel.
Why Find Weekdays from Dates in Excel?
Some examples of when you may need to determine the weekday from a date
 Adding day of week labels to a dates column for reporting
 Analyzing sales by weekday to find highest sales days
 Planning schedules and deadlines based on weekdays
 Labeling dates in a calendar view by weekday name
 Grouping dates by weekday for visualization
 Appending day of week to dates in results for clarity
Extracting the correct weekday provides context, aids analysis, and improves readability of datebased data.
Excel makes this task simple with its builtin TEXT and WEEKDAY functions. Now let’s see how to use them.
Get Day Name from Date Using TEXT Function
The TEXT function in Excel allows extracting a specific part of a date, such as the day name.
To get the complete day name from a date:

Enter the date in a cell (A3 for example)

In another cell, use:
=TEXT(A3,”dddd”)
This will return the full day name, like Monday, Tuesday etc.
You can also get the abbreviated 3letter day name with:
=TEXT(A3,"ddddd")
![Get full day name from date in Excel]
The TEXT function has many formatting options to extract different date parts like month, year etc.
Get Weekday Number with WEEKDAY Function
If you want a weekday number instead of name, use the WEEKDAY function.
It returns a number from 1 to 7 for the weekday of a date, where 1=Sunday and 7=Saturday.
The formula is simply:
=WEEKDAY(A3)
To get the number for any other weekday as 1, use:
=WEEKDAY(A3,2)
This converts Sunday to 1, Monday to 2 etc.
![Get weekday number from date in Excel]
You can combine WEEKDAY with CHOOSE to convert the number into a day name:
=CHOOSE(WEEKDAY(A3), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
Customize Weekday Name Format in Excel
To customize the day names:

Use the TEXT function but enter your own custom formats instead of “dddd” or “ddddd”

For abbreviated days, use the LEFT function. For example:
=LEFT(TEXT(A3,”dddd”),3)

Nest the WEEKDAY function inside TEXT to return custom strings:
=TEXT(WEEKDAY(A3),”Day &”)
This will return Day 1, Day 2 etc. The ampersand & concatenates text.
![Customizing weekday name formats in Excel]
The possibilities are endless!
Create a Dropdown List of Weekdays
To make a dropdown list of days:

Enter the days in cells, like Sunday to Saturday.

Name this list using Formulas > Define Name > DaysList

In another cell, use Data Validation to create a dropdown using the named range DaysList.
Now you can pick days from the dropdown instead of typing!
![Creating a dropdown list of weekdays in Excel]
This provides a quick way to enter days without remembering formulas.
Innovative Uses for Excel Weekday Functions
The day name contains useful information you can extract or manipulate further:

Use LEFT, RIGHT, MID, SEARCH or other text functions to extract parts of the day name

Use IF statements or choose formulas based on the day name

Calculate start and end dates based on specified weekdays

Identify missing weekdays in a dataset for completeness

Calculate working days between dates after excluding weekends

Apply conditional formatting to highlight cells based on weekdays
The key is unlocking the flexibility of Excel’s text manipulation power using the day names.
Common Issues and Solutions
Avoid these common errors when extracting weekdays in Excel:

Entering the date format incorrectly in the original data

Using the wrong text format code like “ddd” instead of “dddd” in formulas

Forgetting the TEXT function and directly returning the date

Using twodigit year formats like “dd/mm/yy” which Excel won’t recognize

Accidentally overriding formulas with hardcoded text entries
Double check the original date and use the correct text codes to avoid errors.
Key Takeaways
The main points to remember when finding weekdays from dates in Excel are:

Use the TEXT function to return the full day name or 3letter abbreviation from a date

Get the weekday number with the WEEKDAY function instead of name

Customize the format of the day names as needed for your reporting

Create handy dropdowns to select days without formulas

Manipulate and extract parts of the day names using text functions
Mastering these simple but useful Excel date techniques will help boost your data analysis.
So learn to find weekdays from dates like an Excel pro!
How to highlight weekends workdays and in Excel
To spot weekends and workdays in your worksheet at a glance, you can get them automatically shaded in different colors. For this, use the weekday/weekend formula discussed in the previous example with Excel conditional formatting. As the condition is implied, we only need the core WEEKDAY function without the IF wrapper.
To highlight weekends (Saturday and Sunday):
To highlight workdays (Monday – Friday):
Where A2 is the upperleft cell of the selected range.
To set up the conditional formatting rule, the steps are:
 Select the list of dates (A2:A15 in our case).
 On the Home tab, in the Styles group, click Conditional formatting > New Rule.
 In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
 In the Format values where this formula is true box, enter the abovementioned formula for weekends or weekdays.
 Click the Format button and select the desired format.
 Click OK twice to save the changes and close the dialog windows.
For the detailed information on each step, please see How to set up conditional formatting with formula.
The result looks pretty nice, doesnt it?
How to count weekdays and weekends in Excel
To get the number of weekdays or weekends in the list of dates, you can use the WEEKDAY function in combination with SUM. For example:
To count weekends, the formula in D3 is:
=SUM((WEEKDAY(A3:A20, 2)>5))
To count weekdays, the formula in D4 takes this form:
=SUM((WEEKDAY(A3:A20, 2)<6))
In Excel 365 and Excel 2021 that handle arrays natively, this works as a regular formula as shown in the screenshot below. In Excel 2019 and earlier, press Ctrl + Shift + Enter to make it an array formula.
How these formulas work:
The WEEKDAY function with return_type set to 2 returns a day number from 1 (Mon) to 7 (Sun) for each date in the range A3:A20. The logical expression checks if the returned numbers are greater than 5 (for weekends) or less than 6 (for weekdays). The result of this operation is an array of TRUE and FALSE values.
The double negation () coerces the logical values to 1s and 0s. And the SUM function adds them up. Given that 1 (TRUE) represents the days to be counted and 0 (FALSE) the days to be ignored, you get the desired result.
Tip. To calculate weekdays between two dates, use the NETWORKDAYS or NETWORKDAYS.INTL function.
Excel: How to Show the Day of the Week for a Date  Weekday Function
How do I find the day of the week in Excel?
ddd: returns the day of the week's first 3 letters (will return Fri for 1/1/21). dddd: returns the day of the week in its full name (will return Friday for 1/1/21). Then click OK. And then we will have the days corresponding to the dates. Selecting any day will display its date in the Formula Bar.
How do I use a weekday function in Excel?
In the selected cell, type the following WEEKDAY function and press Enter. In this function, replace B2 with the cell where you have your date. If you'd like to use the date directly in the function, type the following WEEKDAY function with your date in it and press Enter. Keep double quotes around your date.
How to convert a date to a day of week in Excel?
In order to convert a date to a day of week in Excel, use this formula: =TEXT (A2, “ddd”). Using “ddd” will return an abbreviated weekday, such as Mon or Tue. Using “dddd” will return the full weekday name, such as Monday or Tuesday. Let’s take a look at how this works in Excel, by working through a step by step example.
How do I get the day of the week number?
If your goal is to get the day of the week number, then the WEEKDAY function in its basic form is all you need: If cell A1 contains a date like "18Sep2023" (Monday), the formula will return 2; or 3 if A1 contains "19Sep2023" (Tuesday). If you prefer having the day names, not numbers, you can combine the WEEKDAY function with the TEXT function.