+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    65

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

    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

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

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

    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: copy to clipboard
    Please Login or Register  to view this content.


    See attached.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

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

    Change your formula in H2 to

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

    and it should work
    Happy with my advice? Click on the * reputation button below

  4. #4
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    65

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

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

    AlanL185

  5. #5
    Registered User
    Join Date
    08-18-2013
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    65

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

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

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

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

    Quote Originally Posted by Crooza View Post
    Change your formula in H2 to

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

    and it should work
    This is mathematically equivalent to
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting using hours/time
    By dwtaxguy in forum Excel General
    Replies: 11
    Last Post: 06-26-2017, 02:01 PM
  2. Conditional Formatting - time,hours,days
    By maxgiz84 in forum Excel General
    Replies: 3
    Last Post: 02-02-2015, 01:16 AM
  3. [SOLVED] Conditional format Date/time that is 16 hours greater or Less
    By smugglersblues in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 04:42 PM
  4. [SOLVED] Conditional formatting: TIME greater then...
    By Chris! in forum Excel General
    Replies: 4
    Last Post: 09-19-2013, 08:56 PM
  5. Replies: 2
    Last Post: 08-20-2013, 10:27 AM
  6. Conditional formatting for dates/time x hours passed
    By Sirodot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2012, 11:04 AM
  7. [SOLVED] time:How can I enter a figure for contracted hours which is greater than 24 hours?
    By Chink! in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2005, 02:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1