How do I calculate the time difference between two datetimes in excel and then get the average time?

68 Views Asked by At

I have a dataset that I download from an application (no movement in changing the format of data)

**Created Date       | Reviewed Date**
2023-10-25T09:55:47  | 2023-10-28T10:23:56
2023-10-25T09:55:47  | 2023-10-28T10:23:56
2023-10-25T09:55:47  | *null*
2023-09-25T09:55:47  | 2023-09-28T10:23:56
2023-10-25T09:55:47  | 2023-10-28T10:23:56
2023-10-25T09:55:47  | 2023-10-27T10:23:56
2023-09-25T09:55:47  | 2023-10-28T10:23:56
2023-09-25T09:55:47  | 2023-10-28T10:23:56

I want to work out the average review time. This needs to be in excel.

My workings so far:

  1. Convert the datetime format using =DATEVALUE(MID(H2,1,10))+TIMEVALUE(MID(H2,12,8)) which gives me:
**Created Date    | Reviewed Date**
25/10/2023 09:55  | 28/10/2023 10:23
25/10/2023 09:55  | 28/10/2023 10:23
25/10/2023 09:55  | *null*
25/09/2023 09:55  | 28/09/2023 10:23
25/10/2023 09:55  | 28/10/2023 10:23
25/10/2023 09:55  | 27/10/2023 10:23
25/09/2023 09:55  | 28/10/2023 10:23
25/09/2023 09:55  | 28/10/2023 10:23
  1. Get the datetime different between the two using =TEXT(N4-M4,"dd")&" Days " & TEXT(N4-M4,"hh:mm")&" Minutes " to get
03 Days 00:28 Minutes 
03 Days 00:28 Minutes 
#Value!
03 Days 00:28 Minutes
03 Days 00:28 Minutes
02 Days 00:28 Minutes
02 Days 00:28 Minutes
33 Days 00:28 Minutes
33 Days 00:28 Minutes
  1. Get average time from all the values

Here is where I can't move any further. I have tried =AVERAGE(daterange).

Expected output:

Average time to review: 18 Days 00:28 Minutes

0

There are 0 best solutions below