Forum Discussion
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 w...
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) ?
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.