Google Sheet sparklines to track progress of projects within a fiscal year

Question

I want the cell the sparkline is in to visually represent the fiscal year 7/1/2020-6/30/2021 and to show a bar within it that demonstrates when during that year a project will begin and end. So, say the project starts on 1/1/2021 and runs through 6/30/2021, the bar would fill in the right half of the cell.

(The bar will be colored to represent whether or not the project is on track, but I have that part figured out).

Where I'm running into trouble is when a project started prior to this fiscal year, or runs beyond it. In those cases, I just want the bar to be solid across the full cell, to show that the project will be going on all year long.

Here's roughly what I'm looking at: '=SPARKLINE({INT(projectStart)-INT(fiscalYearStart), INT(projectFinish)-INT(fiscalYearFinish)},{"charttype","bar";"color1".....; "max",INT(fiscalYearFinish)-INT(fiscalYearStart)})

Say a project began back in January 2020, the bar isn't starting at the left of the cell - it's starting a bit farther to the right, which I don't like. I'd like to pretend it started on 7/1/2020 for visualization purposes.

I am tracking a long list of projects across the fiscal year, so it will be apparent if some of them display incorrectly.

Answer 1

I've taken the liberty of putting the FY start/end dates into cells L7, and M7 respectively.

Your problem can be solved with three colors/segments in the bar:

=SPARKLINE({MAX(G9-L$7,0),H9-G9,MAX(M$7-H9,0)},
{
  "charttype","bar";
  "color1","white";
  "color2",if(E9="Complete","darkgreen",if(today()>H9,"darkred",if(AND(today()>G9,today()<H9,E9="Behind Schedule"),"orange",if(AND(today()>G9,today()<H9),"lightgreen",if(E9="On Track","lightgreen","gray")))));
  "color3","white";
  "max",M$7-L$7
})