Determining Time Gaps Between Daily Tasks

%3CLINGO-SUB%20id%3D%22lingo-sub-2013127%22%20slang%3D%22en-US%22%3EDetermining%20Time%20Gaps%20Between%20Daily%20Tasks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013127%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20in%20search%20of%20a%20formula%20to%20determine%20time%20gaps%20between%20tasks%20based%20upon%20work%20dates.%20For%20instance%2C%20an%20employee%20worked%20on%209%2F18%2C%20and%20he%2Fshe%20performed%20multiple%20tasks%20throughout%20the%20day.%20Lets%20assume%20the%20task%20start%20and%20end%20times%20were%209am-1pm%2C%2012pm-3pm%2C%20and%204pm%20to%206pm.%20I%20need%20a%20formula%20or%20workflow%20that%20will%20first%20identify%20the%20job%20date%2C%26nbsp%3B%20analyze%20the%20start%2Fend%20times%2C%20and%20locate%20the%201%20hour%20gap%20between%203pm%20and%204pm.%20The%20data%20is%20setup%20as%20follows.%26nbsp%3B%3C%2FP%3E%3CP%3EColumn1%26nbsp%3B%20%26nbsp%3BColumn%26nbsp%3B%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BColumn%203%3C%2FP%3E%3CP%3EJob%20date%3A%26nbsp%3B%20%26nbsp%3BStart%20Time%3A%26nbsp%3B%20%26nbsp%3BEnd%20Time%3A%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E9%2F18%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%209%3A00%3A00am%26nbsp%3B%20%26nbsp%3B%201%3A00%3A00pm%3C%2FP%3E%3CP%3E9%2F18%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B12%3A00%3A00pm%26nbsp%3B%20%26nbsp%3B3%3A00%3A00pm%3C%2FP%3E%3CP%3E9%2F18%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%204%3A00%3A00pm%26nbsp%3B%20%26nbsp%3B%206%3A00%3A00pm%3C%2FP%3E%3CP%3E9%2F19%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2010%3A00%3A00am%26nbsp%3B%2012%3A00%3A00pm%3C%2FP%3E%3CP%3E9%2F19%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%3A00%3A00pm%26nbsp%3B%20%26nbsp%3B%203%3A00%3A00pm%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2013127%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013196%22%20slang%3D%22en-US%22%3ERe%3A%20Determining%20Time%20Gaps%20Between%20Daily%20Tasks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F910556%22%20target%3D%22_blank%22%3E%40tlmedia1328%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((EndTimeColumn-StartTimeColumn)*(DateColumn%3Ddate))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I'm in search of a formula to determine time gaps between tasks based upon work dates. For instance, an employee worked on 9/18, and he/she performed multiple tasks throughout the day. Lets assume the task start and end times were 9am-1pm, 12pm-3pm, and 4pm to 6pm. I need a formula or workflow that will first identify the job date,  analyze the start/end times, and locate the 1 hour gap between 3pm and 4pm. The data is setup as follows. 

Column1   Column  2     Column 3

Job date:   Start Time:   End Time:   

9/18          9:00:00am    1:00:00pm

9/18         12:00:00pm   3:00:00pm

9/18          4:00:00pm    6:00:00pm

9/19          10:00:00am  12:00:00pm

9/19          1:00:00pm    3:00:00pm

1 Reply

@tlmedia1328 

That could be like

=SUMPRODUCT((EndTimeColumn-StartTimeColumn)*(DateColumn=date))