Do you need to convert dates to days of the week to analyse data for your customers? For example, if your clients are retailers and you want to highlight which days of the week they generate most sales.
Maybe you want to know what day will be the next meeting with your clients just by inserting a date?
Today, we will show you 2 handy methods of generating weekdays from dates in Excel, with examples of course!
Ready to discover Excel WEEKDAY and TEXT functions for dates?
What is WEEKDAY in Excel?
The Excel WEEKDAY function returns the day of the week corresponding to a specific date. This date can be entered by using the DATE function or as a result of other formulas. You may select a cell containing a date in the WEEKDAY formula.
What is the WEEKDAY formula in Excel?
The WEEKDAY function syntax contains 2 arguments: 1 is mandatory and 1 is optional.
WEEKDAY formula in Excel: =WEEKDAY(serial_number,[return_type]).
serial_number – This argument can be a serial number, a date, the result of a formula in a date format. The serial_number argument is required.
return_type – This argument is optional. It’s a number that determines the type of return value to use.
What is the serial number in WEEKDAY?
In the WEEKDAY function, in Excel, the serial number is a sequential number that represents the date of the day you want to use.
By default, the serial number 1 corresponds to 1 January 1900. The serial number 1,000 will correspond to the 1,000th day after 1 January 1900: 26 September 1902.
WEEKDAY return types: what number do they return?
The return_type argument in the WEEKDAY function is important even if optional. Indeed, choose wisely as it will impact the result of the WEEKDAY formula. We recommend you to use return type number 2 (so 1=Monday and 7=Sunday).
| 1 (or omitted) | Numbers 1 (Sunday) through 7 (Saturday). |
| 2 | Numbers 1 (Monday) through 7 (Sunday). |
| 3 | Numbers 0 (Monday) through 6 (Sunday). |
| 11 | Numbers 1 (Monday) through 7 (Sunday). |
| 12 | Numbers 1 (Tuesday) through 7 (Monday). |
| 13 | Numbers 1 (Wednesday) through 7 (Tuesday). |
| 14 | Numbers 1 (Thursday) through 7 (Wednesday). |
| 15 | Numbers 1 (Friday) through 7 (Thursday). |
| 16 | Numbers 1 (Saturday) through 7 (Friday). |
| 17 | Numbers 1 (Sunday) through 7 (Saturday). |
What are the differences between WEEKDAY return types 1 and 17, 2 and 11?
There are no difference between return types 1 and 17, and return types 2 and 11. Return types 11 to 17 were created afterward in a new Excel version.
How to use WEEKDAY in Excel
If you want to convert a date into a day of the week, use WEEKDAY with DATE or use it with a date value.
WEEKDAY in Excel – Combined with the DATE function
The syntax will be =WEEKDAY(DATE(YYYY,MM,DD),return_type).
Example:
Let return the day of the week of this date: 31/10/23.
We enter in Excel the following WEEKDAY formula, combined with the DATE function: =WEEKDAY(DATE(2023,10,31),2).
It’s important to enter the year in full (2023 and not 23 in our example) otherwise the system might give you an incorrect result.
31/10/2023 was a Tuesday (2nd day of the week).
WEEKDAY in Excel – Date as value
If we choose to use a date value as the serial_number argument in the WEEKDAY function in Excel, this is the formula to use: =WEEKDAY(cell number,return_type).
Example:
Let return the day of the week of this date: 31/10/23.
We enter in Excel the following WEEKDAY formula: =WEEKDAY(D13,2).
Our date value is in cell D13 and the return_type we want to use is 2.
31/10/2023 was a Tuesday (2nd day of the week).
Now, you know how to convert a date to the day of the week in Excel! If you want to return the name of that day, use the TEXT function.
How to show the name of a day from a date in Excel?
One way to convert a date to a day of the week in Excel is to use the TEXT function.
What is the TEXT formula in MS Excel?
In Excel, the TEXT function formula is =TEXT(Value you want to format, “Format code you want to apply“).
In our case, in order to show the name of a day for a specific date we will use one of these formulas in Excel:
=TEXT(date,”dddd”)
=TEXT(date,”ddd”).
date – A cell containing a date or a formula resulting in a date value.
dddd or ddd – This argument is not case sensitive (ddd=DDD and dddd=DDDD). dddd will result in the name of a day in its entirety and ddd will result in an abbreviated name of a day.
Name of the day in Excel: TEXT function examples
TEXT in Excel – Full name of the day
If you want to show the full name of the day in Excel from a date, use this formula: =TEXT(date,”dddd”).
In this example, we want to know the day of 31/10/2023 selecting the cell containing the date. Thanks to the TEXT function, we now know 31/10/2023 was a Tuesday!
In this example, we want to know the day of 31/10/2023 entering the date manually: it was a Tuesday.
Now, let us show you how to convert a date into a day of a week using TEXT and resulting in an abbreviated name (Mon, Tue, Wed, etc.).
TEXT in Excel – Abbreviated name of the day
If you want to show the name of the day (in an abbreviated form) from a date, use this formula: =TEXT(date,”ddd”).
In this example, we want to know the day of 31/10/2023 selecting the cell containing the date. The result is Tue: 31/10/2023 was a Tuesday.
Now, let convert 31/10/2023 into a day of the week using the DATE formula within TEXT: =TEXT(DATE(2023,10,31),”ddd”).
31/10/2023 was a Tuesday because the result it Tue.
Now, you know how to display the name of a day in Excel!
Master the Art of Spreadsheet by joining our MS Excel Course today: learn and get certified!
