Dynamic progress bargraph showing time and unconditional formatting

**Mapexz** · 07-06-2015, 03:50 AM

Hello everyone,

For my graduation assignment i'm working on a management dashboard for construction projects. I've read a lot about unconditional formatting for progress bars but i'm looking for an extension of that function and can't figure it out by myself. Following are some examples of what i am trying to get:

Critical Performance bar 2.png Critical Performance bar 1.png

The idea is that a horizontal line on the bar represents the current date (=TODAY function or from MS Project). The bar shows the amount of work (based on a percentage of the total) that has been done till this date. When the distance/space between the current date and the progress becomes too big, the bar will show a yellow or red color (critical). When the current date equals progress the bar shows a green color.

Does anyone know if this is possible or knows of an alternative solution? Help would be much appreciated! Thanks in advance.

**Kaper** · 07-06-2015, 04:39 AM

As a general suggestion - you probably can use stacked bar graph type. with 3 separate series for each colour.
and each series data biult around simple function:

Formula:

Please Login or Register  to view this content.

now if 3 such bars are stacked - only the one with condition met is shown other 2 are 0 height (so only appropriate colour is visible)

**Mapexz** · 07-06-2015, 04:59 AM

Great tip, thank you. I would prefer the stacked bars to change color due to the comparison with time. Maybe =IF(TODAY()<10%(deadlinedate);(greencolor);0)

If there is no way to make unconditional formatting with a time comparison i have to use your method with the stacked bars.