CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaproject-managementgantt-chart
fotoflo
fotoflo

Reputation: 967

Simple Gantt Charts in Google Sheets with Month to Month ranges

I was able to make the following gantt chart with the simple formula below, however i am having trouble adapting the formula to handle the case when the project lasts 3 months - you can see the example on row 4.

Gantt chart in google sheets

What i did was simply put a date Jul 1 2020, Aug 1, 2020, etc in row 1 and then in each row, i check the start and end against row 1 to see if its equal... if so, set the cell to true, and use conditional formatting to color it.

Now how would you check if the the row 1 date is in the range between start and end, and then set to true?

Thank you!

=ifna(
    ifs(
        AND(month($F2)=month(J$1), year($F2)=year(J$1)), true,
        AND(month($G2)=month(J$1), year($G2)=year(J$1)), true
    )
,"")

Upvotes: 0

Views: 967

Answers (2)

Jeff Rush
Jeff Rush

Reputation: 912

=IF(AND(
    C1 >= $A2,
    C1 <= $B2
    ),TRUE," ")

Test it here if you want: link to spreadsheet enter image description here

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34315

Just test if the month in the first row falls between the dates in the first two columns. So the custom formula would be:

=and(H$1>=$F2,H$1<=$G2)

enter image description here

Upvotes: 0

Related Questions