Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Conditional Formatting not working right

Hello,

I have a time tracker with calculated times, then based on input of actual times the color of the time is suppose to go red if greater then the calculated time.

A = START
B = ARRIVE
C = SUPPLY (YES/NO)
D = DONE LOADING
E = DONE

The Calculated times are in row 2
The Actual times are in row 3

For each calculated time the formula is similar to:
a2 =if(e2="","",if(c2="NO", e2-"03:00", e2-"02:30"))
b2 =if(e2="","",if(c2="NO", e2-"01:45", e2-"01:00"))
d2 =if(e2="","",if(c2="NO", e2-"00:40", ""))

I set up the conditional formatting as such for the times with a calculated entry
Cell greater than =MOD(a2, 1) change font color to Red for =$a$3

Additionally there is a conditional formatting to gray out some cells if SUPPLY is NO
formula =c2="NO" change fill to Gray for cells c2:d3

The formatting for all the time cells is hh:mm

This normally produces the required result but occasionally it turns red when the times are equal or greater.

e.g.
e2=12:00 ; d2=11:20 causes d3=11:20 to turn red
e2=16:00 ; d2=15:20 causes d3=15:20 to stays black

Does anyone have any ideas what might be causing the issue. Or what I might be able to fix this. Using MS Office 2010.

Thank you.
~~Chris
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Conditional Formatting not working right

For anyone who read this, I figured out a way that works. In my calculated entries, I added an extra 30 seconds so that the conditional formatting works when the "times" are the same.

e.g. e2-"03:00" + "00:00:30"

~~Chris
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Not working: Conditional Formatting Mel[_4_] Excel Discussion (Misc queries) 6 May 3rd 23 03:42 AM
Conditional Formatting ROW not working Rod Excel Discussion (Misc queries) 8 October 5th 08 09:32 PM
Why isn't the conditional formatting working? Ray[_4_] Excel Programming 2 July 10th 07 06:42 AM
Conditional Formatting Not Working Out Big Red[_2_] Excel Programming 3 June 15th 07 12:07 PM
Conditional Formatting is not working... tmerton Excel Worksheet Functions 1 March 17th 06 11:42 PM


All times are GMT +1. The time now is 03:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"