TedJordan.Org - Logo

Table of Contents

Share this article

Join our newsletter

Excel: how to convert dates to days of the week?

Do you want to know what day was a specific date? Or maybe you want to know what day will be the next bank holidays? Discover 2 great functions to use in Excel to do so! For past or future dates, they are the formulas to know to save time.

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?

The MS 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.

The WEEKDAY function syntax contains 2 arguments: 1 is mandatory and 1 is optional.

Syntax of WEEKDAY 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.

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.

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).
2Numbers 1 (Monday) through 7 (Sunday).
3Numbers 0 (Monday) through 6 (Sunday).
11Numbers 1 (Monday) through 7 (Sunday).
12Numbers 1 (Tuesday) through 7 (Monday).
13Numbers 1 (Wednesday) through 7 (Tuesday).
14Numbers 1 (Thursday) through 7 (Wednesday).
15Numbers 1 (Friday) through 7 (Thursday).
16Numbers 1 (Saturday) through 7 (Friday).
17Numbers 1 (Sunday) through 7 (Saturday).
WEEKDAY return types

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.

If you want to convert a date into a day of the week, use WEEKDAY with DATE or use it with a date value.

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).

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 return the day of the week number from a date in Excel! If you want to return the name of that day, use the TEXT function.

One way to convert a date to a day of the week in Excel is to use the TEXT function.

The TEXT function syntax in Excel 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.

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!