Excel: how to convert a date to day 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 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.

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.

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

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

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.

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!

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More
Programmatic: Pixels in Advertising 101

Advertising pixels: types, pros and best practices

Learn the basics of Programmatic Advertising with Ted Jordan! Today, we explain what a pixel is in advertising with simple words and expertise. Ready to learn?

Read More

Categories

AllFunctionsToolsShortcut Keys

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More

How to use the IFERROR function (Excel + Google Sheets)

Discover how to use IFERROR in Excel or in Google Sheets in 4 easy steps! No more errors on your documents or dashboards with IFERROR.

Read More
Cope Paste Options on excel with shortcut keys

Copy paste values like a pro! (Excel+Google Sheets)

Copy and paste values (not formulas) using shortcut keys in Excel or Google Sheets! Learn how spreadsheets professionals do it within seconds and become one of them. Save time now...

Read More