Forum Discussion
Calculating the average time between tasks
I have a table of date/times when a task started and when a task closed. I calculated the time between the two by using =TEXT(O3-N3,"h:mm:ss") in the TimeLapsed column.
Now, I want to find the Average amount of time lapsed. If I do =AVERAGE(E21,E22,E23) on the TimeLapsed cells, I get #DIV/0!
How can I determine the average amount of time between StartTask and CloseTask?
| StartTask | CloseTask | TimeLapsed |
| 2023-03-09 10:46:26 | 2023-03-09 10:59:22 | 0:12:56 |
| 2023-03-09 10:45:39 | 2023-03-09 10:46:19 | 0:00:40 |
| 2023-03-09 10:36:53 | 2023-03-09 10:41:30 | 0:04:37 |
| 2023-03-09 10:26:52 | 2023-03-09 10:32:09 | 0:05:17 |
1 Reply
Write one of the following (whichever you understand best) to convert text to numeric time:
=--TEXT(O3-N3,"[h]:m:s")
or
=1*TEXT(O3-N3,"[h]:m:s")
or
=VALUE(TEXT(O3-N3,"[h]:m:s"))
Format the cells as Custom [h]:mm:ss .
Note that I use [h] instead of just h. That allows for the possibility that time exceeds or equals 24 hours.
And note that I use VALUE, not TIMEVALUE. Again, that allows for the possibility that time exceeds or equals 24.
Even if you know the possibility does not exist for your current data, it is a good habit to get into, and there is no harm in always doing it.
Also, format the AVERAGE cells as Custom [h]:mm:ss . But you might consider [h]:mm:ss.000 , since the average might include fractional seconds.
