Gantt Chart with 4 dates instead of usual 2

Hi All,

Need some help building what's essentially a Gantt chart in excel from scratch, but with four dates instead of the traditional two (start/end).

The goal is to show a timeline for multiple projects, each with 4 milestone dates, color coded, and have the timeline populate cells with color based on if that cell falls on or after a given milestone date until the next cell falls on or after the subsequent milestone date.

What I'm having trouble with is coming up with a formula that can check the date value a given column has, and output the name of a milestone in a corresponding cell, but then switch the milestone output once the column's date value falls on or after the next milestone.

I've attached a simple file which better details what I'm looking for.

If the context helps, I'm just trying to show a timeline for projects and compare budget to forecast.

Any help would be greatly appreciated as this is time-sensitive. Thanks!!

You will need another column of dates to signify the end of Milestone 4 for each project, otherwise Milestone 4 will propagate for ever. Suppose your use column H for this End date, then you can use this formula in I6:

=IF(AND(I$1>=$D6,I$1<$E6),$D$5,"")&IF(AND(I$1>=$E6,I$1<$F6),$E$5,"")&IF(AND(I$1>=$F6,I$1<$G6),$F$5,"")&IF(AND(I$1>=$G6,I$1<$H6),$G$5,"")

Copy this across and down as required, and then apply your Conditional Formatting to the whole block.

Hope this helps.

Pete