Thread: Time and Conditional Formatting Problem

I have the time of 12:53 am in cell e7. E7 is formatted to be red if the value is greater than 11:26 p.m and green if equal to or less than 11:26 p.m. So, it should be red. This cell represents a start load time. In conditional formatting I have input 11:26 and the value is automatically changed to 0.976388888888889.

In cell e8 I have 12:59 am. It is similarly formatted , but with a time of 11:33. This cell represents an end load time.

In e9 I have 1:07 am. Similar formatted, but the time is 11:43 pm. This cell represents a time out.

In cell e11 I Have the formula e8-e7, i.e. How long did it take to load the truck?
In e12 I have the formula e9-e8., i.e. how long after the truck was loaded did it leave?

Time in all instances is selected from the format menu, cells, time and 1:30 p.m.

The cells all appear green, indicating the deadline was met. The deadlines were not met and the cells should appear red.

The only way I can get this to go to red is to change the values to PM and it looks stooped because anyone seeing this will know it wasn’t a PM time.

Thanks for any help.

Originally Posted by jonhunt

I have the time of 12:53 am in cell e7. E7 is formatted to be red if the value is greater than 11:26 p.m and green if equal to or less than 11:26 p.m. So, it should be red. This cell represents a start load time. In conditional formatting I have input 11:26 and the value is automatically changed to 0.976388888888889.

In cell e8 I have 12:59 am. It is similarly formatted , but with a time of 11:33. This cell represents an end load time.

In e9 I have 1:07 am. Similar formatted, but the time is 11:43 pm. This cell represents a time out.

In cell e11 I Have the formula e8-e7, i.e. How long did it take to load the truck?
In e12 I have the formula e9-e8., i.e. how long after the truck was loaded did it leave?

Time in all instances is selected from the format menu, cells, time and 1:30 p.m.

The cells all appear green, indicating the deadline was met. The deadlines were not met and the cells should appear red.

The only way I can get this to go to red is to change the values to PM and it looks stooped because anyone seeing this will know it wasn’t a PM time.

Thanks for any help.

Hi jonhunt,

Take a look at this link, it may help you

http://www.cpearson.com/excel/datearith.htm#AddingTimes

oldchippy

[QUOTE=jonhunt]I have the time of 12:53 am in cell e7. E7 is formatted to be red if the value is greater than 11:26 p.m and green if equal to or less than 11:26 p.m. So, it should be red. This cell represents a start load time. In conditional formatting I have input 11:26 and the value is automatically changed to 0.976388888888889.

in the above if it is only times 12:53am is < 11.26pm so should be green

because you have not got any days with the dates, which would make your conditions change 2am in the morning of the folowing day is < 11.30pm as the days are not recorded.

You need to rethink how you are recording time

Regards

Dav

Thanks. I hadn't thought of the need to use days, but I see the logic.