How to Apply Conditional Formatting by Weekday and Urgent Status in Excel
Question details
The user needs to apply conditional formatting to color-code rows or cells depending on the specific weekday, but only when a separate status cell indicates the task is Urgent.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visually organizing a schedule or task list by highlighting urgent items with a unique color for each specific day of the week.
- Observed behavior
- Cells need to receive a different color for each weekday only if the corresponding status is 'Urgent', while routine or non-urgent cells remain unformatted (white).
Ensure your date column contains valid numerical date values rather than plain text, and verify that your status column uses consistent spelling for the word 'Urgent'.
Use a Combined Formula with WEEKDAY and AND Functions
Create custom conditional formatting rules using a formula that simultaneously evaluates the day of the week and checks if the text status matches your criteria.
To satisfy two conditions at once, you must use the AND function. Inside the AND function, the WEEKDAY function will determine the day from your date cell (1 for Sunday, 2 for Monday, etc.), while an equals sign will check the status text.
Highlight the range of cells or rows you want the color formatting to apply to (for example, A2:E100). Ensure the active cell in your selection matches the row number you will use in your formula.
Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
Choose 'Use a formula to determine which cells to format'. Enter the formula =AND(WEEKDAY($A2,1)=2, $C2="Urgent") assuming column A contains the date and column C contains the status. This specific formula targets Monday.
Click the Format button, go to the Fill tab, and choose your desired color for Monday. Click OK twice to apply the rule.
Create a new rule for each subsequent weekday by changing the WEEKDAY number (e.g., 3 for Tuesday, 4 for Wednesday) and picking a different Fill color for each.
Use WPS Spreadsheet for Advanced Conditional Formatting
WPS Spreadsheet makes it incredibly easy to manage complex formatting rules like weekday tracking and status highlights. With full support for standard spreadsheet formulas and conditional formatting, you can visually organize your data with a few simple clicks.
- 1. Open your document: Launch WPS Spreadsheet and open your existing task management or schedule file.
- 2. Access the Formatting Rules: Select the data range you want to color-code, click the Home tab, and choose Conditional Formatting.
- 3. Set your custom formulas: Click New Rule, select the formula option, and input the AND/WEEKDAY logic exactly as you would in standard Excel.
- 4. Save your work: Customize your colors, click OK, and save your document seamlessly in .xlsx format.
Frequently Asked Questions
Why is my conditional formatting rule highlighting the wrong rows or cells?
This is almost always caused by an incorrect relative or absolute reference in your formula. Ensure your formula uses a mixed reference like $A2, and verify that the row number in your formula (e.g., row 2) matches the topmost row of your currently selected range.
What return numbers correspond to which days in the WEEKDAY function?
When using the standard WEEKDAY(serial_number, 1) syntax, 1 represents Sunday, 2 is Monday, 3 is Tuesday, 4 is Wednesday, 5 is Thursday, 6 is Friday, and 7 is Saturday.
Is the status check formula case-sensitive?
No. By default, a simple equality check in standard spreadsheet formulas (such as $C2="Urgent") is case-insensitive. It will successfully trigger the format whether the cell contains 'Urgent', 'URGENT', or 'urgent'.
