+ Reply to Thread
Results 1 to 7 of 7

Condtional formatting with time

  1. #1
    Registered User
    Join Date
    04-21-2014
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    9

    Condtional formatting with time

    Hi all,

    can anybody help with this problem I'm having? I'm trying to condtional format all cells in the attached table over 9:00:00. It's working but it is also highlighting times under 9:00:00 as well?

    Any ideas?
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Condtional formatting with time

    Hi, welcome to the forum

    None of those times are rally times, they are text looking like time. Where is this data coming from?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-21-2014
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Condtional formatting with time

    Quote Originally Posted by FDibbins View Post
    Hi, welcome to the forum

    None of those times are rally times, they are text looking like time. Where is this data coming from?
    Hi, Thanks for your reply. The table is just an extract of a sheet I'm working on. The times are coming from a login report for phone stat logins.

    Bascially I'm looking to highlight all cells greater than 9hrs or 09:00:00

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Condtional formatting with time

    Like I said they are not times, they are text, so you need to convert them 1st.

    Instead of the rule you have, use "Use Formula" and use this as your formula...
    =--$A1>9/24

  5. #5
    Registered User
    Join Date
    04-21-2014
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Condtional formatting with time

    Thanks for your reply again.

    That formula worked brilliantly on the book attached but when I tried it on the full sheet I'm working on it did nothing?

    Are you possibly able to explain the formula so that I could replicate it?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Condtional formatting with time

    Sure, it is fairly simple.

    Your "time" only looks like time, but is actually text. I used a double negative -- to convert it.
    Then, instead of adding another function, I just used 9/24.

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Thu 04 Feb 2016) is actually 42404

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    If this is not working on your real data...
    Did you adjust the cell refs in the CF?
    Did you apply it to the correct range?
    Does your "time" perhaps also include a date?
    Can you upload a sample of your actual data - scrubbed, of course

  7. #7
    Registered User
    Join Date
    04-21-2014
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Condtional formatting with time

    Quote Originally Posted by FDibbins View Post
    Sure, it is fairly simple.

    Your "time" only looks like time, but is actually text. I used a double negative -- to convert it.
    Then, instead of adding another function, I just used 9/24.

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Thu 04 Feb 2016) is actually 42404

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    If this is not working on your real data...
    Did you adjust the cell refs in the CF?
    Did you apply it to the correct range?
    Does your "time" perhaps also include a date?
    Can you upload a sample of your actual data - scrubbed, of course
    Really appreciate your help, I'll try and upload the actual sheet shortly but will take some time to remove certain bits of data.

    I'm looking to add the formatting to the following range, K14-K25. How would i adjust the formula you suggested?

+ 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. [SOLVED] Condtional Formatting
    By rizmomin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2015, 01:24 PM
  2. [SOLVED] Condtional Formatting
    By Sarah197862 in forum Excel General
    Replies: 15
    Last Post: 05-21-2014, 08:53 AM
  3. Using condtional formatting for a time range
    By lesoies in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-26-2013, 07:52 PM
  4. [SOLVED] VBA or Condtional Formatting
    By persais in forum Excel General
    Replies: 9
    Last Post: 08-16-2012, 12:01 PM
  5. Condtional Formatting
    By iamtehwalrus in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-19-2010, 11:25 PM
  6. condtional formatting
    By keith6292 in forum Excel General
    Replies: 2
    Last Post: 10-28-2009, 12:43 PM
  7. condtional formatting help
    By diesel20056 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2006, 11:16 AM

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