Gantt Chart

Here is the CF formula for Green, value A

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AND((INT((ROW()-1)/9))*3+INT((COLUMN()-COLUMN($F$1)+1)/8)+1=MONTH(F4),INDEX(Daywise!$6:$27,MATCH("A",Daywise!$A$6:$A$27,0)+1,MATCH(F4,Daywise!$5:$5,0))="A")[/FONT]

Thank you @BobPhillips. But not getting where to use this formula. It will be highly helpful if you kindly elaborate or upload as an example on my excel worksheet.

Use it as the CF formula in all oif the date cells in your calendar months.

Thank you so much sir,
But the same is not working for me.

Bob Phillips said:

Use it as the CF formula in all oif the date cells in your calendar months.

Click to expand...

Then you must have done it incorrectly, it works fine.

Wow, you are awesome sir, I will try to complete it now. Thank you so much. And will keep asking for your help if you don't mind.

Bob Phillips said:

Then you must have done it incorrectly, it works fine.

Click to expand...

lodhnerist said:

Wow, you are awesome sir, I will try to complete it now. Thank you so much. And will keep asking for your help if you don't mind.

Click to expand...

I appreciate your kind help, done it as you suggested and it is awesome. Now the only issue I am having is with my first month and the subsequent months of the calendar. If it is possible to select the calendar automatically from the start date of project, and continue for next 11 months automatically, then only the project can be visible. Otherwise, I have to select every month manually and apply conditional formatting for all the months up to 2049. Is it any how possible to do that?

View attachment Test duration 12.05.2020.xlsx

Its working fine when I am using the dates from Jan 2020 to Dec 2020 in the calendar but not working when I am changing the month and year as per my project duration. Unable to find out the fault. Please see the attachment for clarification.

My solution was predicated on the first month shown in the calendar is January. It wasn't designed tio work if you change the first displayed month to Feb, Mar, Apr, …

I could try and come up with the formula, watch this space.

Okay, thanks a lot.

Okay, here we go, I think this does what you want.

A few things to note:
- I have changed the month list on the Calendar sheet so that you only need to select the firts month, the rest calculate automatically
- I have removed the dates and stuff in AD:AE, they are no longer used so it removes clutter
- I have changed the formulae in F2, N2, V2, etc. to calcuklate full dates and format to your style
- I have changed the formulae in F3, N3, V3, etc. to use the header date and not those value in AD:AE
- I have changed the CF that hides the dates in each calendar that re are not of that month to have one all-encompassing formula rather than a different one per month
- and of course I have adjusted the formula for all of the value check CFs

There are two extra worksheets in this workbook, Scratch and SHeet2. I used these to help me work out the changes, just delete them when you use it.

BTW, the colour on the Calendar for value I is not the same as you are using DayWise.

Great Help Sir. Thank you a lot. One issue is there I think regarding the year, as it is taking the value of D1, hence after December 2020, again January 2020 is coming instead of January 2021. As a result of this formatting for J and K are not shown in the Calendar. Is there any fix you can kindly suggest for me?

Change the calendar header date formulae:

​