Being able to work with dates is an essential skill for anyone working in Excel. This is true, regardless of industry. For example, in project management you may need to be able to work with date duration. In finance and accounting, you’ll often need to aggregate data across different periods of times. In this tutorial, you’ll learn how to get the day of the week from a date in Excel. In particular, you’ll learn how to get the weekday name from a date.
By the end of this tutorial, you’ll have learned the following:
- How to get the day of the week from a date in Excel
- How to get the weekday name from a date in Excel
- How to display the weekday name in Excel but maintain date information
What Method is the Best for Getting the Weekday from a Date in Excel?
In this tutorial, you’ll learn three different methods for getting the day of the week from a date in Excel, including:
- Using the TEXT() function to show the weekday name for a given date
- Using the WEEKDAY() function to get the weekday as a number
- Using Excel formatting to display a weekday for a given date
So, you might be thinking, “What’s the best way to get the weekday for a given date in Excel?” And you’re not wrong for thinking this! Each of the methods provides a specific benefit over the other methods.
Let’s break this down:
| Method | Best For… |
|---|---|
| TEXT() | Getting the weekday as a text value |
| WEEKDAY() | Getting the day of the week as a number |
| Excel Formatting | Keeping date information but only changing how the date is displayed |
Now you’re probably thinking, “Great – but how do I do any of this?!” Don’t worry, we’re diving into that right now!
How to Convert a Date to Day of Week in Excel Using TEXT()
How can you convert a date to the day of week using 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.
Step 1: Load Your Dataset in Excel
In this example, we’ll use a dataset that contains seven sequential days. We’re using sequential dates to be able to demonstrate how the weekdays progress over time.
Step 2: Use the TEXT() Function to Get the Weekday Name
By passing in a date value into the Excel TEXT() function, we can customize how the date should be represented. For example, by passing in "ddd", as we did above, the formula returns an abbreviated version of the weekday. For example, Sunday is represented as Sun and Monday is represented as Mon.
Do you want to show full weekdays instead?
If you want to show the full weekday name, rather than an abbreviated version, you can pass in "dddd" into the TEXT() function instead. This will load the full weekday name, localized to your version of Excel.
Step 3: Use the Fill Handle to Copy the Formula Down
By clicking and dragging the fill handle, you can easily copy and paste the formula for multiple cells. When you drag the formula down for the entire range of values, Excel will extract the weekday name from each of the cells in the dataset.
In the following section, you’ll learn how to use the Excel WEEKDAY() function to get the number of the weekday, rather than its name.
How to Get the Day of the Week from a Date in Excel Using WEEKDAY()
There may be times when you simply want to represent a day of the week using a number. This can be really helpful when identifying whether or not a day belongs to a weekend or not. For example, if Saturdays and Sundays are represented as 6 and 7, you can use a simple IF() function to check if a date is a weekday.
The Excel WEEKDAY() function takes two arguments:
=WEEKDAY(serial_number,[return_type])The first argument is the date itself and the second is how the weekday should be calculated. By default, the return_type is set to 1. In this case, which sets Sundays equal to 0.
Let’s take a look at the other options available in the WEEKDAY() function:
| Return_type | First Number | Last Number |
|---|---|---|
| 1 or omitted | 1 = Sunday | 7 = Saturday |
| 2 | 1 = Monday | 7 = Sunday |
| 3 | 0 = Monday | 6 = Sunday |
| 11 | 1 = Monday | 7 = Sunday |
| 12 | 1 = Tuesday | 7 = Monday |
| 13 | 1 = Wednesday | 7 = Tuesday |
| 14 | 1 = Thursday | 7 = Sunday |
| 15 | 1 = Friday | 7 = Thursday |
| 16 | 1 = Saturday | 7 = Friday |
| 17 | 1 = Sunday | 7 = Saturday |
Let’s walk through a step-by-step example of how to get the day of the week in Excel.
Step 1: Load Your Dataset in Excel
In this example, we’ll use a dataset that contains seven sequential days. We’re using sequential dates to be able to demonstrate how the weekdays progress over time.
Step 2: Use the WEEKDAY() Function to Get the Weekday Number
By passing a date into the WEEKDAY() function, Excel will return a number between 0 and 7. When we don’t specify a return type value, Excel will start with Sundays being equal to 1 and moving up to Saturdays being equal to 7. We can modify this return value by changing the second parameter of the function.
Step 3: Use the Fill Handle to Copy the Formula Down
By clicking and dragging the fill handle, you can easily copy and paste the formula for multiple cells. When you drag the formula down for the entire range of values, Excel will extract the weekday number from each of the cells in the dataset.
In the following section, you’ll learn how to use the Excel formatting to display a weekday for a date without losing the details of the date.
How to Use Excel Formatting to Display a Weekday for a Date
In the previous sections, we used Excel to return either the weekday name or the number of the weekday. In both of these cases, the detail of the date isn’t carried forward, meaning that we either get a string of text or a number.
In some cases, however, you’ll want to simply display the weekday name, rather than overwriting the details of the date itself. In these cases, you can make use of the formatting options available in Excel.
To see how this works, let’s walk through a step-by-step example.
Step 1: Load Your Dataset in Excel
In this example, we’ll use a dataset that contains seven sequential days. We’re using sequential dates to be able to demonstrate how the weekdays progress over time.
Step 2: Select the Formatting Option Menu
With the cells selected, click on the dropdown on the Number formatting options. This will open up the Format Cells window.
Step 3: Select Custom and Click OK
Select the Custom category and type dddd into the Type field. This will format the date as an unabbreviated weekday. If you want to use a short form, instead, you can also pass in ddd. Select OK and Excel will apply the formatting to the cells.
Conclusion
In this tutorial, you learned how to get the day of the week from a date in Excel. You first learned how weekdays can be represented differently, including as text and as numbers. You then walked through three separate examples.
First, you learned how to get the weekday as a short-form or long-form text representing the weekday. Then, you learned how to get the day of the week as a number ranging from 0 through to 7. Finally, you learned how to show the weekday using formatting, without losing the details of the date directly.
Additional Resources
To learn more about related topics, check out the tutorials below: