Sparkline Gantt Chart selecting earliest date from column and latest from another columns when i want individual dates to be displayed

Solution 1:

If I understood correctly, your objective is to accomplish the following behavior:

where the cell corresponding to that Date and Trade Number is colored with the respective buy/sell color if the date falls between Trade Date and Due Date.

This can certainly be accomplished with the SPARKLINE function, however, bear in mind that the SPARKLINE function will create a chart (you can read the documentation here). I presume that that is not what you want, as you would prefer the actual cells to be colored in.

That can be achieved with some conditional formatting (you can read the documentation about it here). In my example (int the G2 cell, the first of the Gantt Chart) I have set up two conditions; both with a G2:Q4 range, with the Format cells if in Custom formula is: and with the following formulas:

=AND(G$1>=$A2,G$1<=$B2,$E2="Buy")

and

=AND(G$1>=$A2,G$1<=$B2,$E2="Sell")

Of course, you can extend the range of the conditional and modify the colors of each condition.