How can I change the fill of a shape based on dates?

**sweetonyx** · 07-12-2022, 06:25 PM

Hello! I am trying to use Microsoft Excel to create a sheet that has a table and a long rectangular bar shape that corresponds to each row of the table. This table will have 2 columns: One for the Name and one for the Start Date. I want to configure this sheet so that any time someone changes the start date, it is reflected in the fill of the shape. For example, if someone changes the date for "Party" to July 2nd (approximate midpoint of the year), then I want the shape that corresponds to "Party" to be 50% full. Is there a way to do this in excel? Or should I be using something else. Any insight is greatly appreciated :)

**Richard Buttrey** · 07-12-2022, 07:01 PM

You could do this with a macro.
Is it vital you use a bar shape? Conditional formatting on say a series of 10 adjacent cells that look like a bar could also be used.

**sweetonyx** · 07-12-2022, 07:04 PM

Thanks for the reply! No, not necessarily a bar shape. But I don't want the bar to be super large where I need to zoom out a lot to see the whole thing.

**JeteMc** · 07-14-2022, 04:00 PM

I believe that what Richard is proposing would be something like what is displayed in the attached file.
1. The conditional formatting formula for the 10 cells is: =COLUMNS($C1:C1)<=$N1
2. Cell N1 is populated using: =DATEDIF(P1,B1,"d")/DATEDIF(P1,Q1,"d")*10
3. Cells P1:Q1 contain =DATE(YEAR(TODAY()),1,1) and =DATE(YEAR(TODAY()),12,31).
Note that cells N1, P1:Q1 could be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.