Avg. Difference between dates

[Matt]

Say I have the following dates, where the left column is the date the
item was opened and the right column is the date it was closed (blank
if not yet closed):

Open Closed
3/8/07 5/2/07
4/16/07 --
1/30/07 3/4/07
3/20/07 4/15/07
2/12/07 --

I would like to have a function that would give the average number of
days between open and close dates. However: if the item is still
open, the number of days should be assumed to be today's date minus
the open date. But: if the today's date minus the open date is less
than 30 days, it should not be counted toward the average at all. Any
ideas?

 

[Bernard Liengme]

Use a helper column with =IF(B2>0,B2-A2,IF(TODAY()-A2>30,TODAY()-A2,""))
Average the helper column values (blanks will not be included in AVERAGE)
best wishes

 

[Matt]

Can this be done using a single function? Perhaps a CSE formula?

 

[Bernard Liengme]

I cannot see how, with the two blank conditions.
A User Defined Function would work, I imagine. Interested?

 

[Matt]

Tried using ={average(...)} and it worked for for me. Thanks!