How to average times that cross midnight?

[JenniferMurphy]

I have several sheets with columns of just times (no dates). I want to calculate the Max, Min, and Ave times. Most of these times are between 9pm and midnight as in column D. A few are at or after midnight as in Column F. If none of the times are later than 11:59 PM, then the calculations work. If any are at or after midnight, the calculations are wrong.

A solution I came up with is in Column H. I move the times back half a day. If they are before midnight, I subtract 0.5. If they are at or after midnight, I add 0.6. Now they are in the same relative position to each other, but around noon, rather than midnight. Then, when I do the calculations, I add the half day back in and everything is correct.

I know this is kludgy and it depends on the times all being close to midnight.

Is there a better way?

 

[JenniferMurphy]

Try this for Minimum & Maximun:

=MINIFS(D6:D9,D6:D9,">"&0.5)

AND

=IF(MAXIFS(D6:D9,D6:D9,"<="&0.5)=0,MAX(D6:D9),MAXIFS(D6:D9,D6:D9,"<="&0.5))

Aha! I think I understand Min, but I'll have to study that Max code.

Thanks for this.

 

[maabadi]

You're Welcome & thanks for feedback.