How to get average time difference across timestamps in excel?-CodePudding

I have a table in excel with each row corresponding to an arrival time at an office. I would like to extract the average difference between arrival times across all rows so that I can simulate an arrival pattern. I am not looking for the average time but the average difference in time between each subsequent row.

Arrival
2001-02-17 08:02
2021-02-17 08:35
2021-02-17 08:38
2021-02-17 09:22
2021-02-17 09:23
...
2001-02-17 13:02
2021-02-17 13:22
2021-02-17 14:04
2021-02-17 16:32
2021-02-17 17:00

CodePudding user response：

You can use:

=(MAX(A2:A11)-MIN(A2:A11))/(COUNT(A2:A11)-1)

CodePudding user response：

Try AVERAGE() function. First extract times from datetime value then calculate average. Try-

=AVERAGE(TIME(HOUR(A1:A10),MINUTE(A1:A10),SECOND(A1:A10)))

For older version of excel you may need to enter formula as array entry means put formula to cell then press CTRL SHIFT ENTER.