I have a column that has a date and time recorded in it like 09/19/18, 9:34 AM. I would like to find the average time between these recordings each day, then average all the days together. So if I have recordings at 9:39, 2:10, 5:30, 7:30 etc, what is the best way to format the cells to achieve this?
In another column calculate the elapsed time between two time stamps and then calculate the average over those elapsed times. So, assuming you have date+time stamps in A1, A2, A3, …, A99 then in B2 write =A2-A1, copy that formula cell to clipboard, select the target range B3:B99, paste. Then in some other cell write =AVERAGE(B2:B99). The best number format for that cell and the elapsed time cells probably would be [HH]:MM (where [HH] displays hours above 23 instead of wrapping to wall clock time).
That works well enough but I’m running into some problems. It’s rounding the values funny. Like if the time between two is slightly less then an hour (7:45 and 8:43) it displays a 0 instead of a 1. I can’t seem to figure out how to get it to display as halves either. It will only do whole hours and not say 1.5. Also it doesn’t like the transition between PM and AM. Going from 10:46PM to 12:45AM will give me -21 hours intead of the 2 I would like to see.
If the result of 7:45 subtracted from 8:43 displays as 0 then you didn’t apply the [HH]:MM number format but 0 or some such which rounds it to integer. If you want the result to be the amount of hours instead of a time value to calculate with further then multiply by 24 (because times are calculated as fraction of days), e.g. =(A2-A1)*24 and do not apply a time format but a number format instead.
Also, for subtracting 10:46PM from 12:45AM of course that needs the date information as well, not just time (btw the result is -22:01 not -21). Otherwise, for mere times if the maximum can be one day difference (i.e. only one night covered and never more than 24 hours) you can compare which time is greater to cater for overnight, like if 10:46PM in A1 and 12:45AM in A2 then =A2-A1+(A1>A2) or =(A2-A1+(A1>A2))*24 for the number of hours.
=A2-A1+(A1>A2) is what worked for me. And you’re right I had the sum column set to a number instead of time like it should have been.
If this solves your problem then please mark the answer as correct by clicking on the check mark. Thanks.