Formatting appearance of Time Values

[WildWill]

Hi

I have a cell with data, formatted in hh:mm:ss. If the value in that cell is
less than 24:00:00, then I want it to appear with a green format (i.e. the
cell is filled with green colour and the text remains black), if the value is
greater than 24:00:00. then I want a similar effect but with the cell filled
in red colour. I am not getting this right with Conditional Formatting.
Please help.

 

[Desperate]

Hey Will there are only 24 hours in a day, and it will usually appear as
00:00:00 not 24:00:00. I think that if you try to type in an hour larger than
24:00:00, like 26:00:00 it will just read as 2:00 am. Long story short you
can do this with conditional formatting but not using the greater than and
less than. You'll have to use between. For example, to make cells green
between midnight and noon use the drop down menu for between, and type in
00:00:00 and 12:00:00.

 

[WildWill]

Hi Desperate

Oh dear - my database has just taken a couple of steps back. I was going to
reply to you by saying that the value (in this case) could exceed 24 hrs,
since it is the difference between two dates, and thus not a "time-of-day"
value. (e.g. the number of working hours between Monday & Friday) - but I
have just seen that it does not in fact calculate like that. I will have to
take some steps back and apply a different formula for the calculation as
described above, before I get to the conditional formatting bits. Just a note
however - regardless of the type of time data, when I try and enter a
hh:mm:ss value into the conditional formatting field, it just returns "1",
and does not seem to retain the hh:mm:ss format of the input.

 

[David Biddulph]

You've said "I am not getting this right with Conditional Formatting", but
you haven't told us what conditions you have tried to set in CF, nor what
your problem is. What value is in the cell, what result did you get, and
what result did you expect?

CF/ Cell Value Is/ less than 1 should do the job.
You will realise, I hope, that with a cell formatted as hh:mm:ss it will
always be *displayed* as less than 24:00:00. If you want to see values
beyond 24:00:00 you need to format as [hh]:mm:ss, but the format for the
hours display doesn't affect the value in the cell, and hence doesn't affect
the result from CF.

 

[WildWill]

Hi David & Desperate

In the end, I reverted back to calculating working days instead of trying to
calculate working hours between the two dates. In this way I get a numeric
value, i.e. "3" (working days) and this is easy to regulate in CF. Thanks a
stack for the advice. Awesome Forum - Awesome Assistance!

You've said "I am not getting this right with Conditional Formatting", but
you haven't told us what conditions you have tried to set in CF, nor what
your problem is. What value is in the cell, what result did you get, and
what result did you expect?

CF/ Cell Value Is/ less than 1 should do the job.
You will realise, I hope, that with a cell formatted as hh:mm:ss it will
always be *displayed* as less than 24:00:00. If you want to see values
beyond 24:00:00 you need to format as [hh]:mm:ss, but the format for the
hours display doesn't affect the value in the cell, and hence doesn't affect
the result from CF.
--
David Biddulph

Hi

I have a cell with data, formatted in hh:mm:ss. If the value in that cell
is
less than 24:00:00, then I want it to appear with a green format (i.e. the
cell is filled with green colour and the text remains black), if the value
is
greater than 24:00:00. then I want a similar effect but with the cell
filled
in red colour. I am not getting this right with Conditional Formatting.
Please help.