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
Oct 19 2022 09:35 AM
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!!