Need help to develop a Monthly Gantt schedule which is more complex in nature

Dear Experts,

Attached is the excel file which is am trying to develop as a High Level Gantt format for multiple projects. Each row represents a separate project which have different start dates and end dates. The requirements are stated below, which i hope can be achieved & this is where i need help to develop the Gantt.

In the attached file, I want each project row to be colored with some color (e.g. grey) for the entire duration of project. Additionally, I also need the Gantt to color each project row with 'green' color for the duration from start date to =TODAY() date.
So, essentially, whenever I open this Gantt, there would be 2 colors appearing on each project row. i.e. Grey color will automatically populate for the entire project duration & green color will be overlaid on this grey until today's date. Further, if the end date of project has passed by today's date, then only green color should appear for the entire project duration & not further.

The idea is to represent how much duration has been passed when viewed on any given date….Green indicates this duration until =today()

Next, in column E, I have put column header as 'FLAG", which has 2 options in drop-down, Y or N, If it is Y, then column F, header as 'FLAG MONTH' will be updated manually by giving the actual month reference which has some flag (i.e. any issue/alarm/risk etc...).Now, if this Column F is populated with any certain month, I need that specific month cell against that respective project row to be highlighted in LIGHT RED (our standard conditional formatting RAG color).
The column G, header as 'FLAG REMARKS' will also be updated manually to indicate what type of flag it is….(e.g., Utilization impact, Cost overrun, Resource risk, Delivery delay etc..)

Further, I was also looking to check if there is a way to archive the entire sheet for a specific month which has passed by. For e.g., Feb-24 last day is 29-Feb-24, so the sheet should be archived on a separate tab with name as Feb-24….so essentially, once a month is passed, all those months Gantts status should sit in separate tabs as archived & locked for editing.

This way, we can see how was the progress/flags in each of the respective months.

Hope i was able to explain myself clear. Kindly help with your suggestions/formulas.

Thanks,
Mahesh

Dear Experts,

Any suggestions on the above please? Kindly support. I am OK to skip the archive part (which i can do manually at end of every month). For other part, can someone help?

Thanks,
Mahesh

Try,
CF formula for entire project duration: =MEDIAN(EOMONTH($B6,-1)+1,$C6,H$5)=H$5
CF formula for Start Date to Today: =MEDIAN(MIN(EOMONTH($B6,-1)+1,TODAY()),MIN($C6,TODAY()),H$5)=H$5
CF formula for Flag Month: =AND($E6="Y",EOMONTH($F6,0)=EOMONTH(H$5,0))

Hi,

I have attached the file with mock-ups shown. Please see & suggest best way to automate the requirements shown.

Thanks,
Mahesh

No need for archiving. Just change the Schedule Start Date in cell B3 to a new month.

Hi Josephteh,

Thanks for the CF solution...It works well & serves the purpose. However, I just made few changes to the file (v1.0) attached herewith. Can you please tell me the CF formulas for this revised version. I am not a pro to formulate the CF rules hence asking if you can have a look at the attached file once & help me with the solution. Basically, i have now represented the Gantt in Weeks instead of Months (which i did in original file)

Also, can we apply a CF rule to highlight the current week (whenever we open the file).For this i have given the reference in cell D2 (Today's date) & in cell E2 as today's week. The week highlight should be seen in the respective week column. Plus, if we can add a slider to view the Gantt across columns, it would be great (in cases where the project duration is longer(1-2 years).

Hope this is possible...Waiting for your reply.

Thanks,
Mahesh

@Josephteh - Can you please look & help me with my above request with the revised version file? Have to submit this today...

Thanks,
Mahesh

CF formula for entire project duration: =AND(MEDIAN($B6,$C6,H$5)>=H$5,MEDIAN($B6,$C6,H$5)<IF($B$4="MONTHLY",(EDATE(H$5,1)),(IF($B$4="WEEKLY",(H$5+7),(IF($B$4="DAILY",(H$5+1),))))))

CF formula for Start Date to Today: =AND(MEDIAN($B6,$C6,H$5)>=H$5,MEDIAN($B6,$C6,H$5)<IF($B$4="MONTHLY",(EDATE(H$5,1)),(IF($B$4="WEEKLY",(H$5+7),(IF($B$4="DAILY",(H$5+1),))))),H$5<=TODAY())

CF formula for Flag Month: =AND(MEDIAN($B6,$C6,H$5)>=H$5,MEDIAN($B6,$C6,H$5)>EOMONTH($F6,-1),MEDIAN($B6,$C6,H$5)<IF($B$4="MONTHLY",(EDATE(H$5,1)),(IF($B$4="WEEKLY",(H$5+7),(IF($B$4="DAILY",(H$5+1),))))),H$5<=EOMONTH($F6,0))

CF formula for Today's Week: =H$3=$E$2

Hi Josephteh - This is wonderful work...the CF formulas work as expected. Many thanks for your efforts & prompt support

Thanks,
Mahesh

You are welcome, thanks for the feedback and Rep!