May 09 2022 04:54 PM
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.
May 09 2022 08:33 PM - edited May 09 2022 08:37 PM
@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) ?
May 10 2022 12:14 AM