Conditional Formatting for Deadlines

[missyvette]

I have a spreadsheet that shows what date and time a job was submitted and the time that it is due. I would like to have a status field that uses the red circle x and green check mark icons to appear in the status field. I want a red circle if it is late, a yellow symbol if it is due within 60 minutes and the green check mark when we have 120 minutes or more left to complete the job.

My date and time field is formatted with the date format: 3/14/01 1:30 PM so that we can use the NOW function.

How do I write this function?

 

[pbornemeier]

Your problem statement does not describe the color for work due in 60-120 minutes.

Assuming you want red for late, yellow for 0-120 minutes and green for >120 minutes try the following:

If you are using Excel 2007 or later, you can use conditional formatting icon sets.
Highlight the block of cells containing the due time.
From the Home tab, select Conditional Formatting, Icon Sets, Indicators
Change the 'Format Style' to 'Icon Sets' and the 'Icon Style' to 'Custom' and each of the Types to Formula
Set the rules blocks as follows
1st: green check, >=, =NOW()+(2/24), Formula
2nd: yellow exclamation point, >=, NOW(), Formula
3rd: red X

Note you can select up to 5 different icons by scrolling to the bottom of the 'Icon Style' list then manually edit the resulting 5 interval definitions.