Excel Formula for Weekend vs. Weekday Dates

Formulas & functions · Updated November 19, 2026

In Excel, use WEEKDAY to tell a weekend from a weekday: if the date is in A2, =WEEKDAY(A2,2)>5 returns TRUE for Saturday or Sunday and FALSE for Monday through Friday. To display a word instead, use =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday").

The basic weekend test

The second argument in WEEKDAY sets how Excel numbers the days. With the value 2, Monday is 1, Tuesday is 2, and Sunday is 7. That makes the test simple: numbers above 5 identify Saturday and Sunday.

With dates in A2:A20 and a test formula in B2, enter this formula in B2, then copy it down the column:

=WEEKDAY(A2,2)>5

For example, if A2 contains Saturday, November 7, 2026, the formula returns TRUE. If A2 contains Monday, November 9, 2026, it returns FALSE. Excel displays the logical results as TRUE or FALSE; you can use those results in filters, other formulas, or conditional formatting.

The formula classifies calendar days only. It does not know whether your workplace, school, or business treats Saturday as a workday, or whether a weekday is a holiday. For a different schedule, use a custom test or a business-day function, as described below.

How WEEKDAY numbers dates

WEEKDAY takes a date and an optional return type. The return type determines which number represents each day, so the comparison in your formula must match that numbering. Using 2 is a convenient choice for a standard Monday-to-Friday workweek because the weekend days are the final two numbers.

  • WEEKDAY(date,1) numbers Sunday as 1 through Saturday as 7. For the usual Saturday-and-Sunday weekend, test whether the result is 1 or 7.
  • WEEKDAY(date,2) numbers Monday as 1 through Sunday as 7. Test whether the result is greater than 5.
  • WEEKDAY(date,3) numbers Monday as 0 through Sunday as 6. Test whether the result is greater than 4.

For a date in A2, the Sunday-first version returns TRUE when the date is Sunday or Saturday:

=OR(WEEKDAY(A2,1)=1,WEEKDAY(A2,1)=7)

Both approaches work; the important thing is not to mix a return type with the wrong test. For example, checking whether WEEKDAY(A2,1)>5 would mark Friday as a weekend day too, because that numbering assigns Friday the value 6.

Show Weekend or Weekday as text

A TRUE/FALSE result is useful for logic, but a label is often easier to read in a report. With dates in A2:A20 and labels in B2:B20, place this formula in B2 and fill down:

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

For a Saturday date in A2, the result is Weekend; for a Wednesday date, it is Weekday. The words in quotation marks are the text Excel returns. You can change them, for instance to “Off day” and “Workday,” without changing the underlying test.

If the date list may contain empty cells, avoid showing a label for an empty row. With dates in A2:A20 and results in B2:B20, use this version in B2:

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

This checks for an empty A2 before evaluating the day and returns an empty string for an empty row. It is helpful in lists that will grow over time, although the result is technically a formula returning empty text rather than a truly empty cell.

Use different weekend days

Not every schedule defines the weekend as Saturday and Sunday. A Friday-and-Saturday weekend, for example, can be checked by testing for day numbers 5 and 6 when using return type 2. With the date in A2, this formula returns TRUE for Friday or Saturday:

=OR(WEEKDAY(A2,2)=5,WEEKDAY(A2,2)=6)

For a work schedule with only Sunday off, test for 7 under the same numbering. With a date in A2, use:

=WEEKDAY(A2,2)=7

When the weekend days are not consecutive, OR can list each day number. For a repeating schedule with a more unusual pattern, make the intended workdays explicit and check the formula against dates you know. The built-in WEEKDAY function can number the days, but it does not automatically infer a region’s customs or an employer’s schedule.

Highlight weekends with conditional formatting

To color weekend dates without adding a helper column, select the date cells, then choose Home > Conditional Formatting > New Rule. Select the rule type that uses a formula to determine which cells to format, enter the formula below, choose a format, and confirm the rule. In Excel for Microsoft 365 on Windows, this applies the formatting based on each row’s date.

Assuming the selected range is A2:A100 and A2 is the top-left cell, use:

=WEEKDAY($A2,2)>5

The dollar sign locks the date column while leaving the row number relative. That allows the rule to evaluate A2 for the first row, A3 for the next, and so on. If the selected range begins in another row or the date is in another column, adjust the reference to the top-left date cell. On Mac, the Conditional Formatting command is also on the Home tab; names and dialog layouts can vary slightly by Excel version. Excel for the web also supports conditional formatting, though its rule editor may look different.

Weekends are not the same as holidays

A weekday/weekend formula answers only which day of the week a date falls on. It does not identify public holidays, company closures, or personal days off. For a business-day count or date calculation, Excel provides NETWORKDAYS and WORKDAY, which use Monday through Friday as working days and can exclude a supplied holiday range.

For example, suppose A2 contains April 6, 2026, B2 contains April 10, 2026, and E2:E10 is the holiday list, with April 8, 2026, entered as a holiday. The range from Monday to Friday has four working days after that holiday is excluded. With the start and end dates in A2:B2 and holidays in E2:E10, enter:

=NETWORKDAYS(A2,B2,$E$2:$E$10)

The result is 4. The function counts both the start and end dates when they are working days, excludes Saturday and Sunday, and excludes the listed holiday. The dollar signs keep the holiday range fixed if you copy the formula elsewhere.

WORKDAY calculates a date a specified number of working days before or after a starting date. With April 6, 2026, in A2 and April 8, 2026, in the holiday list E2:E10, the fifth working day after the start date is April 14, 2026:

=WORKDAY(A2,5,$E$2:$E$10)

The starting date itself is not counted as day one. Here, April 7 is the first workday, April 8 is skipped as a holiday, and the count reaches five on April 14. These functions are useful for deadlines and schedules, but confirm that the default Monday-to-Friday pattern matches your workweek.

Common errors and date checks

If a formula gives an error or classifies a date unexpectedly, check the value in the date cell before changing the weekday test. Excel needs a valid date value for WEEKDAY. A cell that looks like a date may instead contain text, especially if it was imported from a file or entered in a format Excel did not recognize.

  • Check the cell value: Select the date cell and inspect the formula bar. Re-enter the date using a format Excel recognizes, or convert the source data to actual dates before relying on day calculations.
  • Check the return type: Make sure the comparison matches the numbering scheme. Under return type 2, Saturday and Sunday are 6 and 7; under return type 1, Sunday is 1 and Saturday is 7.
  • Check blank rows: Use a blank-handling version of the label formula if rows without dates should remain visually empty.
  • Check the reference: When copying a formula down, confirm it points to the date on the same row. When using conditional formatting, the formula’s row reference should align with the first row of the selected range.
  • Check the holiday list: NETWORKDAYS and WORKDAY only exclude dates included in the supplied range; they do not load a country’s holiday calendar automatically.

A frequent misconception is that Excel stores a date as a special weekday object. In practice, Excel represents recognized dates as numeric serial values and displays them using a date format. WEEKDAY uses the date value to calculate the day number; changing a cell’s display format alone does not turn text into a usable date.

Which formula should you choose?

Choose a formula based on what the spreadsheet needs to do, not just how the result should look. A logical result can feed further calculations, while a text label is more readable for a human-facing list. A conditional formatting rule changes appearance without placing another value in the sheet.

  • Use =WEEKDAY(A2,2)>5 when a TRUE/FALSE weekend test is useful for calculations or filtering.
  • Use an IF formula when a column should say Weekend or Weekday.
  • Use conditional formatting when the goal is to visually highlight date cells or entire rows.
  • Use NETWORKDAYS to count working days between two dates, or WORKDAY to calculate a date after a number of working days.
  • Use a custom OR test or a suitable custom workweek function when the normal Saturday-and-Sunday pattern does not apply.

For ordinary workweek labels, the return-type-2 test is usually the easiest to remember: Monday through Friday are 1 through 5, and Saturday and Sunday are 6 and 7. Keep that convention consistent across formulas, formatting rules, and any related business-day calculations.

Frequently Asked Questions

What is the simplest Excel formula to check if a date is a weekend?

If the date is in A2, use =WEEKDAY(A2,2)>5. It returns TRUE for Saturday and Sunday, and FALSE for Monday through Friday.

How do I show the day name instead of Weekend or Weekday?

Format the date cell to display a full or abbreviated day name, or use Excel’s TEXT function if you need a separate text result. A date format changes how the date appears; it does not change the stored date value.

Does WEEKDAY count holidays as weekends?

No. WEEKDAY reports the day of the week only. A holiday that falls on Tuesday is still identified as a weekday. Use a holiday list with NETWORKDAYS or WORKDAY when you need to exclude specified dates from business-day calculations.

Can I check for a Friday-and-Saturday weekend?

Yes. With the date in A2, use =OR(WEEKDAY(A2,2)=5,WEEKDAY(A2,2)=6). It returns TRUE for Friday and Saturday and FALSE for the other days.

Why does my weekend formula return an error?

Most often, the referenced cell is not a valid Excel date, or the formula refers to an empty or text cell. Confirm that the source contains a real date value and that the day-number test matches the selected WEEKDAY return type.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.