How can I get an average of times in Excel?

I Have a column of cells in excel of format hh:mm (The day is NOT included) like:

``````19:08
19:08
19:40
20:10

1:45 (Any Times before 12:00 noon are considered NEXT DAY)
``````

one (or more of these) is after midnight.

The Average of these times is = 20:46

But Excel AVERAGE gives 15:58

I would like to calculate this average without adding extra columns or using VBA Just one formula at the end of the column. By the way, I also need to exclude any blank entries in the column for the calculations.

• You'll need an averageif, I think - what do you want to average? Noon to noon like 11:59am is the latest? – Raystafarian Jul 21 '15 at 17:10
• correct Raystafarian ... Wouldn't AVERAGEIF just exclude entries less than 12:00? rather than adding 24 hours and including them? – Flashheart Jul 21 '15 at 17:19
• Well since you want to skew the timeline, it will be trickier, I thought you wanted to exclude some. – Raystafarian Jul 21 '15 at 17:22

``````=AVERAGE(IF(A1:A5>=0.5,A1:A5,1+A1:A5))
``````=AVERAGE(IF(NOT(ISBLANK(A1:A5)),IF(A1:A5>=0.5,A1:A5,1+A1:A5),""))