Custom Sparkline Formula

How about this:

1. Insert Basic Sparkline

Use the built-in Sparkline feature:

• Select the cell where you want the sparkline.
• Go to Insert > Sparklines > Bar.
• Choose the data range (e.g., a helper row with calculated values).
• Choose the location range (where the sparkline will appear).

2. Create Helper Columns

Since Excel sparklines can't take arrays directly like Google Sheets, you’ll need to create helper columns to simulate the bar segments:

• Start Offset: =INT(D5)-INT($D$2)
• Duration: =IF(D5=E5,1,INT(E5)-INT(D5))
• Progress: =IF(TODAY()<D5,0,IF(TODAY()>E5,DURATION,TODAY()-D5))

You can then use these values to build a row of data that the sparkline references.

3.  Color Coding

Excel sparklines don’t support conditional colors directly. But you can simulate it using Conditional Formatting on the sparkline cell itself:

• Select the sparkline cell.
• Go to Home > Conditional Formatting > New Rule.
• Use a formula like:

=G5="Complete"

and set the fill color to grey.

• Add additional rules for "In Progress", "Not Started Yet", and overdue tasks.

4. Max Value

Set the max value manually in the Sparkline Tools:

• Click the sparkline cell.
• Go to Sparkline > Axis > Vertical Axis Minimum/Maximum Value Options.
• Set the max to =INT($E$2)-INT($D$2).