Status field in Excel from multiple columns

[TanjaO]

Hello, 

I have a dilemma. I have a spreadsheet with 3 different columns with following information: start date, apply from, end date. In a 4th column I want a color depending on the information of the other columns. 

If the date in any of the columns are today -10 days the color should be green, If today -5 day the color should be yellow, If today it should be orange. If the date is today or today +5 days should be red. Anything outside of this should give no color. 

I can't figure out what kind of formula I can use to get this resoult if at all possible. 

Any help or hint is much appreciated 

[#2 · Accepted solution]

@TanjaO 

See the attached workbook.

[#3]

@Hans Vogelaar 

That looks exactly what I am after. But how do you get the colouring? I can't see any formulas in the cell

[#4]

@Hans Vogelaar 

Ops! I found it. Thank you so much for the quick reply.