Time formula to deduct two times

**salvo69** · 10-30-2012, 08:28 AM

I am looking to create a formula to work out the difference between a deadline time and the current time, if the deadline has passed I want to show as red, within an hour of the deadline amber and greater than an hour before the deadline green

**jeffreybrown** · 10-30-2012, 09:00 AM

See if this works for you.

**salvo69** · 10-30-2012, 09:11 AM

Hi, I am looking for a seperate cell which states RED, AMBER or GREEN

I have the time (NOW) in one static cell and various deadlines and I want to RAG the cells next to each deadline

I.e. 13:09 (now) 14:00 (deadline) and "RAG Status. My current formula is trying to update a cell saying RED etc (I have done conditional formatting to change colour automatically).

Here is my formula that will not work "=IF($I$9>0,"RED",IF($I$9<-1-0,"AMBER","GREEN"))

**salvo69** · 10-30-2012, 09:14 AM

Sorry I am using Excel 2003

**jeffreybrown** · 10-30-2012, 09:33 AM

Good idea to put your excel version in either your profile (like mine) or in your sig block.

Sorry I blanked out and went straight to conditional formatting for some reason.

=IF($B$2<$A$2,"RED",IF(AND($B$2>$A$2,$B2<TIME(HOUR($A$2)+1,MINUTE($A$2),SECOND($A$2))),"AMBER",IF($B$2>TIME(HOUR($A$2)+1,MINUTE($A$2),SECOND($A$2)),"GREEN")))

A2 = 13:09
B2 = 14:00