CODEWITHSUNDEEP
excelconditional-formattinggantt-chart
SuavestArt
SuavestArt

Reputation: 217

Gantt chart on Excel - Conditional Formatting based on the number of days for a task which are in a given month

I'm trying to build a Gantt Chart in Excel where I could conditionally fortmat cells to reflect the number of days a task has on a given month.

For example, today is 24-oct-19. A task starting on 11-oct-19 and ending on 08-nov-19 has 20 days on October and 8 days in November. I need to conditionally format a cell on the task row, October column, to be painted in a proportion given by 20/31. I should format similarly the same row, November column.

None of the many Gantt charts I found online could make it. Could you please give advice on how could I do this?

I'm also attaching a sketch.

Thank you!

Gantt chart with next 12 months

Upvotes: 0

Views: 356

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34315

Just a proof of concept (not very aesthetically pleasing because of padding round cells, but this can be fixed).

I just wanted to see if you could get a reasonable chart using basic string functions to get a bar in the right position in each cell:

=IFERROR(LEFT($A$5,ROUND((MAX(D$1,$B6)-D$1)/DAY(EOMONTH(D$1,0))*10,0))&LEFT($A$3,ROUND((MIN($C6,EOMONTH(D$1,0))-MAX(D$1,$B6)+1)/DAY(EOMONTH(D$1,0))*10,0)),"")

enter image description here

Slightly more pleasing appearance - the trick is to centre the text in the cell, but then you need to include spaces to the right where necessary (e.g. in cell E6) to make it work:

=IFERROR(LEFT($A$5,ROUND((MAX(D$1,$B6)-D$1)/DAY(EOMONTH(D$1,0))*10,0))
&LEFT($A$3,ROUND((MIN($C6,EOMONTH(D$1,0))-MAX(D$1,$B6)+1)/DAY(EOMONTH(D$1,0))*10,0))
&LEFT($A$5,ROUND((EOMONTH(D$1,0)-MIN(EOMONTH(D$1,0),$C6))/DAY(EOMONTH(D$1,0))*10,0)),"")

enter image description here

To get fixed character size, I have set the area where the bars are to use Courier font, and chosen a suitable column width.

Upvotes: 1

Related Questions