Finding the average time using varied datestamps

%3CLINGO-SUB%20id%3D%22lingo-sub-3347779%22%20slang%3D%22en-US%22%3EFinding%20the%20average%20time%20using%20varied%20datestamps%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3347779%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%20I%20am%20a%20complete%20newbie%20to%20this%20community.%26nbsp%3B%20I'm%20an%20infrequent%20and%20inexperienced%20user%20of%20Excel.%26nbsp%3B%20Prior%20to%20this%20plea%20for%20assistance%2C%20I've%20spent%20a%20considerable%20amount%20of%20time%20researching%20how%20to%20work%20with%20time%20and%20average%20time%20in%20Excel%20and%20have%20not%20yet%20been%20able%20to%20create%20a%20formula%20to%20take%20datetimes%20that%20span%20midnight%20and%20provide%20an%20accurate%20average%20time%20in%20h%3Amm%20format.%26nbsp%3B%20I%20can't%20find%20a%20way%20to%20account%20for%20the%20am%2Fpm%20differences%20and%20am%20seeking%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20example%2C%20let's%20say%20in%20cells%20A1%3AA3%20I%20have%20the%20following%20data%3A%3C%2FP%3E%3CP%3EA1%3D2%2F8%2F2022%201%3A00%3A00%20AM%3C%2FP%3E%3CP%3EA2%3D2%2F9%2F2022%2011%3A00%3A00%20PM%3C%2FP%3E%3CP%3EA3%3D2%2F14%2F2022%2012%3A00%3A00%20AM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimply%20using%20the%20AVERAGE%20function%20for%20(A1%3AA3)%20yields%20%222%2F10%2F2022%2016%3A00%22.%26nbsp%3B%26nbsp%3BMy%20expected%20answer%20is%20%2212%3A00%20AM%22%20so%20this%20does%20not%20work.%26nbsp%3B%20When%20I%20strip%20out%20the%20date%20and%20use%20just%20the%20time%2C%20the%20average%20function%20returns%3A%20%228%3A00%3A00%20AM%22%2C%20also%20incorrect.%26nbsp%3B%20I%20think%20I%20need%20to%20compensate%20for%20AM%20and%20PM%20values%20somehow.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20datetime%20input%20value%20(regardless%20of%20formatting)%20is%20useful%20in%20calculating%20the%20differences%20between%20a%20start%20and%20finish%20time%20with%20different%20days%2C%20so%20I%20am%20not%20considering%20changing%20to%20a%20text%20or%20other%20version%20of%20data%20input.%26nbsp%3B%20The%20start%20and%20finish%20time%20differences%20compensate%20for%20AM%2FPM%20by%20using%20the%20MOD%20function.%26nbsp%3B%20For%20example%2C%26nbsp%3B%3DMOD(A1-A2%2C1)*24%20returns%20the%20correct%20value%20of%20%2212%3A00%3A00%20AM%22.%26nbsp%3B%20I%20haven't%20had%20any%20issues%20with%20finding%20the%20difference%20between%20two%20times%20with%20this%20formula%20and%20function.%3CBR%20%2F%3EI'd%20like%20to%20find%20an%20AVERAGE%20function%20formula%20that%20also%20corrects%20for%20AM%2FPM%20using%20these%20same%20datetime%20or%20timestamp%20data%20entries.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20am%20out%20of%20ideas%20and%20could%20really%20use%20some%20help%20on%20this.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3347779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3348358%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20average%20time%20using%20varied%20datestamps%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3348358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1385361%22%20target%3D%22_blank%22%3E%40ChadOwen%3C%2FA%3E%26nbsp%3BHow%20do%20you%20define%20%22average%20time%22.%20Excel%20stores%20dates%20and%20times%20as%20numbers.%20Days%20are%20counted%20sequentially%20with%20day%201%20being%20Jan%201%2C%201900.%20One%20full%20day%20contains%2024%20hours.%20So%2C%201%3A00%20AM%20is%2C%20represented%20by%20the%20number%201%2F24%20%3D%200.04166667.%20Similarly%2C%2011%3A00%20PM%20%3D%200.95833333%20and%2012%3A00%20AM%20(midnight)%20%3D%200.0.%3C%2FP%3E%3CP%3EAverage%20those%20three%20numbers%20and%20you%20arrive%20at%200.333333%2C%20which%20is%20the%20time%20value%20for%208%3A00%20AM.%20So%2C%20why%20do%20you%20expect%20it%20to%20be%2012%3A00%20AM%20(midnight%20%3D%200)%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3348929%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20average%20time%20using%20varied%20datestamps%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3348929%22%20slang%3D%22en-US%22%3EUnderstood.%20Perhaps%20it%20is%20my%20terminology%20that%20is%20causing%20confusion.%20I%20was%20looking%20to%20find%20the%20average%20of%20several%20datetimes.%20Without%20the%20date%20information%2C%20a%20group%20of%20all%20%22pm%22%20times%20or%20all%20%22am%22%20times%20on%20the%20same%20day%20average%20just%20fine.%20When%20%22am%22%20and%20%22pm%22%20times%20were%20both%20present%20in%20the%20range%2C%20the%20%3DAVERAGE%20values%20were%20not%20what%20I%20was%20looking%20for.%20I%20was%20returning%20the%20values%20as%20per%20the%20summary%20that%20you%20referenced%20above.%20Since%20my%20post%2C%20I%20have%20gone%20back%20over%20my%20data%20and%20considered%20the%20issue%20further%20and%20the%20answer%20was%20in%20the%20date%20portion%20of%20the%20data.%20I%20had%20been%20inputting%20my%20datetimes%20incorrectly.%20For%20instance%2C%20if%20the%20entry%20for%20start%20time%20was%20%225%2F7%2F2022%2011%3A00%3A00%20PM%22%20and%20the%20finish%20time%20was%20input%20as%20%225%2F7%2F2022%201%3A00%3A00%20AM%22%2C%20the%20%3DAVERAGE%20would%20not%20return%20%2212%3A00%20AM%22.%20Once%20I%20realized%20that%20after%20midnight%2C%20the%20date%20portion%20would%20increase%20by%201%20(input%20as%20%225%2F8%2F2022%201%3A00%3A00%20AM%22)%2C%20the%20function%20could%20return%20the%20correct%20value%20that%20I%20was%20seeking.%20The%20start%20and%20finish%20times%20are%20for%20a%20single%20day%2C%20but%20technically%20the%20times%20span%20days%2C%20so%20I%20needed%20to%20adjust%20the%20after%20midnight%20datetimes%20to%20reflect%20that%20midnight%20had%20passed%20and%20a%20new%20calendar%20day%20had%20started.%20It%20was%20a%20case%20of%20Garbage%20In%20Garbage%20Out.%20Another%20benefit%20of%20correcting%20the%20datetime%20data%20was%20that%20the%20%22%3DMOD(A1-A2%2C1)*24%22%20formula%20could%20be%20replaced%20with%20a%20much%20simpler%20one%20(%22end%20time%22-%22start%20time%22)%20which%20is%20much%20tidier%20and%20simple.%20Operator%20Error%20indeed.%20Thank%20you%20so%20much%20for%20the%20timely%20reply%20-%20I%20truly%20appreciate%20it.%3C%2FLINGO-BODY%3E
New Contributor

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 Replies

@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.