Conditional formatting with date calculations

[Thinker8]

Hi All,

I have two date columns named "start_date" and "Dead_line".

Both the columns are in the date format. Ex: 9/14/13 9:14 AM

i want the third column to be the "status" column.

if sysdate-dead_line < 30 % of (dead_line - start_date), then the status column should become green.
if sysdate-dead_line is between 30 % and 70 % of (dead_line - start_date), then the status column should become amber.
if sysdate-dead_line > 70% of (dead_line - start_date), then the status column should become red.


First the dead_line - start_date to be converted to hours, then have to do conditional formatting like mentioned above.


Pleae help to crack it...

Thanks in advance.

[FDibbins]

I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that

[Thinker8]

i have attached the sample sheet.