NickA
NickA

Reputation: 433

How to get average time difference across timestamps in excel?

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

Upvotes: 0

Views: 471

Answers (2)

Jos Woolley
Jos Woolley

Reputation: 9052

You can use:

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

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36780

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.

enter image description here

Upvotes: 0

Related Questions