Forum Discussion

ChadOwen's avatar
ChadOwen
Copper Contributor
May 09, 2022

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...
Formulas and Functions
theflippedbit's avatar
theflippedbit
Copper Contributor
Oct 19, 2022

ChadOwen 

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!!

Resources