Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Cell

**Alan L 185** · 11-18-2022, 09:53 PM

Hi

I?m trying to set up a ?traffic light? system to balance the average time allocated for Teacher Aides per day (H13) with the funded time per day (H2).
In the example attached, the average time allocated per day is 48 hours 25 minutes (H13) compared with the funded time per day which is 30 hours 25 minutes (H2).
I want to set up the worksheet to show that when the time in H13 is greater than that in H2, then H13 is coloured red and when the time in H13 is less than or equal to that in H2 I want H13 to be coloured green.
I?ve set up the follow conditional formatting formulae in H13:
= $H$13>$H$2 fill H13 with red colour.
=$H$13<=$H$2 to fill H13 with green colour.

However, it doesn?t work???
Can someone please help me by solving this mystery?

AlanL185

**6StringJazzer** · 11-18-2022, 10:20 PM

The problem is that the content of H2 is text. You are building it with the TEXT function. Use this formula instead to make it a date/time value.

Formula:

Please Login or Register  to view this content.

See attached.

**Crooza** · 11-18-2022, 10:28 PM

Change your formula in H2 to

=INT(F2)/24+MOD(F2,1)/24

and it should work

**Alan L 185** · 11-18-2022, 10:42 PM

Thanks Jeff. It works well. All the best from NZ.

AlanL185

**Alan L 185** · 11-18-2022, 10:44 PM

Thanks Crooza. That works well. All the best from NZ

**6StringJazzer** · 11-19-2022, 10:39 AM

Originally Posted by Crooza

Change your formula in H2 to

=INT(F2)/24+MOD(F2,1)/24

and it should work

This is mathematically equivalent to

Formula:

Please Login or Register  to view this content.

**Crooza** · 11-19-2022, 08:36 PM

Yeah I know. I initially thought the decimal part had to be divided by 60 and the integer part by 24 but the answer looked wrong and I then realised both are divided by 24. Rather than retype full formula I changed the second part to 24 and didn’t bother changing the full formula to the simplified version you posted.

Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Cell

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Cell

Re: Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Ce

Re: Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Ce

Re: Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Ce

Re: Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Ce

Re: Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Ce

Re: Conditional Formatting - Time (Hours&Min) Greater Than or Less Than Time in Another Ce

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting using hours/time

Conditional Formatting - time,hours,days

[SOLVED] Conditional format Date/time that is 16 hours greater or Less

[SOLVED] Conditional formatting: TIME greater then...

Auto summing hours and minutes.Conditional formatting cell with the sum of time attendance

Conditional formatting for dates/time x hours passed

time:How can I enter a figure for contracted hours which is greater than 24 hours?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions