yogi_Compute Start Time Of Next Job From End Time Of Previous Job

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user on.llc said:
I have the following formulas (crude and primitive as they may be) that works. But I have new data coming in sometimes by the minute, an arrayfomula function would sure help. But mixing indexing and arrayformula combination does not generate the intended result. Any help would be greatly appreciated.
=if(D32<2,iferror(INDEX( FILTER( C33:C ; A33:A=A32;B33:B=B32;D33:D<2) ; 1 );""),"") and =if(D32=0,if(E32=1,index(sort(filter(C1:E31,E1:E31>1,A1:A31=A32),1,false),1,3),""),if(D32>1,"",E32))
The link is https://docs.google.com/spreadsheet/ccc?key=0Ara_-4ohkGQYdERpRVdidWkxbGZJRjVlRUp6dGZqV2c
Col D-F are incoming data where employees key in with text messages. Col A-F are stamped by the system.
Col G & H are the formulas seeking guidance here.
Basically the end time (Code 0) of one Work Order becomes the start time (Code 1) of the next Work Order except when lunch (Work Order 1) gets in the way. The continuation of the Work Order prior to lunch needs to be resumed when lunch stops. Remarks (Code 2) can be ignored for calculation. And (Code 0) and (Work Order 0) together means end of day. Hence when the spreadsheet is tabulated, all the rows with blank Col G & H can be skipped. The spreadsheet has only one sheet but may contain 10,000 rows of data.
Ditto to you gentleman's sentiments about Andre. I hope all is well with him and look forward to his continued insightful comments.
Jim
-----------------------------------