How to Combine Multiple IF Formulas in an Excel Gantt Chart
Question details
Combine multiple IF formulas to accurately reflect different date ranges in a Gantt chart timeline while ignoring blank input cells.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a project timeline where specific cells in the Gantt chart must return distinct values (like 3 or 4) depending on two separate sets of start and end dates, without generating errors for empty rows.
- Observed behavior
- The user needs a consolidated formula for the timeline range (E2:AG2) that successfully evaluates overlapping or sequential dates from input columns (A2:D2) while cleanly handling blank entries.
Ensure that your timeline headers (e.g., E1:AG1) contain valid date serial numbers and your start/end date columns (A2:D2) are formatted consistently as dates.
Use Nested IF with OR and AND Logic
This recommended approach explicitly checks for blank source cells first, then evaluates the two date ranges using standard logical AND/OR functions.
This formula uses the OR function to verify if the primary date cells are blank. If they are, it returns a blank space to keep the chart clean. If valid dates exist, it evaluates the timeline date against the project start and end dates.
Click on cell E2, which represents the first date in your Gantt chart timeline.
Type the following formula into the formula bar: =IF(OR($A2="",$C2=""),"",IF(OR(AND($A2<E$1,$B2>E$1),AND($A2>E$1,$A2<F$1)),3,IF(OR(AND($C2<E$1,$D2>E$1),AND($C2>E$1,$C2<F$1)),4,0)))
Press Enter, then use the fill handle (the small square at the bottom-right corner of the cell) to drag the formula across to column AG, and then drag it down to fill all your project rows.

Use Boolean Multiplication Logic for a Concise Formula
Use boolean multiplication instead of nested AND functions to shorten the formula while achieving the same Gantt chart scheduling criteria.
Create Professional Gantt Charts Easily with WPS Spreadsheet
WPS Spreadsheet provides comprehensive formula support and powerful conditional formatting, making it the perfect tool for building dynamic Gantt charts and managing complex project timelines.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new Spreadsheet or load your existing project timeline file.
- 2. Apply your formulas: Input your nested IF logic into the timeline cells just as you would in Microsoft Excel.
- 3. Add Conditional Formatting: Highlight your timeline range, go to Home > Conditional Formatting, and set rules to color cells containing '3' or '4' to visually generate your Gantt bars.

Frequently Asked Questions
Why does my IF formula return a #VALUE! error in my Gantt chart?
A #VALUE! error usually occurs if the formula is trying to compare a date with text. Ensure that your timeline headers and project start/end cells contain actual date values, not text strings that look like dates.
Can I use conditional formatting instead of numbers (3 and 4) to show the chart?
Yes. The numbers 3 and 4 are used as triggers. Once the formula populates these numbers, you can select the range, go to Conditional Formatting > Highlight Cells Rules, and format cells equal to 3 or 4 with specific background colors. You can also format the font color to match the background color so the numbers disappear visually.
Is there a limit to how many IF conditions I can nest?
In modern versions of Excel and WPS Spreadsheet, you can nest up to 64 IF functions. However, for complex Gantt charts with many conditions, using IFS functions, boolean logic, or reference tables with VLOOKUP is recommended for better readability and performance.
How do I exclude weekends from my Gantt chart formula?
To exclude weekends, you can integrate the NETWORKDAYS function to calculate working days, or use the WEEKDAY function within your IF logic to return a blank or gray cell if the timeline header date evaluates to a Saturday (7) or Sunday (1).




