Forum Discussion

Hawwa1900's avatar
Hawwa1900
Copper Contributor
Jan 29, 2020

Gantt chart date formula

I have a row listing the dates of weeks commencing for each month.   I want the Gantt chart to fill each block if the date occurs in that week. At the moment, it won't fill if the start date isn't ...
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 29, 2020

Hawwa1900 

Use this one in stead. Then it works as you want it to, I believe. 

=AND(ISOWEEKNUM(J$6)>=ISOWEEKNUM($F7), ISOWEEKNUM(J$6)<=ISOWEEKNUM($G7))

Should a project carry over a year-end you probably need to work in the year also.

 

  • Kashibaba's avatar
    Kashibaba
    Brass Contributor
    Mar 06, 2020

    Riny_van_Eekelen Hi I want to use this for 57 weeks so how I can manage Year carry over.

    e.g start week of one task is 502019 and ends in 062020, for another task start is 062019 ends 052020.

    also any suggestion of working out to have both dates/days and weeks on same sheet where I can choose visibility option days or weeks.

     

    Thanks.

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Mar 06, 2020

      Kashibaba Can you upload the file with the example of a task carrying over a year-end and with the conditional formatting based on my latest response? Then I don't have to do it all over again, as I did not retain the file I worked on previously.

  • Hawwa1900's avatar
    Hawwa1900
    Copper Contributor
    Jan 29, 2020

    Riny_van_Eekelen 

    Thanks! What if I then want to change the colour depending on another cell value? 

    =ISNUMBER(SEARCH("Low risk",$C8))

     

    Do I just add this into the formula you gave?

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Jan 29, 2020

      Hawwa1900 

      Sorry, forgot to address the risk element. This one should work for "Low risk". You need separate rules for each risk category. Just replace the "Low risk" part and select you colour.

      AND(ISOWEEKNUM(J$6)>=ISOWEEKNUM($F8), ISOWEEKNUM(J$6)<=ISOWEEKNUM($G8),$C8="Low risk")

       

       

Resources