Growing Bar w/ changing color and cells the age (color indicated)

I want to be able to track the monitor the time-to-hire for the roles we have open as well as the candidates we have in process.

For roles, I want to be able to choose a Role Opened and Time to Hire Goal. (ex. Role opened: 4/12/19 and Time to Hire Goal of 45 days.

I was hoping to have a thin rectangle bar (not gantt) how many days the role has been open. As grows towards the 45 day mark it starts to change to yellow, wants it passes, it changes to red. and then a formula turns out the amount of days over the role is.

For candidates. I just want a “behind the scenes” Formula that slowly changes their cell’s color based on the time they’ve been in 1. Process and 2. How long in each stage

I’ll try to create a visual if this is not clear

Take a look here:

For changing rectangle’s color, you can simply put a SwitchIf(...) formula for Rectangle’s color parameter.

For candidates, I’m afraid there’s no way to “slowly change” cell background color. You can only make use of conditional formatting for now, which comes with limited color options. Alternatively draw a rectangle/circle with arbitrary color that can interpolate with better precision. But conditional formatting may be enough.