+ Reply to Thread
Results 1 to 2 of 2

Conditional formating - Cell changes color based on time passed from a set date&time

  1. #1
    Registered User
    Join Date
    02-15-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1

    Question Conditional formating - Cell changes color based on time passed from a set date&time

    I have tried a few formulas for conditional formatting without luck.
    I' m trying to have cells change color based on 30min, 60min and 90min passed from a cell containing a specific date & time. Like so:

    CELL NEEDS TO BE GREEN IF LESS THAN 30MIN HAVE PASSED FROM INITIAL RESPONSE CELL
    CELL NEEDS TO BE YELLOW IF MORE THAN 30MIN BUT LESS THAN 60MIN HAVE PASSED FROM INITIAL RESPONSE CELL
    CELL NEEDS TO BE ORANGE IF MORE THAN 60MIN BUT LESS THAN 90MIN HAVE PASSED FROM INITIAL RESPONSE CELL
    CELL NEEDS TO BE RED IF MORE THAN 90MIN HAVE PASSED FROM INITIAL RESPONSE CELL
    NO FORMATING IF DATE&TIME VALUE HAS BEEN ENTERED IN CELL REGARDLESS OF TIME PASSED

    I attached a .xlsx with an example of what I need

    any help will be much appreciated
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Conditional formating - Cell changes color based on time passed from a set date&time

    Highlight the area you want to have conditional formatting (e.g. G2:G50).
    Make a new rule with formula for conditional formatting.
    For the GREEN rule use this formula:
    =AND(ISBLANK(G2),(NOW()-$F2) < TIME(0,30,0))

    Adjust that formula as needed for the other colors.

    Make sure in the conditional formatting rules manager the green rule is at the top, followed by yellow, then orange, then red at the bottom.

    One problem you may run into is that this only gets updated with a change in the spreadsheet. So, you can't just stare at the screen and expect the colors/time to change. Here is a link that shows how to force data refreshes - use if needed:
    https://office-watch.com/2018/excel-...lation-tricks/
    Last edited by Pauleyb; 02-19-2020 at 12:03 PM. Reason: Get around the Securi firewall not liking <
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

+ 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. Changing Cell Color Based Date and Time That Is Dynamic
    By fearglow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2020, 08:13 AM
  2. [SOLVED] Cell color changing based on Time passed
    By monaghan4523 in forum Excel General
    Replies: 23
    Last Post: 04-24-2019, 12:17 PM
  3. Replies: 8
    Last Post: 06-20-2014, 02:40 AM
  4. Change multiple cell colors based on date and time passed
    By cvalentine02 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-12-2013, 12:20 PM
  5. Replies: 1
    Last Post: 10-09-2013, 05:05 PM
  6. Conditional formating based on the time and date
    By kricks99 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2013, 09:42 PM
  7. Conditional formatting based on entered dated and how much time has passed
    By sanchez7315 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2012, 12:07 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