I have a log file that has a time (hh:mm:ss) in column A, and (among other commands) "LOAD" and "UNLOAD" strings in column B. I need to calculate the total time the machine was idle during the day, i.e., sum the time gaps between each "UNLOAD" command and the next "LOAD" command. I'm not having much luck with simple formulas, and I'm not a VBA programmer. I would appreciate any help.
Hi handerson,
Would you be able to upload a sample file.. ?
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Does a load command automatically follow an Unload in column C (ie. in the very next row)? Are there blanks in col B? Can you upload a small example of what we might see?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
The log files that I am using have lots of other commands and status messages (potentially including blanks), but I have no problem filtering them to only show the Load and Unload commands I am interested in. Here is a short sample of data:
0:01:26 LOAD
0:02:53 UNLOAD
0:04:19 LOAD
0:05:46 UNLOAD
0:07:12 LOAD
0:08:38 UNLOAD
In this case, the total idle time is 2 minutes, 52 seconds (4:19-2:53)+(7:12-5:46). There can be over 500 Load/Unload event pairs in each log file.
Hi handerson,
Basically we are considering the complete set i.e., UNLOAD followed by LOAD.... so I have put in "Y" on these combinations and got the solution.. see the attached file and let me know if this helps. thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
If you can filter out (and delete) the non-load/unload entries, you can use this formula
=SUM(IF($B$2:$B$6="load",$A$2:$A$6-$A$1:$A$5))
This is an array formula. Enter it with CNTRL SHFT ENTER which will put brackets {} around the formula.
ChemistB,
Thanks! That did what I wanted.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks