Finding the average time using varied datestamps

[ChadOwen]

Hello - I am a complete newbie to this community.  I'm an infrequent and inexperienced user of Excel.  Prior to this plea for assistance, I've spent a considerable amount of time researching how to work with time and average time in Excel and have not yet been able to create a formula to take datetimes that span midnight and provide an accurate average time in h:mm format.  I can't find a way to account for the am/pm differences and am seeking help.

As an example, let's say in cells A1:A3 I have the following data:

A1=2/8/2022 1:00:00 AM

A2=2/9/2022 11:00:00 PM

A3=2/14/2022 12:00:00 AM

Simply using the AVERAGE function for (A1:A3) yields "2/10/2022 16:00".  My expected answer is "12:00 AM" so this does not work.  When I strip out the date and use just the time, the average function returns: "8:00:00 AM", also incorrect.  I think I need to compensate for AM and PM values somehow. 

The datetime input value (regardless of formatting) is useful in calculating the differences between a start and finish time with different days, so I am not considering changing to a text or other version of data input.  The start and finish time differences compensate for AM/PM by using the MOD function.  For example, =MOD(A1-A2,1)*24 returns the correct value of "12:00:00 AM".  I haven't had any issues with finding the difference between two times with this formula and function.
I'd like to find an AVERAGE function formula that also corrects for AM/PM using these same datetime or timestamp data entries.

I am out of ideas and could really use some help on this.  

Thanks in advance.

[#2]

@ChadOwen How do you define "average time". Excel stores dates and times as numbers. Days are counted sequentially with day 1 being Jan 1, 1900. One full day contains 24 hours. So, 1:00 AM is, represented by the number 1/24 = 0.04166667. Similarly, 11:00 PM = 0.95833333 and 12:00 AM (midnight) = 0.0.

Average those three numbers and you arrive at 0.333333, which is the time value for 8:00 AM. So, why do you expect it to be 12:00 AM (midnight = 0) ?

[#3]

Understood. Perhaps it is my terminology that is causing confusion. I was looking to find the average of several datetimes. Without the date information, a group of all "pm" times or all "am" times on the same day average just fine. When "am" and "pm" times were both present in the range, the =AVERAGE values were not what I was looking for. I was returning the values as per the summary that you referenced above. Since my post, I have gone back over my data and considered the issue further and the answer was in the date portion of the data. I had been inputting my datetimes incorrectly. For instance, if the entry for start time was "5/7/2022 11:00:00 PM" and the finish time was input as "5/7/2022 1:00:00 AM", the =AVERAGE would not return "12:00 AM". Once I realized that after midnight, the date portion would increase by 1 (input as "5/8/2022 1:00:00 AM"), the function could return the correct value that I was seeking. The start and finish times are for a single day, but technically the times span days, so I needed to adjust the after midnight datetimes to reflect that midnight had passed and a new calendar day had started. It was a case of Garbage In Garbage Out. Another benefit of correcting the datetime data was that the "=MOD(A1-A2,1)*24" formula could be replaced with a much simpler one ("end time"-"start time") which is much tidier and simple. Operator Error indeed. Thank you so much for the timely reply - I truly appreciate it.

[#4]

@ChadOwen 

the problem i think still exists(at least in my case)

say those timestamps are your bedtimes for 3 consecutive days.

so here, 

A1=2/8/2022 1:00:00 AM

A2=2/9/2022 11:00:00 PM

A3=2/10/2022 12:00:00 AM

Now, using the logic you mentioned the avg. time would be 8:00:00 AM, which is correct as per just the heuristics by which avg. of timestamps would be calculated but, clearly your avg. bedtime is at night not in the morning, and assuming the zero point at 24:00HRS(12 AM midnight).

The new transformed HH:MM: SS timestamp(taking the date part out) would be,

A1=2/8/2022 1:00:00 AM -> 1

A2=2/9/2022 11:00:00 PM -> -1

A3=2/10/2022 12:00:00 AM -> 0

the avg. value(mathematically and as expected) should be 0 (1-1+0) which can be interpreted as 12:AM (your avg. bedtime for those days).

I've been looking for the solution to this problem in SQL/Excel but no luck. Hoping someone could help with the maths that would take into account all the edge cases.

Thanks!!