Forum Discussion

Carter March's avatar
Carter March
Copper Contributor
Sep 19, 2018

Weekly Gantt Chart using IF AND statement

(Yes, I realize that MS Project would be the more appropriate tool.. Let's get that clear.. Political, budget and user constraints - enough said!).    Attempting to create a very simple Gantt Chart in excel that simply populates a cell if the Start or End date fall into that weekly range, using =IF (And) logic.  

 

However, I am getting inconsistent results.  Not sure if a formula error or a bug.

I'm hoping to avoid blowing this up into a daily, and maintain a weekly grouping.  Adds for a nice filter each week to identify work that should be underway this week, etc.

 

My current formula is =IF(AND(AT$4>=$F6,AT$4<=$G6),"X","").  Basically, checking the date column (AT4) against the start (F) and end date (G), and return an "X" if accurate.  Then to make it look pretty, I'll use conditional formatting to hide the "X" and color fill the cell.

Thoughts?

  • Hi Carter,

     

    I guess your formula is incorrect. If, for example, we take one day Dec 03 or Dec 31, they both meet condition since BG4 is Dec 03 and BK4 is Dec 31.

     

    For Nov 15 (F20 and G20) BD4 is >= F20, but BD4 is not <=G20 and the formula returns FALSE.

     

Resources