How to Get the Day of the Week from a Date in Excel

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 one-at-a-time and see how they can help you cope with various date-related 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 drop-down 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 date-based data.

Excel makes this task simple with its built-in 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:

  1. Enter the date in a cell (A3 for example)

  2. In another cell, use:


This will return the full day name, like Monday, Tuesday etc.

You can also get the abbreviated 3-letter day name with:



![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:



To get the number for any other weekday as 1, use:



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:


  • 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 Drop-down List of Weekdays

To make a dropdown list of days:

  1. Enter the days in cells, like Sunday to Saturday.

  2. Name this list using Formulas > Define Name > DaysList

  3. 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 drop-down 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 two-digit 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 3-letter 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 drop-downs 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 get day of week from date in excel

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 upper-left 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 above-mentioned 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? Highlight weekends and weekdays in Excel.

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. Count weekdays and weekends in Excel.

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 "18-Sep-2023" (Monday), the formula will return 2; or 3 if A1 contains "19-Sep-2023" (Tuesday). If you prefer having the day names, not numbers, you can combine the WEEKDAY function with the TEXT function.

Related Posts

Leave a Reply

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