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.