Day of Week in Excel: WEEKDAY Function Explained (2024)

Looking to level up your Excel game in 2024?

Then try the Excel WEEKDAY function! This handy tool allows you to quickly and easily determine the day of the week for any given date.

Whether you’re a data analyst or just looking to organize data, the WEEKDAY function is a must-know 🤓

In this article, we’ll dive into the ins and outs of this function and show you how to harness its full potential. So let’s get started!

You can also download our sample workbook here to practice the WEEKDAY function.

What is the WEEKDAY function in Excel?

The WEEKDAY function in Excel is used to return the day of the week corresponding to a particular date. It is a part of the Date and Time functions in Excel.

The syntax for the WEEKDAY function is:

=WEEKDAY(serial_number,[return_type]) 

Where:

• serial_number is the date for which you want to find the day of the week. This argument can be entered directly as a date value (e.g., “5/4/2023”) or as a reference to a cell containing a date value.
• return_type: This is an optional argument that specifies the type of return value. If omitted, the default value is 1. The return_type can take one of the following values:
  • 1 (default) – Returns numbers from 1 (Sunday) to 7 (Saturday)
  • 2 – Returns numbers from 1 (Monday) to 7 (Sunday)
  • 3 – Returns numbers from 0 (Monday) to 6 (Sunday)
  • 11 – Returns text values of the day of the week (e.g., “Sunday,” “Monday,” etc.)

The WEEKDAY function is particularly useful when you need to calculate data based on the day of the week 🧐

For example, you could use it to create a pivot table that shows the average sales for each day of the week. Or you can use it to calculate the number of days until the next weekend.

Find day of week with WEEKDAY (number)

We will use the WEEKDAY function to find the day of a week a particular date occurred. Let’s see how to do it below.

Say, we have the following sample data

We want to find the day of the week for April 1, 2023. To do that,

1. Select cell B2.
2. Enter the formula as:

=WEEKDAY(

3. Enter the specified date or cell reference as:

=WEEKDAY(A2)

4. Press Enter.

And voila! It’s done 🎯

Microsoft Excel returns the day of the week – seven – meaning April 1st is on Saturday. This is the default format of the WEEKDAY function result 😃

Double-click the Fill Handle to copy paste the values.

How to convert weekday number to name of day

Let’s now see how to convert the weekday number into the name of the day using the TEXT function.

Its syntax is given as follows:

=TEXT(serial_number, “dddd”)

Where serial_number is the date you want to find the day of the week. And dddd is a date format code that returns the full name of the day of the week 📆

We will use the same sample data as used before.

And we will convert the previously used date to the weekday name. To do that,

1. Select cell B2.
2. Enter the formula as:

=TEXT(

3. Enter the date or serial number as:

=TEXT(A2,

4. Enter the format as:

=TEXT(“A2”, “dddd”)

5. Press Enter.

The name of the weekday appears as:

Double-click the Fill Handle to copy the values.

Pretty cool, no? Try it yourself now 💪

That’s it – Now what?

In this article, we saw what the WEEKDAY function in Excel is. We also saw how to use the WEEKDAY function to find the day of the week.

We also saw how to convert a weekday number into the name of the day and more 😉

Luckily, similar to WEEKDAY, Excel has a huge variety of other awesome functions. Our personal favorites include IF, SUMIF, VLOOKUP, etc.

Do you want to learn them? If yes, then join my 30-minute free email course today that teaches just this and more. Only at the cost of your email address – so sign up now!

Other resources

Did you enjoy reading this article? If yes, then you’d love to read more.

Try similar topics: Week Number, Date Functions, Excel Functions, and more.