Conditional formatting issue

pghhdh

New Member
Joined
Aug 17, 2019
Messages
6
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
ABCD
3Time per unit1346.5
4Max Time3:02:000:12:000:58:30
5Actual Time2:07:470:08:300:41:32
6Over/Under0:54:130:03:300:16:58
7Actual Count1439
80:08:590:03:410:03:05
90:09:300:02:480:05:09
100:08:520:02:010:04:48
110:08:280:03:31
120:09:050:03:21
130:13:250:03:20
140:12:430:10:11
150:09:330:03:36
160:07:570:04:31
170:05:15
180:05:20
190:06:22
200:09:44
210:12:34
22
23
New Points Sytem
Cell Formulas
RangeFormula
B4:D4B4=((B7*B3)/60)/24
B5:D5B5=SUM(B8:B30)
B6:D6B6=IF(B4-B5<0, "-" & TEXT(ABS(B4-B5),"h:mm:ss"),B4-B5)
B7:D7B7=COUNT(B8:B30)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
What formula are you using in the conditional formatting? and for what cells
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top