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?

 

[Sulprobil]

If start time and end time are within a 24h window, you can use =MOD(Endtime - Starttime, 1).

 

[JenniferMurphy]

If start time and end time are within a 24h window, you can use =MOD(Endtime - Starttime, 1).

There is only one time. It's for when a blood glucose reading was taken. At the end of a recording period, I want to calculate the earliest, latest, and average time when those reading were taken.

 

[maabadi]

You should Input Date & time then Take Average for them.

 

[JenniferMurphy]

You should Input Date & time then Take Average for them.

I am not averaging the date + time -- just the time. These readings are all taken on different days. If I averaged the full time stamp (date + time), I would get a time stamp somewhere in the middle of the range. I want the average time of day when the readings are taken. If they are 9pm on Monday, 10pm pm Tuesday, and 11pm on Thursday (no reading on Wednesday), I want the average to be 10pm, not sometime 6 am on Wednesday, which is what I'd get if I averaged the time stamps (date + time).

This is not the average I need.

 

[Sulprobil]

Then just take =MOD(YourTime,1) and MIN, MAX, AVERAGE those.

 

[JenniferMurphy]

Then just take =MOD(YourTime,1) and MIN, MAX, AVERAGE those.

Unless there is something I don't understand, taking the Mod,1 of my time stamps will be a NOP. By definition, times are [0,1) -- fractions of a day.

 

[maabadi]

Try this :

=Average(D6:D9)+iferror(1/Countif(D6:D9,"<" & 0.5),0)

 

[maabadi]

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))

 

[JenniferMurphy]

Try this :

=Average(D6:D9)+iferror(1/Countif(D6:D9,"<" & 0.5),0)

Wow!!! That works. That IfError function is new to me. It will come in handy. Thanks.

I'll have to study why the CountIf works.

This same formula does not work for Min & Max.