New Contributor

# Finding the average time using varied datestamps

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.