Average wait time

**evelynl** · 08-06-2009, 03:21 PM

Hi, there.
I need a function that will tell me the average wait and the median wait in a column of about 600 pieces of data. the data is in the format of years months and days (i.e., 1 years, 0 months, 4 days).
The data is excel created. i got it by comparing two strings of dates (start date and end date) using the following function (not sure if that matters, but thought i'd include the info):
=YEAR(E2)-YEAR(D2)-IF(OR(MONTH(E2)<MONTH(D2),AND(MONTH(E2)=MONTH(D2), DAY(E2)<DAY(D2))),1,0)&" years, "&MONTH(E2)-MONTH(D2)+IF(AND(MONTH(E2) <=MONTH(D2),DAY(E2)<DAY(D2)),11,IF(AND(MONTH(E2)<MONTH(D2),DAY(E2) >=DAY(D2)),12,IF(AND(MONTH(E2)>MONTH(D2),DAY(E2)<DAY(D2)),-1)))&" months, "&E2-DATE(YEAR(E2),MONTH(E2)-IF(DAY(E2)<DAY(D2),1,0),DAY(D2))&" days"
I was wondering whether I have to now convert it into days to figure out the average wait?
Any help would be much appreciated.

**arthurbr** · 08-06-2009, 03:28 PM

Hi and welcome to the board,
if you could post a small sample of your data and the format it's in, it would be a great help

**evelynl** · 08-06-2009, 05:39 PM

Thanks so much.
Data looks like this:

CASE FILED CASE CLOSED COURT WAIT

6/8/2005 6/12/2006 1 years, 0 months, 4 days
5/25/2004 1/25/2005 0 years, 8 months, 0 days
10/27/2005 9/11/2006 0 years, 10 months, 15 days
5/10/2007 4/15/2008 0 years, 11 months, 5 days
12/29/2006 9/19/2008 1 years, 8 months, 21 days
11/14/2006 1/5/2009 2 years, 1 months, 22 days

**DonkeyOte** · 08-07-2009, 03:23 AM

evelynl, if we assume Case Filed Dates are listed in A2:A7, Case Closed Dates in B2:B7 then Wait can be seen in terms of days in C2:C7 using:

C2: =B2-A2
copied down

Your Average Wait in days is then:

D1: =AVERAGE(C2:C7)

Your Median in Days is then:

E1: =MEDIAN(C2:C7)

If you want to achieve the above without using the formulae in C2:C7 you can use Arrays

D1: =AVERAGE(B2:B7-A2:A7)
confirmed with CTRL + SHIFT + ENTER

E1: =MEDIAN(B2:B7-A2:A7)
confirmed with CTRL + SHIFT + ENTER

**evelynl** · 08-07-2009, 03:06 PM

Thank you so much for your reply.
Unfortunately, I don't get a number of days when I subtract the closed date from the open date, what i get is another date. (e.g. for the first one, i get 1/3/1901.)
That's what led me to hunt around for the more complicated formula.
I don't know what I could be doing wrong. =E2-D2 is pretty simple. And the field is formatted as a date.
Thoughts on how I could be messing that up?

**DonkeyOte** · 08-07-2009, 03:23 PM

Unfortunately, I don't get a number of days when I subtract the closed date from the open date, what i get is another date. (e.g. for the first one, i get 1/3/1901.)

Format the cell containing the formula to be General (not Date).

**evelynl** · 08-07-2009, 04:37 PM

yes!!!!
hooray!!!
thankyou thankyou thankyou thankyou thankyou.....

**arthurbr** · 08-08-2009, 02:32 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save