WEEKDAY Function in Excel: Syntax, Examples and Day Names

WEEKDAY Function in Excel: Syntax, Examples and Day Names

The WEEKDAY function turns a date into a number from 1 to 7 that tells you which day of the week it falls on. Type =WEEKDAY(A2) and Excel returns 1 for a Sunday and 7 for a Saturday. Add a second argument, =WEEKDAY(A2,2), and the week starts on Monday instead. If you want the name of the day ("Monday") rather than a number, pair it with the TEXT function or a custom number format. Both are covered below, along with weekend flags, counting formulas, conditional formatting and the errors that trip people up.

TL;DR

• =WEEKDAY(date, [return_type]) returns a number; with no return_type, Sunday is 1 and Saturday is 7.
• Use =WEEKDAY(A2,2) for a Monday-to-Sunday week (Monday is 1), which makes weekend tests simple: a result above 5 is Saturday or Sunday.
• To get a day name, use =TEXT(A2,"dddd") for "Monday" or =TEXT(A2,"ddd") for "Mon". A custom number format shows the name without changing the date.
• Combine WEEKDAY with IF, SUMPRODUCT or conditional formatting to label, count and highlight weekdays and weekends.
• #NUM!, #VALUE! and a date where you expected a number almost always come from a bad date, a text date or a cell format.

Table of Contents

• What the WEEKDAY function does
• WEEKDAY syntax
• Choosing a return_type
• A worked example: dates to weekday numbers
• How to get the day name from a date
• Flag weekdays and weekends with IF
• Count weekdays and weekend dates
• Highlight weekends with conditional formatting
• Check dates before they reach WEEKDAY
• Troubleshooting WEEKDAY errors
• Practice what you just learned
• Our perspective
• FAQ
• Sources

What the WEEKDAY function does

Excel stores every date as a serial number, so a formula can do math on it. WEEKDAY reads that number and tells you where in the week it lands. According to Microsoft's documentation for WEEKDAY, the function returns an integer representing the day of the week, and by default that integer runs from 1 (Sunday) to 7 (Saturday).

That number is useful for three jobs:

1. Deciding whether a date is a workday or a weekend.
2. Counting how many of a given kind of day sit in a list.
3. Feeding another function, such as CHOOSE or INDEX, that turns the number into a label.

If you only need the name of the day to read, you often do not need WEEKDAY at all. We get to that shortly.

WEEKDAY syntax

The syntax is:

=WEEKDAY(serial_number, [return_type])

Microsoft lists serial_number as required and return_type as optional. In plain terms:

• serial_number is the date you are testing. It can be a cell reference like A2, a date function such as TODAY(), or a date built with DATE(2026,12,25). Typing a date as text inside the formula can work in some regional settings, but it breaks easily, so a reference or the DATE function is safer.
• return_type tells Excel which day counts as 1. Leave it out and you get the Sunday-start week.

A quick test: put =WEEKDAY(DATE(2026,12,25)) in any cell. December 25, 2026 is a Friday, so with the default setting the result is 6.

If you want a refresher on building dates and pulling today's date into a sheet, our guide to Excel date functions covers DATE, TODAY, DATEDIF and DAYS, and the TODAY function page shows how to keep a weekday check current.

Choosing a return_type

The return_type argument is where most confusion starts. Microsoft documents these options:

Which one should you use?

For most business spreadsheets, 2 is the practical choice. Monday is 1, Friday is 5, Saturday is 6 and Sunday is 7. That means "is it a weekend?" becomes the single test WEEKDAY(A2,2)>5. With the default type you would need to check for 1 or 7 separately, which is longer and easier to get wrong.

Types 11 through 17 give the same kind of numbering but let you start the week on any day. They are handy for a retail week that starts on Saturday or a shift schedule that starts on Thursday. Type 3 is useful when you want a zero-based offset, for example to calculate how many days to subtract to land on the Monday of the same week.

One habit worth building: always write the return_type explicitly, even when you want the default. Anyone who opens the file later can see which week you intended.

A worked example: dates to weekday numbers

Suppose column A holds order dates and you want to know the weekday number for each one.

Notice how Sunday flips from 1 to 7 between the two columns. When a colleague tells you "the formula says 1 but it's a Sunday" or "it says 1 but it's a Monday," the return_type is almost always the explanation.

Fill the formula down by double-clicking the fill handle in the corner of the cell. Each row adjusts its reference automatically.

How to get the day name from a date

The WEEKDAY function gives you a number, not a word. Readers who search for how to get a day name in Excel usually want one of three things.

Option 1: the TEXT function

The quickest way is TEXT with a date format code. Microsoft's guide to showing dates as days of the week uses =TEXT(A2,"dddd") for the full name and "ddd" for the three-letter abbreviation:

• =TEXT(A2,"dddd") returns Monday
• =TEXT(A2,"ddd") returns Mon

The result is text, so you can join it to other words: ="Due on "&TEXT(A2,"dddd") returns "Due on Monday". Keep in mind that a text result cannot be used in date math afterward. If you need to sort by weekday order, sort by the original date or by a WEEKDAY helper column, because text sorts alphabetically (Friday before Monday).

Option 2: a custom number format

If you want the cell to look like a day name but stay a real date, change the format instead of the contents. Per Microsoft's steps: select the date cells, open the Number Format list, choose More Number Formats, pick Custom, and type dddd for the full name or ddd for the abbreviation in the Type box.

The formula bar still shows the full date, and every formula that refers to the cell keeps working. This is the right choice when you want a calendar header or a schedule where the date itself must remain usable.

To show both at once, such as "Monday, 6 July 2026" style output, use a custom format like dddd, mmmm d, yyyy, which follows the U.S. convention of month before day.

Option 3: WEEKDAY with CHOOSE

When you want your own labels, such as "Mon-Fri shift" or abbreviations in another language, feed WEEKDAY into CHOOSE:

=CHOOSE(WEEKDAY(A2,2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")

The number returned by WEEKDAY picks the matching item from the list. Because return_type 2 numbers Monday as 1, the list reads in natural Monday-first order. Change the labels to anything you like: "Weekday" five times and "Weekend" twice works too, though IF is shorter for that.

You can also store your labels in a range and use INDEX to look them up. Our INDEX function guide walks through that pattern.

Flag weekdays and weekends with IF

The classic use of WEEKDAY is a label. With return_type 2, Saturday is 6 and Sunday is 7, so anything above 5 is a weekend:

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

The same logic can be written the other way round, which some people find easier to read:

=IF(WEEKDAY(A2,2)<6,"Workday","Weekend")

If you leave out the return_type, you have to test for both ends of the default scale instead:

=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"Weekend","Weekday")

Both work; the first is shorter and has one fewer place to make a typo.

Using IF for other day-based rules

Once you have the pattern, you can adapt it:

• Friday cut-off: =IF(WEEKDAY(A2,2)=5,"Send report","")
• Different rate on weekends: =IF(WEEKDAY(A2,2)>5,B2*1.5,B2) multiplies the amount in B2 by 1.5 on Saturday and Sunday.
• Monday of the same week: =A2-WEEKDAY(A2,2)+1 subtracts the days since Monday, so every date in a week returns that week's Monday. Useful for weekly summaries.

If IF logic is still new to you, the IF function examples and practice exercises are a gentle place to build the habit of nesting conditions correctly.

Count weekdays and weekend dates

To count how many dates in a list fall on weekends, wrap WEEKDAY in SUMPRODUCT. It handles the whole range without needing array entry:

=SUMPRODUCT(--(WEEKDAY(A3:A20,2)>5)) counts Saturdays and Sundays.

=SUMPRODUCT(--(WEEKDAY(A3:A20,2)<6)) counts Monday through Friday.

Here is how it works. WEEKDAY evaluates every date in the range and returns a set of numbers. The comparison turns each one into TRUE or FALSE. The double minus converts TRUE and FALSE to 1 and 0, and SUMPRODUCT adds them up.

To count one specific day, such as Mondays, change the test to =SUMPRODUCT(--(WEEKDAY(A3:A20,2)=1)).

To total an amount on those days, multiply instead of counting: =SUMPRODUCT((WEEKDAY(A3:A20,2)>5)*B3:B20) adds up the values in column B that sit beside weekend dates. That is a quick way to compare weekday and weekend sales without building a pivot table.

All of these assume the dates in your range are real dates. Blank cells in the range read as zero, which Excel treats as a date serial number that maps to a weekday, so keep the range tight to your data to avoid counting empty rows.

Highlight weekends with conditional formatting

Color makes a schedule far easier to scan. Microsoft's own article on conditional formatting for dates uses the formula =WEEKDAY(B$5,2)>5 to shade weekends, noting that return_type 2 maps Saturday to 6 and Sunday to 7. The row in the reference is locked with a dollar sign so the rule applies correctly across the rest of the table.

Here is the step-by-step version for dates in A2:A100:

1. Select A2:A100.
2. On the Home tab, choose Conditional Formatting, then New Rule.
3. Pick "Use a formula to determine which cells to format."
4. Enter =WEEKDAY($A2,2)>5.
5. Click Format, choose a light fill color, and confirm.

The dollar sign before the column letter keeps the rule pointed at column A, while the row stays relative so each row tests its own date. To shade the whole row instead of just the date, select A2:F100 and keep the formula as =WEEKDAY($A2,2)>5.

For more rule patterns, see our pages on conditional formatting formulas and conditional formatting based on another cell.

Check dates before they reach WEEKDAY

Bad inputs create most WEEKDAY problems, so it helps to stop them at the door. Data validation can limit a column to real dates, or even to a date range. Our date validation guide shows the rules and formulas. You can also restrict entry to weekdays only by using a custom validation formula such as =WEEKDAY(A2,2)<6, which rejects Saturdays and Sundays when someone types a delivery date.

Troubleshooting WEEKDAY errors

#NUM!

Microsoft's documentation says to check that the serial_number is valid and the return_type is one of the allowed options when you see #NUM!. A return_type of 4, for example, is not on the list above, so =WEEKDAY(A2,4) fails. A negative date serial number will also fail.

#VALUE!

This one usually means Excel does not see a date. A cell can look like a date and still be text, for example when data was pasted from a web page or exported from another system. Signs of a text date: it is left-aligned by default, or a small green triangle sits in the corner. Fix it by re-entering the date, using Data > Text to Columns on the column and finishing with a date format, or converting with =DATEVALUE(A2) and then wrapping that in WEEKDAY.

A date appears instead of a number

If =WEEKDAY(A2) shows something like 1/6/1900, the formula cell has inherited a date format from the cell it refers to. WEEKDAY itself is working. Change the cell format to General or Number on the Home tab and you will see the 1 to 7 result.

The number is right but the day looks wrong

Check return_type first. Then check whether you are looking at a date typed in a different regional order. In the U.S., 3/4/2026 is March 4. In many other countries the same entry reads as April 3, and Excel will interpret it according to your system settings. If a file travelled between countries, confirm the dates are real dates in your locale.

Time values and WEEKDAY

Dates can carry a time as a decimal part, for example from NOW(). WEEKDAY ignores the time portion, so =WEEKDAY(NOW()) still gives the correct day. If you work with timestamps, our overview of Excel time functions explains how HOUR, MINUTE and TIME fit alongside date logic.

Practice what you just learned

Reading a formula is not the same as writing one. The fastest way to make WEEKDAY stick is to type it into a real grid, get it wrong, and see why. On LogicExcel you can do that for free, with no account and nothing to install: you enter formulas in an Excel-like environment and get instant feedback, and every mistake is explained.

Try the 95 free Excel exercises to work through date, logic and lookup problems at your own pace, or follow the structured Excel I to IV courses if you want a guided path. If you are preparing for a hiring test, the free Excel assessment practice includes the kind of date and IF questions that WEEKDAY solves. Beginners may prefer our Excel exercises for beginners.

Our perspective

> We built LogicExcel on one belief: people learn spreadsheet functions by using them, not by watching someone else use them. WEEKDAY is a perfect example. Its syntax fits on one line, and yet it is the function people most often get subtly wrong, because the return_type changes what the number means.
>
> A video can show you that =WEEKDAY(A2,2)>5 flags a weekend. It cannot show you the moment your own formula returns 7 for a Sunday when you expected 1, and what you do next. That moment is where understanding forms, and it is why every exercise on our platform gives instant feedback in a real Excel-like grid and explains the mistake instead of only marking it wrong.
>
> Our advice for WEEKDAY is simple. Pick return_type 2 unless you have a reason not to, write it explicitly, and test your formula on a Friday, a Saturday, a Sunday and a Monday before you trust it on a full column. Then practice the same pattern in IF, SUMPRODUCT and conditional formatting until it feels automatic. We keep it free, with no sign-up, so that nothing stands between you and that practice.
>
> Thomas Houza

FAQ

How do I get the day name from a date in Excel?

Use =TEXT(A2,"dddd") for the full name or =TEXT(A2,"ddd") for the short form. If you would rather keep the cell as a real date, apply a custom number format of dddd or ddd instead. Microsoft documents both approaches on its page about showing dates as days of the week.

How do I get only weekdays in Excel?

To test a single date, use =WEEKDAY(A2,2)<6, which is TRUE for Monday through Friday. To filter a list, add a helper column with that formula and filter for TRUE. To count weekdays in a range, use =SUMPRODUCT(--(WEEKDAY(A3:A20,2)<6)).

Why does WEEKDAY return 1 for Sunday?

Because the default return_type is 1, which numbers the week from Sunday (1) to Saturday (7). Add a second argument of 2 and Monday becomes 1 and Sunday becomes 7. Setting it explicitly in every formula avoids surprises.

Can WEEKDAY return the name of the day directly?

No, it returns a number only. To turn the number into a name, either use TEXT on the date or wrap WEEKDAY in CHOOSE with your own list of labels. TEXT is shorter, while CHOOSE lets you use custom wording.

What is the difference between WEEKDAY and a date format that shows the day?

WEEKDAY returns a number you can calculate with. A custom format such as dddd only changes how the date looks and leaves the underlying value unchanged. Use WEEKDAY when a formula needs the day, and a format when a reader just needs to see it.

Sources

• WEEKDAY function, Microsoft Support
• Show dates as days of the week, Microsoft Support
• Using conditional formatting to highlight dates in Excel, Microsoft 365 Blog

Recommended

• Excel Date Functions: TODAY, NOW, DATE, DATEDIF, DAYS
• TODAY Function