To highlight weekend dates in Excel, select the date cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =WEEKDAY(A2,2)>5. This assumes real Excel dates start in A2; choose a format, confirm, and Saturdays and Sundays in the selected range will be highlighted.
What the weekend formula does
Excel stores real dates as serial numbers, so a formula can determine the day of the week from a date value. WEEKDAY returns a number representing that day. Its second argument controls how the days are numbered: with a return type of 2, Monday is 1, Tuesday is 2, and Sunday is 7. The test greater than 5 therefore identifies Saturday and Sunday.
The reference A2 in the rule is deliberately relative. If the selected range begins at A2, Excel adjusts the reference for each cell as it checks the rest of the range: the next row is tested against A3, then A4, and so on. If your first date is in another cell, substitute that cell in the formula. The reference must match the top-left cell of the range you selected.
Conditional formatting changes how qualifying cells look; it does not change their values, convert text into dates, or remove rows. You can still sort, calculate with, and edit the underlying dates as usual.
Apply the rule step by step
- Select the date range, such as A2:A100. Leave out the header if row 1 contains a heading.
- On the Home tab, select Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter the formula using the first date cell in the selected range. For dates beginning in A2, use
=WEEKDAY(A2,2)>5. - Select Format, choose a fill, font, border, or number format, and select OK. Select OK again to create the rule.
To inspect or adjust the rule later in Excel for Microsoft 365 on Windows, use Home > Conditional Formatting > Manage Rules. Check the Show formatting rules for setting if the rule you expect is not visible. The Applies to field shows which cells the rule affects; the formula and that range need to agree.
Highlight weekends across entire rows
A schedule often has dates in one column and related details in other columns. To shade a whole row according to its date, select the full data area, for example A2:F100, and use a formula that fixes the date column but leaves the row number relative. With dates in A2:A100 and the selected rows spanning A:F, enter =WEEKDAY($A2,2)>5.
The dollar sign before A keeps the date check in column A as the rule moves across each row. The missing dollar sign before 2 lets Excel check A3 for the next row, then A4. If you lock the row as well, Excel may keep checking the same date for every row. If your dates are in column C, use $C2 instead, and make sure the selected range starts on the same row as the formula reference.
This is useful for staff rosters, delivery plans, and project timelines, where the date determines the formatting of several neighboring fields. It does not require copying the date into every column.
Prevent blank or invalid cells from being highlighted
The short rule is convenient when every selected cell contains a valid date. If the range includes blank cells or other values, use a check that only evaluates the weekday when the cell contains a number. Excel dates are numeric serial values, so this helps ignore blanks and text. With dates or possible blanks in A2:A100, and the rule applied starting at A2, use =IF(ISNUMBER(A2),WEEKDAY(A2,2)>5,FALSE).
IF returns FALSE for a blank or text cell, so that cell does not meet the formatting condition. This is also a useful safeguard when a list is expected to grow or has incomplete entries. It does not verify that every number is a meaningful date: ordinary numbers are numeric too, and Excel may interpret them as serial dates. Keep the source column limited to valid dates if that distinction matters.
A formula-based rule does not repair bad source data. If a date appears to be text, convert it to a real date before relying on weekday formatting. Depending on the data, you can re-enter the value, use Excel’s date conversion options, or import the column with the correct date format. Simply changing the cell’s display format may not convert text into a date value.
Choose a format and manage rule scope
Pick a style that remains readable alongside the workbook’s other formatting. A light fill is often easier to scan than a strong fill, particularly when the range contains text or values. The formatting is a visual cue, not a separate weekend category stored in the cell, so do not rely on color alone if the workbook will be printed in black and white or used by people who may not distinguish the colors.
When adding a rule to a range that already has conditional formatting, inspect the rule list. Multiple rules can apply to the same cells, and their order or Stop If True settings can affect which formatting is visible. A weekend rule can be correct yet appear to do nothing if another rule overrides the same fill or font setting. Manage Rules lets you review the order, conditions, and Applies to ranges.
For a growing list, you can apply the rule to a larger reasonable range or use an Excel table so new rows inherit table formatting behavior. Check the Applies to field after adding rows; an existing rule may not automatically cover cells outside its range. Avoid applying a formula rule to an unnecessarily huge area, since that can make a workbook harder to manage.
Adapt the rule for different weekend definitions
Saturday and Sunday are common weekend days, but not universal. The return type of 2 gives a simple way to choose a different day because Sunday is 7 and Saturday is 6. For example, to highlight Sunday only, with dates in A2:A100 and a rule beginning at A2, use =WEEKDAY(A2,2)=7. To highlight Saturday only, change the comparison to equal 6.
For a workweek with a different pair of weekend days, the WEEKDAY function can test the corresponding day numbers. As one example, Friday and Saturday are 5 and 6 under return type 2. With dates in A2:A100 and the rule beginning at A2, use =OR(WEEKDAY(A2,2)=5,WEEKDAY(A2,2)=6). Confirm the organization’s actual calendar before using a customized rule; some work schedules include exceptions, rotating days off, or holidays that cannot be identified from weekday alone.
Holiday highlighting is a separate condition. A holiday can fall on a weekday, and the weekend formula will not identify it. If a workbook needs both weekend and holiday formatting, create a separate rule for the holiday list or combine conditions deliberately, then review the rule order and visual styles so the meanings remain clear.
Common problems and how to fix them
- Every cell or no cells seem highlighted: Check that the formula refers to the first cell in the Applies to range. A range starting at A2 should normally use A2, not A1 or A3.
- The wrong day is highlighted: Check the WEEKDAY return type. The formula with 2 numbers Monday as 1 and Sunday as 7; other return types number days differently.
- Dates look right but the rule fails: The entries may be text rather than Excel date values. Test a known date or convert the source values, then reapply or recalculate the rule.
- Blank cells get a color: Use the guarded IF formula above, and verify that the rule’s Applies to range does not include unrelated cells.
- The chosen color is not visible: Another conditional-formatting rule may take precedence, or the fill may be difficult to distinguish from the existing cell style. Review Manage Rules and select a clearer format.
- The rule misses new rows: Extend the Applies to range or confirm the new entries are within the table or range covered by the rule.
One common misconception is that changing a cell to a date display format makes any text in it a valid date. Number formats affect display, while the conditional-formatting formula evaluates the stored value. If WEEKDAY encounters text where a date is expected, the problem is with the data, not the color selection.
Excel for Mac and Excel for the web
The formula logic is the same in Excel for Microsoft 365 on Mac. The ribbon layout or dialog wording can differ slightly by platform and update, but create the rule from the Home tab’s Conditional Formatting controls and choose the formula-based rule type. The formula must still point to the top-left cell of the selected range.
Excel for the web also supports conditional formatting, although its controls may appear in a side pane rather than the same desktop dialog. Select the range, open Conditional Formatting from Home, and create a rule based on a formula if that option is available in the current interface. If you cannot find the required rule controls, open the workbook in the desktop app to set or manage the rule. Opening or editing a workbook in the web version does not change the weekday logic.
Frequently Asked Questions
Can Excel highlight Saturdays and Sundays automatically?
Yes. Create a formula-based conditional-formatting rule using WEEKDAY with return type 2 and apply it to the date range. The rule updates when the date values change.
Can I highlight an entire row based on a date in one column?
Yes. Select the full row range and use an absolute column reference, such as $A2 when the date is in column A. Keep the row number relative so each row checks its own date.
Does the weekend rule work if dates are stored as text?
Not reliably. The rule expects Excel date values, which are numeric serials. Convert text dates into actual dates before using WEEKDAY-based formatting.
Can I highlight only Sundays?
Yes. With return type 2, Sunday is day 7. Use a formula that tests for 7, and set its cell reference to the first date cell in the selected range.
Will the highlighting change when I edit a date?
Yes. Conditional formatting recalculates from the cell value. If a date is changed from a weekday to a weekend, the rule changes the cell’s appearance accordingly, provided the cell remains within the rule’s Applies to range.
Related
- Close Excel Shortcut: Windows and Mac Keys
- Checklist Template Excel: Set Up, Track, and Reuse Tasks
- Excel Drop-Down Lists: How to Create and Manage Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Date Difference: Days, Workdays, and Inclusive CountsTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- How to Share a Calendar Event in Microsoft OutlookTo share a calendar event in Outlook, create an invitation and add the people who should receive it,…
- Oracle SQL Text to Columns: Split Delimited DataTo split delimited text into separate columns in Oracle SQL, use INSTR to find delimiter positions and SUBSTR…
- Microsoft Word Watermark: Add It to Every PageYes. In Microsoft Word for Microsoft 365 on Windows, select Design > Watermark and choose a preset, or…
- Why Did My Outlook View Just Change?Your Outlook view usually changes because a display setting was switched, a different folder has its own view,…
- Excel Data Table Shortcut: What It Does and How to Use ItExcel has no single universal keyboard shortcut for the What-If Analysis Data Table command: in Windows, choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.