Excel 2007 : Notification time Plus - real time tracking PAST midnight with Conditional formatting

My problem has more than likely been addressed before. What's going on is I have a Notification Time (NT). Say it's 22:00. I have specific actions to complete from NT to End Time (ET). (Excel book attached) I have it conditionally formatted to fill green when the real time tracking (time input as actions start and stop) is less than or equal to (<=) the start or stop time. So, start time for action 1 is 22:00, actual start time is 21:45. The cell fills green. If the actual input time is greater than 22:00 it fills red. So, 22:00 start time, 22:30 actual will fill red. The problem arises when I go past midnight, the formatting recognizes anytime after 0:00 as the same day, thus filling it green. But only when the time passes the required stop time. Example, NT is still 22:00. Action requires start time of 22:00 and a stop time of 0:00. If ITEM 1 does not finish action 1 in time, and goes past the 0:00 required stop time, you would fill it in, say it was 15 minutes late, 0:15. This would mean the cell fills red right? NO. Because excel sees only 1 day. the same day for all times. So you see my dilemma. Please help if you know a way around this while keeping the formatting of time 00:00. Thanks in advance to everybody that takes the time,

Dave

Notes:
Microsoft Excel 2007, (Office Home and Business)

The only way I know of is to add the 24 hours when typing your data... Instead of 23:50, enter 47:50. With that entry, your conditional formatting will work, but it's will display confuse anyone who doesn't know what the underlying formatting is doing. Formatting, like statistics, can lie... Good luck with your boss.

Originally Posted by dangelor

The only way I know of is to add the 24 hours when typing your data... Instead of 23:50, enter 47:50. With that entry, your conditional formatting will work, but it's will display confuse anyone who doesn't know what the underlying formatting is doing. Formatting, like statistics, can lie... Good luck with your boss.

Thanks, I have thought about this and unfortunately they told me no, it needs to be dummy proof. I appreciate the timely response though. Anybody else have any ideas? Maybe this will require a degree of VBA?

Bump for a solution?

Possibly by breaking the time down?

=1+23/24+50/1440 will display as 23:50