Use IF, ISBLANK, and NOT to Return a Weekday Value in Excel
Question details
Return a specific value from a fixed cell (like X6) only when a target cell is blank and the corresponding day is a weekday (not Saturday or Sunday), otherwise return zero.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Evaluating multiple conditions to populate a spreadsheet conditionally based on empty cells and excluding weekend dates.
- Observed behavior
- Users need to nest logical formulas like IF, ISBLANK, NOT, AND, or WEEKDAY to accurately identify empty states and weekdays without triggering formula errors.
Verify whether your day column contains actual date values (e.g., 10/24/2023) or text strings (like 'Saturday' or 'Sunday'), as this dictates which formula approach to use.
Use IF, ISBLANK, and NOT for Text Day Names
Best used when your day column contains literal text strings like 'Saturday' and 'Sunday'.
If your dataset lists the days of the week as plain text, you can use the NOT and OR functions combined with ISBLANK to verify all conditions simultaneously.
Click on the cell where you want the conditional result to appear.
Type the formula: =IF(AND(NOT(OR($C327="Saturday",$C327="Sunday")),ISBLANK(X327)),X$6,0) into the formula bar. Replace C327 and X327 with your actual data cells.
Press Enter, then click and drag the fill handle at the bottom-right corner of the cell to apply this logic to the remaining rows.
Use IF, ISBLANK, and WEEKDAY for Actual Dates
The recommended approach when your column contains actual formatted dates instead of text.
Handling Formula-Generated Blank Cells
Use this method if the 'blank' cell actually contains a formula returning an empty string ("").
Evaluate Complex Logical Conditions with WPS Spreadsheet
WPS Spreadsheet seamlessly handles complex nested functions like IF, ISBLANK, NOT, and WEEKDAY. Process large datasets with multiple conditions effortlessly using this free, high-performance office suite.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your dates and values.
- 2. Enter the logical formula: Click the target cell and type your nested IF, ISBLANK, and WEEKDAY formula into the formula bar.
- 3. Calculate and drag: Press Enter to evaluate the result, then use the fill handle to drag the formula across your entire column.
Frequently Asked Questions
Why does ISBLANK evaluate to FALSE for seemingly empty cells?
If a cell contains a formula that returns an empty string ("") or hidden spaces, ISBLANK will read it as not blank. Use the syntax X1="" instead of ISBLANK(X1) to check for these pseudo-blank cells.
What is the difference between WEEKDAY return types?
By default, the WEEKDAY function returns 1 for Sunday and 7 for Saturday. If you add '2' as the second argument (e.g., WEEKDAY(A1,2)), it shifts the week so Monday is 1 and Sunday is 7. This makes it much easier to isolate weekdays by checking if the result is <=5.
Can I lock the reference to the output cell so it doesn't shift when copied?
Yes. You must use absolute cell referencing by adding dollar signs (e.g., $X$6). This tells the spreadsheet to always pull the value from exactly column X and row 6, regardless of where you drag or copy the formula.
