Expert: Tom Ogilvy - 11/10/2009

Question
QUESTION: Hello Tom,

I am upgrading my Log Analyzer that extracts performance statistics from a date/time stamped log. The input worksheet has a row with a date/time stamp for each contact logged during a 48-hour period. There might be up to 3,000 rows of data.

I created a report worksheet using sumproduct formulas that compares each row of the input worksheet and shows the count of lapsed times between each entry. For example, the count of 0~1 minute, 1~2 minutes, 2~3 minutes, etc. This part is working fine.

But I can't figure out how to create a formula where I can set a minimum time (say 10 minutes) and have all the lapsed times >= 10 minutes be totaled. For example, there may be a dozen instances where the gap between log entries exceeds 10 minutes or more.

The objective is to calculate the amount of "off time" within the 48-hour period by adding these values together. I would use a reference cell so the "10 minutes" could be a user determined value.


ANSWER: Robert

Assume your data is in    C2:C3000
and the user specifies the time lower bound in G1.  


=SUM(IF((($C$3:$C$3000)-($C$2:$C$2999))>=$G$1,(($C$3:$C$3000)-($C$2:$C$2999))))
Entered with Ctrl+Shift+Enter rather than just enter since this is an array formula

where G1:  =Timevalue("00:10:00")

Tested and worked for me.

Follow-up if I haven't correctly understood your problem.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Thank you. I understand what the formula is supposed to do. That's slick! I am having my usual problem of getting #VALUE! errors because of a data syntax mismatch.

It's probably easier to download a copy of the workbook to see the context instead of trying to explain everything. Please look at column P on the RATES worksheet.  Here's the link: http://tinyurl.com/yhty3r6  The workbook is loaded with test data so it's about 8-Mb in size.

Working with Excel date/time values always seems to be my nemesis!


Answer
Robert,

the problem is that you have blank cells in the range.

I will have to see if I can craft a solution for that - but the formula works fine if you restrict it to refer to the cells that contain date/time values.

I will get back to you.

--
Regards,
Tom Ogilvy