# 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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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

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

1,147,476
Messages
5,741,349
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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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