+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting based on date and time

  1. #1
    Registered User
    Join Date
    10-26-2017
    Location
    Melbourne, Florida
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Conditional formatting based on date and time

    Hello all,

    I need some assistance with a two-fold issue. I need a formula to calculate the age (in hours) of how long a particular case has been open (calculated in column E) and color the row according to the color coded legend below the rows. Currently, the conditional formatting that is there only accounts for a date and now I need to report how many hours old a particular item is. I have attached an example spreadsheet for reference.

    Any assistance would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Conditional formatting based on date and time

    remove duplicated
    Last edited by etaf; 10-26-2017 at 03:12 PM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Conditional formatting based on date and time

    it works - if you put dates into those cells
    rather than use N3
    you could replace that for today()

    also for green rather than = 2
    use >=2
    then you can delete the other green

    If the cell is blank , what do you want the colour to be

    you could ADD
    AND ( ..... , D4<>"")

    Now time
    you have NO time in the cell so did you just want multiples of 24

    you could then use today() - D4
    which will give the number of days
    and then * 24
    to get hours

    is that the sort of thing you mean ?

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Conditional formatting based on date and time

    Try this
    apply green color to B4:E15
    =TODAY()-$D4<3 --> Green (0, 176, 80) (base color not needed in conditional formating)
    =TODAY()-$D4=3 --> Yellow (255, 255,0)
    =TODAY()-$D4=4 --> Orange (255, 204, 0)
    =TODAY()-$D4=5 --> Dark Orange (255, 153, 51)
    =TODAY()-$D4>5 --> Red (255, 255, 255)
    Apply formulas into B4:E15

    See the file
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-26-2017
    Location
    Melbourne, Florida
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Re: Conditional formatting based on date and time

    Thank you for the response. My biggest problem aside from that is the need for the cell D4 to have a date and time, like 10/24/2017 11:42 AM and then for E4 to calculate the hours (I assume the best way would be using the NOW() function) to tell me how many hours it has been open. Any suggestions would be helpful.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,549

    Re: Conditional formatting based on date and time

    yes NOW() will have the system time
    and update each time the spreadsheet calculates

    also if its over 24 you need to format the time cells as [H]:MM
    then you will get hours above 1 day

  7. #7
    Registered User
    Join Date
    10-26-2017
    Location
    Melbourne, Florida
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Re: Conditional formatting based on date and time

    Thank you all, that has solved my dilemma.

+ 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 - time based
    By rosethorn5 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2017, 03:00 PM
  2. Replies: 0
    Last Post: 06-15-2017, 06:28 PM
  3. [SOLVED] Conditional Formatting with Date/Time with Date/Time of another cell
    By jnepsa in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-20-2017, 10:48 AM
  4. [SOLVED] Conditional Formatting of Cells based off of date and time criteria
    By rhett7660 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-16-2015, 03:14 PM
  5. Conditional Formatting - Based on Time
    By haroon284 in forum Excel General
    Replies: 6
    Last Post: 09-08-2014, 04:47 AM
  6. [Solved] Conditional Formatting based on date and time
    By HackerJL in forum Excel General
    Replies: 5
    Last Post: 06-08-2011, 06:58 PM
  7. Conditional formatting based on time
    By sharkey in forum Excel General
    Replies: 2
    Last Post: 12-24-2007, 02:13 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