+ Reply to Thread
Results 1 to 5 of 5

Highlight time gap between 2 rows

  1. #1
    Registered User
    Join Date
    05-07-2017
    Location
    usa
    MS-Off Ver
    2016
    Posts
    3

    Highlight time gap between 2 rows

    Hello!
    I am trying to find a quick formula that will help me audit time stamps. I would like to identify time gaps of 10 minutes or more by highlighting the 2 cells involved.

    5/2/2017 8:12:00 AM
    5/2/2017 8:14:00 AM
    5/2/2017 8:14:00 AM
    5/2/2017 8:14:00 AM
    5/2/2017 8:17:00 AM
    5/2/2017 8:27:00 AM

    5/2/2017 8:29:00 AM
    5/2/2017 8:29:00 AM
    5/2/2017 8:29:00 AM
    5/2/2017 8:59:00 AM

    5/2/2017 9:03:00 AM
    5/2/2017 9:05:00 AM
    5/2/2017 9:06:00 AM
    5/2/2017 9:07:00 AM
    5/2/2017 9:08:00 AM

    Thank you in advance for any help you can give me!!!

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Highlight time gap between 2 rows

    you actually need 2 conditional formatting to do this. Look at the attached workbook for a working example.
    Attached Files Attached Files
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    05-07-2017
    Location
    usa
    MS-Off Ver
    2016
    Posts
    3

    Re: Highlight time gap between 2 rows

    I have NO experience with any of that
    I'm sorry...I know how annoying it must be to dumb it all down

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Highlight time gap between 2 rows

    Ok, first select the HOME menu on top of the screen.
    Be sure to open the workbook I supplied.
    Then you should see the Conditional Formatting icon. Click on it.
    Click on manage rules.
    You should see the 2 rules I created.
    You can click on edit to see the entire formula I used. It is as simple as testing if the difference between 2 adjacent cells are more than 10 minutes away.
    Remember that for Exel, time is expressed in fraction of a day. That is why I'm dividing the 10 minutes by 60 minutes in an hour and by 24 hours in a day.
    In the "Applied to" column, you will see at which cells this condition is applied.
    One starts at cell A1 so it will color cells starting at cell A1.
    The other one starts at cell A2 and will color cells starting at cell A2. You will note that the 2 formulas are different because they dont start at the same cell.
    If you edit a rule, you can change the format as you want by clicking on Format.
    I hope this help you a bit.
    For more information, you can find detailed tutorial on Excel Conditional Formatting on the Web.

  5. #5
    Registered User
    Join Date
    05-07-2017
    Location
    usa
    MS-Off Ver
    2016
    Posts
    3

    Re: Highlight time gap between 2 rows

    Thank you so much! That looks like something I can do!!

+ 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] Conditionl Formating to highlight time slots between start and Finish time.
    By excelrooky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-25-2017, 02:39 AM
  2. Highlight time in green based on real time?
    By CC268 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-13-2016, 02:50 PM
  3. Highlight rows every time value in column A changes
    By marius1304 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2015, 09:33 AM
  4. how to highlight rows based on latest time stamp
    By rjagini in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-13-2012, 03:31 PM
  5. Replies: 2
    Last Post: 06-27-2012, 12:27 PM
  6. [SOLVED] highlight/remove highlight of rows conditional on cellvalue
    By timtim89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2012, 07:14 AM
  7. compare 2 rows at a time and highlight differences
    By teej in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2009, 02:25 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