# New problem with summing time gaps & conditional formatt

#### =HPSF=RMP

##### Board Regular
Hello,
I am wanting to create a cell that will sum up any time gaps over 10 minutes on a list and highlight the gaps.

Here is an example of the cells.

Activity_Time
11/21/2005 11:08
11/21/2005 11:17
11/21/2005 11:17
11/21/2005 11:21
11/21/2005 11:25
11/21/2005 11:28
11/21/2005 11:30
11/21/2005 11:38
11/21/2005 11:39
11/21/2005 11:59

On this list there would be one gap totalling 20 minutes. The 11:39 to 11:50 gap. Everything else would be ignored being under 10 minute gaps.

Is there a way to create a cell next to this that would highlight these gaps and then sum the total string of gaps??

any help would be appreciated.
thanks!!

#### Andrew Poulsom

##### MrExcel MVP
Say your data is in the range A1:A11, with the heading in A1. In B3 enter:

=MINUTE(A3-A2)>10

and copy down to B11.

Select A2:A11 and choose Format|Conditional Formatting. Condition 1 Formula is:

=OR(B2,B3)

click Format, choose what you want and click OK twice.

#### neofytosc

##### New Member
Try the following
IF((A3-A2)>=1,006944444;A3-A2;0)
Format Cell as Time

Regards

thank you!

#### =HPSF=RMP

##### Board Regular
One more question if I may.

I have taken the cells that say TRUE and converted a cell to read the total number given.

=MINUTE(B3-B2)>10 This is in cell Column C

=IF(C2,B3-B2) This is in Column D

Column D is now returning either FALSE or the time gap in hh:mm:ss. I am trying to total the number of minutes by summing them but it isnt working. Its seeing the formula instead of the number.

Any idea how I can fix this?

#### Andrew Poulsom

##### MrExcel MVP
This formula:

=C2*(B3-B2)

will return the number of minutes or zero. You can sum that column and format as [mm]:ss or you can multiply the sum by 1440 to get the minutes as a number.

thank you

#### =HPSF=RMP

##### Board Regular
I'm sorry if i'm being a pain, but now I have another problem to deal with.
The above formula works great! Thanks, however now I am running into a problem of the total not summing based on a cell that has #NUM! in it after the last gap is calculated. I want to have several extra cells there so I can input the list of times. The rows will vary. Any way to not include the "#NUM!" text from the whole sum?

thanks

#### Andrew Poulsom

##### MrExcel MVP
What formula are you using that returns #NUM!?

#### =HPSF=RMP

##### Board Regular
Sorry I started the new thread. The previous question to get to where I am was answered and I thought this was lost in the shuffle. My appoligies.

The actual formula that is returning the error is =MINUTE(A13-A12)>10
It works up until the point where the string ends.

A14 in this case would not be a number to actually subtract A13 which returns the error. I'm wanting to keep the string open.

As it is i'm having to adjust the actual columns to sum meaning I have to sum A1 to A13. I would like to automatically sum A1 to A200 even though I may not be utilizing all 200 cells. Ignoring the cells without numbers would work but I cant figure out how to format it?

Again, I'm sorry for creating the new thread.. Thank you.