Subtotal of time, should not taking into account the date

Hi

I have this

Formula:

Please Login or Register  to view this content.

formula where I want the average time for only the cells that are shown (meaning filter cells are not taken into account). The problem with this formula is that it takes into account the date as well, I just want an average time within 24 hours.

Example, the cells contains these values:
17/07/2018 12:33:00
17/07/2018 10:04:00
18/07/2018 10:32:00
19/07/2018 12:04:00
19/07/2018 12:36:00
19/07/2018 11:51:00

The formula I have now gives me an average time of 15:36, as it takes into account the date as well. The correct answer should be: 11:36

Add a helper column to remove the date. =MOD(BJ20,1) In BK20 and fill down, then subtotal the times in BK20:BK1992

But that will not take the filtered rows into consideration.

I don't think that it will be possible without a helper column.

I was trying similar methods to yours with AGGREGATE(1,5 to see if it could be done, but the arrays all appear to evaluate as 3d and throw an error.

Originally Posted by jason.b75

Add a helper column to remove the date. =MOD(BJ20,1) In BK20 and fill down, then subtotal the times in BK20:BK1992

Great! that worked well, thank you!