How to Get the Day of the Week from a Date 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:

   =TEXT(A3,”dddd”)

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

You can also get the abbreviated 3-letter 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 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!