Help with Conditional Formating Time

[jonhunt]

I have various deadlines and I have formatted my cells to have a Green background if the deadline is met. So the first condition is "Less than or Equal to." The cell turns red if the deadline isn't met, so the condition is "Greater than."

I would type in 12:51 as the conditional value and not worry about a.m or p.m. The numbers are converted to something like 0.03546666.

Things have been going along great until I got into early a.m. times, but now I'm having problems. If I type in 12:51 as the conditional value and 11:20 as the actual I should get a green background, but I'm getting red.

It doesn't seem as if AM or PM should make a difference in the outcome, but something strange is going on. I tried formatting the actual time as PM, but that didn't make a difference.

Thanks for any help.

[Sandy Mann]

"jonhunt" <[email protected]> wrote in
message news:[email protected]...

> I would type in 12:51 as the conditional value and not worry about a.m
> or p.m. The numbers are converted to something like 0.03546666.

Excel is therefore interpreting your entry as 12:51 AM therefore 11:20,
whether A or PM is later,(greater), then your first entry

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"jonhunt" <[email protected]> wrote in
message news:[email protected]...
>
> I have various deadlines and I have formatted my cells to have a Green
> background if the deadline is met. So the first condition is "Less than
> or Equal to." The cell turns red if the deadline isn't met, so the
> condition is "Greater than."
>
> I would type in 12:51 as the conditional value and not worry about a.m
> or p.m. The numbers are converted to something like 0.03546666.
>
> Things have been going along great until I got into early a.m. times,
> but now I'm having problems. If I type in 12:51 as the conditional
> value and 11:20 as the actual I should get a green background, but I'm
> getting red.
>
> It doesn't seem as if AM or PM should make a difference in the outcome,
> but something strange is going on. I tried formatting the actual time as
> PM, but that didn't make a difference.
>
> Thanks for any help.
>
>
> --
> jonhunt
> ------------------------------------------------------------------------
> jonhunt's Profile:
> http://www.excelforum.com/member.php...o&userid=35370
> View this thread: http://www.excelforum.com/showthread...hreadid=561199
>

[jonhunt]

I have entered 12:51 as the conditional value, so I suspect the problem must be in how the entered values for the deadline and actual times are formatted. They obviously have to be formatted as times and I've tried all sorts of ways, but it isn't working.

[Sandy Mann]

Jon,

If I understand what it is you are saying then you entered 12:51 in the
conditional formatting dialog box and it turned into 0.03546666 which is
equivelant to 00:51. All times entered that are after nine minutes to one
in the morning will therefore be greater then the conditional time thus will
turn red.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"jonhunt" <[email protected]> wrote in
message news:[email protected]...
>
> I have entered 12:51 as the conditional value, so I suspect the problem
> must be in how the entered values for the deadline and actual times are
> formatted. They obviously have to be formatted as times and I've tried
> all sorts of ways, but it isn't working.
>
>
> --
> jonhunt
> ------------------------------------------------------------------------
> jonhunt's Profile:
> http://www.excelforum.com/member.php...o&userid=35370
> View this thread: http://www.excelforum.com/showthread...hreadid=561199
>