+ Reply to Thread
Results 1 to 4 of 4

Conditionally formatting a cell based on current time of day

  1. #1
    Registered User
    Join Date
    07-25-2014
    Location
    Olathe, KS
    MS-Off Ver
    2010
    Posts
    2

    Conditionally formatting a cell based on current time of day

    I have header cells labeled by the time of day 8:00, 9:00, 10:00, etc.

    I want the cell fill color to change when the current time is >= the time in the cell. For example, the cell labeled 8:00 am turns green when the clock strikes 8:00 am. I can't seem to get anything to work. Please help.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Conditionally formatting a cell based on current time of day

    Let's hop in my car and go to imagination land, where your headers are in A1, B1, C1, etc.

    A2's formatting:

    =(A$1<=NOW()-INT(NOW()))

    and then paste the format across the cells
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditionally formatting a cell based on current time of day

    I found that this works though a bit "clunky". I found that the AM/PM was necessary for the format that I used.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    07-25-2014
    Location
    Olathe, KS
    MS-Off Ver
    2010
    Posts
    2

    Re: Conditionally formatting a cell based on current time of day

    Thanks guys. I discovered that my primary problem wasn't the conditional formatting formulas I was using but that they are dependent on the header cell always having the current date. Otherwise, the NOW function becomes moot as soon as the date changes . . . and all of the cells fill regardless of the time of day. Once I was able to set the column headers to default to the current date + the static time I wanted, the conditional formatting worked fine. Ex) =today()+(8/24) for the 8am column header.

+ 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. Replies: 2
    Last Post: 03-29-2014, 03:58 AM
  2. Is there a way to do conditionally formatting based on a rolling date?
    By Drogo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-05-2014, 10:44 PM
  3. Display current time based on cell changes.
    By shiva_reshs in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2013, 12:16 AM
  4. [SOLVED] Conditional Formatting, current cell color follows to diff cell based on content
    By iallenwood in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2012, 04:13 PM
  5. Replies: 4
    Last Post: 05-01-2012, 03:44 PM

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