Creating a Gantt Chart in Excel using conditional formatting

[HACZN12]

To Whom itConcerns,

I’m trying to create a Gantt chart which would look something like this: https://www.youtube.com/watch?v=00s5efQWrgU&t=41s

However I’ve designed mine around projects rather than tasks, and each projecthas a start, forecast completion and actual completion date.
The linedisplayed should be
Green if bothStart and Actual Completion dates have been provided; Currently I’ve puttogether the following formula:
=AND(M$4>=$E6,M$4<=$G6+6)
Blue if Startand Forecast Dates have been provided, and the forecast date is after or in thefuture from TODAYs date (and no actual date has been provided) ; Currently I’veput together the following formula:


=AND(M$4>=$E6,M$4<=$F6+6,$G6=0)
Red if Start and Forecast Dates have been provided, and theforecast date is prior to TODAYs date (and no actual date has been provided) ; Currently I’ve puttogether the following formula:
=AND(M$4>=$E6,M$4<=TODAY()+6,TODAY()+6>=$F6,$F6>0,$G6=0)
I wanted to dothis via conditional formatting but I’m also open to having a nested ifstatement which merges all three of these statements and displays “1” if the lineis supposed to be green, 2 if the line is supposed to be Blue, 3 if the line issupposed to be red. I can then base the conditional formatting upon theninstead.
And No Line ifno forecast or actual date has been input.
At the momentonly the green line works correctly, the other two lines (red and blue) don’t seemto appear where applicable..

Any Help wouldbe very much appreciated.

Let me know ifyou require more info,

Many Thanks

HaczN12