Averaging time calculations

[jkaler]

Hello there - new member here...

Hoping someone has a simple solution or direction to offer. The question is this: I have two columns of date data consisting of dd/mm/yy hh:mm:ss. One column is a start time the second is end time. I am calculating the time difference across the row just fine, with a condition that a specific value is present in a third cell in that row. If the specific value is not present, the time calculation result is 0:00:00.

What I am now having troubles with is calculating the average time calculated down the entire column while skipping cells with 0:00:00...

So it would look like this:
[TABLE="width: 471"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Start Time[/TD]
[TD="align: left"]End Time[/TD]
[TD="align: left"]Condition[/TD]
[TD="align: left"]Time difference[/TD]
[/TR]
[TR]
[TD="align: right"]10/7/17 5:43 PM[/TD]
[TD="align: right"]5/17/18 3:19 PM[/TD]
[TD="align: left"]Open[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]11/9/17 5:19 PM[/TD]
[TD="align: right"]5/17/18 3:18 PM[/TD]
[TD="align: left"]Closed[/TD]
[TD="align: left"]07:06:21:59:00[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/18 4:11 PM[/TD]
[TD="align: right"]5/17/18 3:18 PM[/TD]
[TD="align: left"]Closed[/TD]
[TD="align: left"]03:15:23:07:00[/TD]
[/TR]
[TR]
[TD="align: right"]4/20/18 3:39 PM[/TD]
[TD="align: right"]5/17/18 3:18 PM[/TD]
[TD="align: left"]Open[/TD]
[TD="align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]

The formula I am using to calculate the time difference is: =IF(C4="Closed", (TEXT(B4-A4,"MM:DD:hh:mm:ss")),0)

So now what I want to do is calculate the average time difference to not include the 0's


Any ideas?

 

[DRSteele]

Welcome to the forum.

This is one idea. Forget trying the approach where function TEXT is required: simply have the time difference be a blank instead of zero and format the Time Difference column as MM:DD:hh:mm:ss

Book1
	A	B	C	D
1	Start Time	End Time	Condition	Time difference
2	10/07/17 17:43	5/17/18 15:19	Open
3	11/09/17 17:19	5/17/18 15:18	Closed	07:06:21:59:00
4	3/02/18 16:11	5/17/18 15:18	Closed	03:15:23:07:00
5	4/20/18 15:39	5/17/18 15:18	Open
6			Total:	09:20:21:06:00
7			Average:	05:11:10:33:00
Sheet70

Cell Formulas
Range	Formula
D2	=IF(C2="Closed", B2-A2,"")
D6	=SUM(D2:D5)
D7	=AVERAGE(D2:D5)

 

[AhoyNC]

@jkaler - By using the TEXT function the way you did per my calculation your time difference is wrong. Best to use something like DRSteele suggested.

Wouldn't the time difference be:

Excel Workbook
	A	B	C	D	E
1	Start Time	End Time	Condition	Time difference	Shouldn't it be??
2	10/7/2017 17:43	5/17/2018 15:19	Open	0	0
3	11/9/2017 17:19	5/17/2018 15:18	Closed	07:06:21:59:00	6 months 7 days 21 Hours 59 mins 00 sec
4	3/2/2018 16:11	5/17/2018 15:18	Closed	03:15:23:07:00	2 months 14 days 23 Hours 7 mins 00 sec
5	4/20/2018 15:39	5/17/2018 15:18	Open	0	0
Sheet

[TABLE="class: cms_table, width: 471"]
[TR]
[TD="align: right"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[/TR]
[/TABLE]

 

[jkaler]

Thank you both -- I will give that a go.