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:
- 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
- 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
- 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