Gantt Chart - Months & Quarters

[mindofone]

I'm trying to construct a Gantt chart using conditional formatting. However, I am working with a data range in months on the 'Y' Axis, and columns that indicate Quarters.

I get that I can construct an 'If And' formula to match my monthly date range to a specific Quarter, but am running into trouble when my monthly date range falls into two or more quarters.

For example, I have a project component that begins in February 2015, and ends in June 2015. Along the 'X' axis, my conditional formatting should highlight those columns that match Q1 2015 and Q2 2015. My simple 'If And' formula doesn't work because the date range exceeds one quarter.


Any thoughts?

<colgroup><col width="53"><col width="52"></colgroup><tbody>
[TD="class: xl64, width: 52"][/TD]

</tbody>

 

[pjmorris]

The answer to this is, as you've found, more complicated than it at first appears. There are four circumstances in which a column should be highlighted:

1. The activity occurs entirely within the qtr: Act_St_Date >= Qtr_St_Date AND Act_Fn_Date <= Qtr_Fin_Date
2. The activity entirely spans the Qtr.
3. The activity starts within the Qtr.
4. The activity finishes within the Qtr.

You could test these conditions, but a better solution is probably to highlight the Qtr unless the activity finish date occurs before the qtr start or the activity start occurs after the Qtr finish. I used this formula:

=OR($A$3>EOMONTH(DATE(2000+RIGHT(D$2,2),3*MID(D$2,4,1),10),0),$B$3<DATE(2000+RIGHT(D$2,2),3*MID(D$2,4,1)-2,1))

with A3 having the activity start date, B3 the activity end date and qtrs. were in row 2 in this format 'Qtr1-14', 'Qtr2-14' etc

Hope this helps a bit.

 

[mindofone]

Wow, your solution is completely opposite of how I was going after the date check. Very elegant!

I am using a start date (in this example - 01/01/2015) and then proceed to calculate the quarter end date based on the start date, followed across by the next quarter star & end dates, etc., spanning the entire gantt chart time period.

Next Quarter State Date: =DATE(YEAR(F1),LOOKUP(MONTH(F1),{1,4,7,10},{1,4,7,10})+3,1)
Next Quarter End Date: =COUPNCD(G1-1,DATE(9999,1,0),4)

Then I simply use the conditional formatting you outlined above (although without the RIGHT and MID formulas):
=IF(OR($D7>F$2,$E7<F$1),0,1)
Where the Start and End dates are in columns D and E respectively, and the Quarter Start and End dates are in rows 1 and 2.

Thank you ever so much for your help Peter!

 

[pjmorris]

Glad it was of some help. Thanks for the feedback.

Regards