# How to average times that cross midnight?

#### JenniferMurphy

##### Well-known Member
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

##### Well-known Member
Try this for Minimum & Maximun:
Excel Formula:
``=MINIFS(D6:D9,D6:D9,">"&0.5)``
AND
Excel Formula:
``=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.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

##### Well-known Member
You're Welcome & thanks for feedback.

Replies
5
Views
384
Replies
4
Views
353
Replies
15
Views
484
Replies
7
Views
300
Replies
6
Views
490

1,147,475
Messages
5,741,344
Members
423,656
Latest member
Medrok2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back