Conditional formatting issue

[pghhdh]

Hi all! I've hit something that's stumping me and need some help, please. I've attached a mini-sheet for reference! I am tracking 2 types of task times as h:mm:ss. One is the maximum amount of time it takes for a task, the other is the actual time it takes. Below them I have a formula showing how far over or under the maximum amount of time the task took. If the resulting number shows an overage, I want to turn the text red. I think part of my issue is how I have the formula for the 3rd row written. I appreciate any help!

Productivity Timing.xlsm
	A	B	C	D
3	Time per unit	13	4	6.5
4	Max Time	3:02:00	0:12:00	0:58:30
5	Actual Time	2:07:47	0:08:30	0:41:32
6	Over/Under	0:54:13	0:03:30	0:16:58
7	Actual Count	14	3	9
8		0:08:59	0:03:41	0:03:05
9		0:09:30	0:02:48	0:05:09
10		0:08:52	0:02:01	0:04:48
11		0:08:28		0:03:31
12		0:09:05		0:03:21
13		0:13:25		0:03:20
14		0:12:43		0:10:11
15		0:09:33		0:03:36
16		0:07:57		0:04:31
17		0:05:15
18		0:05:20
19		0:06:22
20		0:09:44
21		0:12:34
22
23
New Points Sytem

Cell Formulas
Range	Formula
B4:D4	B4	=((B7*B3)/60)/24
B5:D5	B5	=SUM(B8:B30)
B6:D6	B6	=IF(B4-B5<0, "-" & TEXT(ABS(B4-B5),"h:mm:ss"),B4-B5)
B7:D7	B7	=COUNT(B8:B30)

 

[Momentman]

What formula are you using in the conditional formatting? and for what cells

 

[pghhdh]

Hi Momentman! I was attempting to enter a formula for conditional formatting in row 6 and tried a variety of simple things. What I thought would be the simplest was comparing row 4 to row 5 and adjusting the formatting of row 6 accordingly, but something I was doing wasn't working.

 

[Momentman]

Hi Momentman! I was attempting to enter a formula for conditional formatting in row 6 and tried a variety of simple things. What I thought would be the simplest was comparing row 4 to row 5 and adjusting the formatting of row 6 accordingly, but something I was doing wasn't working.

If the formula gives the correct answer on the spreadsheet, then conditional formatting should be easy...

you can simply use Highlight cell rules>greater than 0.or if you choose to use a formula, then B$6>0 in the conditional formatting

 

[pghhdh]

Right! And that's where I'm running into an issue. I've not done this before with times and I think that's where I'm at a sticking point. Using the formula you suggest above is where I started and I wasn't able to get any results.

 

[Momentman]

Right! And that's where I'm running into an issue. I've not done this before with times and I think that's where I'm at a sticking point. Using the formula you suggest above is where I started and I wasn't able to get any results.

Times are basically numbers in Excel. So you can treat them as such. Highlight where the number(times) are greater than zero...that;s basically your OVER

 

[pghhdh]

I'll try it from that angle and keep my fingers crossed! Thanks!