Trying to find time gaps greater than "X" minutes.

[xdozex]

Hello all,

My first post here so I would like to thank everyone in advance for any help I receive. I am recently getting more into excel so I'm looking forward to helping out others as I learn more! Okay, so on with my problem.

I get sheets, sometimes 10 records, sometimes 1,000 records. And there is a time column. It's formatted as: hh:mm:ssam (09:12:36am). Now what I need to do is find a way to highlight any record that has a large gap between itself and the record before it. This gap amount can be variable, but for the explanation lets say, I need to highlight any record that has a gap of 5 minutes greater than the time before it.

I've tried a formula I found elsewhere:

=F3-F2> 1/24/60*5

But it just returns "#VALUE!".

I'm completely stumped, and since about last week, 1 of my daily responsibilites will be analysing anywhere from 10 - 50 sheets a day.
The guy before me would open the sheet and manually find it by literally looking from record to record. This was this 1 guy's full time job. From what I'm reading around there are ways to have excel do this for me with a simple formula and conditional formatting, but I am not having any luck.

Any help would be greatly appreciated, thanks in advance!
Mark
</pre>

 

[sous2817]

Hi and welcome to the board!

How about:

=IF(F3 > F2 + TIME(0,5,0),TRUE,FALSE)

Does that get you close?

 

[Jonmo1]

Welcome to the board...

My guess is that your times are not valid excel times, but a TEXT string that looks like a time..

Use this to test

=ISNUMBER(F2)

Repeat for F3

Both should return TRUE, if not, then they are NOT valid times.

 

[c_m]

or if you are interested in conditional formatting use this formula:
=MINUTE(C2-C1)>5
where C1:C100 are the entries of time. Select C2:C100 and goto format-conditional formatting-select formula is from the drop down list and paste the above formula. (This is valid for XL2003)

 

[xdozex]

Thanks for the suggestion guys, but I think Jonmo1 has pointed out a valid point. I tried his formula, and I am getting "FALSE" as a value, meaning my times are not valid. They look like this 05:09:10am but for some reason Excel is saying its not valid.

 

[c_m]

you need a space before am. It should look like
05:09:10 am instead of 05:09:10am

 

[Jonmo1]

Are those times manually entered, or result of a formula?

If manually entered, C_M is correct

If they are result of formula, can you show that formula?

 

[xdozex]

Well the entire sheet is actually whats called a shapefile which is a file format from the GIS industry. The program I use exports the attributes of the shapefile as a DBF which open in excel. In the shapefile the time in that format is okay. But in access, the AM requires a space.

So I did a find and replace, and replaced "am" with " am" and that process created a valid time. So now I need to figure out how to find the gaps, and then build the whole process into a macro.

I'm going to refer to the first few suggestions for formulas to find the gaps. I'll keep you guys posted.

 

[Jonmo1]

You can try this formula to convert the text times to real times..

=((LEFT(F3,8)&" "&RIGHT(F3,2))+0)-((LEFT(F2,8)&" "&RIGHT(F2,2))+0)>"00:05:00"+0

 

[xdozex]

Okay, guys I think I've figured it out. I used find and replace. And replaced "am" with " am", which made the times valid. Then I used the formula:

"=(A3-A2,"h:mm:ss")"

Which gave me the time difference between each record, and then used a conditional to highlight any of the records that had a gap of 10 minutes or higher. I'm sure there is a way to work it all together so the conditional formatting is not a separate step, but I sort of like having the ability to read the gaps in measure of time. Rather than just highlighting the records that do not satisfy the rule.

Thanks for all the help guys, this forum ROCKS!