+ Reply to Thread
Results 1 to 6 of 6

Need to color code by time with conditional formatting

  1. #1
    Registered User
    Join Date
    09-10-2015
    Location
    ann arbor, michigan
    MS-Off Ver
    2013
    Posts
    8

    Need to color code by time with conditional formatting

    needhelp.xlsHi.

    I am trying to use conditional formatting to color code a column of times.

    The format is minutes:seconds

    If the time is less than 3:30 it needs to be formatted with a green fill
    If the time is 3:30-5:00 it needs to be formatted with a yellow fill
    If the time is above 5:00 it needs to be formatted with a red fill

    Basically the goal of the business is to have the time under 3:30 every day and my job is to rank and recap this performance.

    So far I have tried going to formatting and choosing the mm:ss option
    Then I highlight the entire column
    I go to conditional formatting and create a new rule
    Format only cells that contain cell value less than or equal to =time(3,30) and it says enter a valid formula
    So I tried =time(3,30,0) and it let me do it
    format fill green

    Then when I run the rule nothing happens.

    Please help!

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,377

    Re: Need to color code by time with conditional formatting

    Hi, welcome to the forum.

    What you need to know about times in Excel is that they are treated as fractions of a day. So 6am = 0.25, 12noon = 0.5, 6pm = 0.75, etc. The same applies to hours & mins: 3hrs = 1/8th of 24 = 0.125, etc. Therefore, you need to use the appropriate fraction in your conditional formatting.
    Try using something like this for green / yellow / red:
    Cell Value | less than | ="3.5/24"
    Cell Value | between | ="3.5/24" | and | ="5/24"
    Cell Value | greater than | ="5/24"
    Make sure 'Stop If True' is selected for all of the rules.

    Hope that works for you.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.
    Last edited by Aardigspook; 09-11-2015 at 07:18 AM.

  3. #3
    Registered User
    Join Date
    09-10-2015
    Location
    ann arbor, michigan
    MS-Off Ver
    2013
    Posts
    8

    Re: Need to color code by time with conditional formatting

    Aardigspook

    That worked way better than what I was doing.

    Almost perfectly!

    The only problem I am running into is that it is coding 3:01 as yellow when it should be green.
    I'll include a clip of what is going on and if you have any more advice I would very much appreciate it!

    Thanks!

    test.png

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,377

    Re: Need to color code by time with conditional formatting

    Ok, not entirely sure why it's doing that, but I do have a way to fix it.

    Instead of formatting your cells as mm:ss, format them as [h]:mm. Then change the conditional formatting to remove the quotation marks, so ="3.5/24" becomes =3.5/24 etc.

    The [h] is used instead of h without [] so that you can total to more than 24, which you'll obviously need for minutes, if you're totalling in rows 8 and 16 (though the figures in there aren't totals at the moment).

    You may also want to remove the conditional formatting from blank cells (D9), either by deleting it from those cells or by adding another conditional format with Cell Value ="" with no format set.

    Here's your file back with the changes done - note that I've saved it as an .xlsx file, because .xls file type only supports 3 conditional formats, so the 'blank' format wouldn't work in that older file type.
    needhelp _ CF mins_secs for ibooklover123.xlsx

    Hope that now works for you.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

  5. #5
    Registered User
    Join Date
    09-10-2015
    Location
    ann arbor, michigan
    MS-Off Ver
    2013
    Posts
    8

    Re: Need to color code by time with conditional formatting

    Hey Thanks!!!!

    That did it - though for some reason to get it to change the formatting i had to individually double click each cell. No big deal though - i recorded the whole thing as a macro so I can just run it every day.

    One step closer to automating my entire morning routine so I can wake up 10 minutes later!


    Thanks again

    =)

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,377

    Re: Need to color code by time with conditional formatting

    You're welcome - enjoy your extra time in bed

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

+ 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. Know the color code used in conditional formatting
    By krazyhype19 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2014, 12:26 AM
  2. Replies: 3
    Last Post: 05-30-2014, 10:24 AM
  3. Replies: 5
    Last Post: 12-07-2013, 02:21 PM
  4. [SOLVED] Conditional formatting to adapt font color to background color
    By jankee in forum Excel General
    Replies: 4
    Last Post: 08-23-2013, 09:38 AM
  5. get the color coding in conditional formatting using VBA code
    By ammupriyaa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-04-2012, 07:13 AM
  6. [SOLVED] Conditional color formatting entries have wild color.
    By John Geyer in forum Excel General
    Replies: 0
    Last Post: 02-24-2006, 02:15 PM

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