# Highlight time gap between 2 rows

1. ## 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

2. ## 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.

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. ## 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.
For more information, you can find detailed tutorial on Excel Conditional Formatting on the Web.

5. ## Re: Highlight time gap between 2 rows

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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