How to average times that cross midnight?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,675
Office Version
  1. 365
Platform
  1. Windows
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.

1610782753943.png

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
Joined
Jul 23, 2011
Messages
1,675
Office Version
  1. 365
Platform
  1. Windows
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. :confused:

Thanks for this. (y)
 

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.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,675
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & thanks for feedback.
 

Forum statistics

Threads
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?

Disable AdBlock

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
Top