More Excel Answers
Question Library
Ask a question about Excel
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Tom Ogilvy
Expertise
Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience
Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials
BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
| | |
| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > Accumulation of time in a log
Excel - Accumulation of time in a log
Expert: Tom Ogilvy - 11/10/2009
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
Ask a Question
|
|
