I need help with formulas counting dates.

I have open dates and close dates. I need to know the average, minimum and
maximum time a case is open by year. I have figured out the array for the
total period (2002-2006), but can't break it out the same by individual year.

I also am having a problem getting a field to remain blank if no close date
has been entered. Currently it reports 01/00/00 for blanks.

Any/all help appreciated!

Your description does not tell me how to account for a case that is started
in one year and closed in the next. These will only work if started and
closed in the same year, but are easily adaptable.

Assuming start date in column B, close date in column C, headers in Row 1,
and cases down to row 250.
I would type in say column Z, in Z1 1/1/2002, n Z2 1/1/2003, in Z31/1/2004
and so on up to 1/1/2007 just to simplify the formula.

Average for 2002
=sumproduct(--($C$2:$C$2500),--($B$2:$B$250=Z1),--($C$2:$C$250<Z2),--($C$2:$C$250-$B$2:$B$250+1))/sumproduct(--($C$2:$C$2500),--($B$2:$B$250=Z1),--($C$2:$C$250<Z2))
copydown for later years

Min for 2002 (in say AB1)
=MIN(IF($C$2:$C$2500,IF($B$2:$B$250=Z1,IF($C$2:$ C$250<Z2,$C$2:$C$250-$B$2:$B$250+1))))
This in an array formula, you must commit with control+shift+enter
Max (say in AC1) is the same as min except trade out the MIN for MAX

When you copy down the min and max functions, you must not paste over the
cell you copied, just select the rows below to paste.

If you want this adjusted for how to account for a case that is started in
one year and closed in the next, just respond to this thread and I'll see it
today or tomorrow.



"Kathryn J Bittman" wrote in
message ...
I need help with formulas counting dates.

I have open dates and close dates. I need to know the average, minimum and
maximum time a case is open by year. I have figured out the array for the
total period (2002-2006), but can't break it out the same by individual
year.

I also am having a problem getting a field to remain blank if no close
date
has been entered. Currently it reports 01/00/00 for blanks.

Any/all help appreciated!

Bob,
Thanks for the help. Still not getting it yet, but closer. Does it matter
that all the dates for open are the first of the month and all the close
dates are the last day of the month? I am trying on use the year without
month specification in the Z1 part of your formula. Is that why I am not
getting the result I want?

I will need the help with the latter situation of open this year, but closed
the next.

"Bob Tarburton" wrote:

Your description does not tell me how to account for a case that is started
in one year and closed in the next. These will only work if started and
closed in the same year, but are easily adaptable.

Assuming start date in column B, close date in column C, headers in Row 1,
and cases down to row 250.
I would type in say column Z, in Z1 1/1/2002, n Z2 1/1/2003, in Z31/1/2004
and so on up to 1/1/2007 just to simplify the formula.

Average for 2002
=sumproduct(--($C$2:$C$2500),--($B$2:$B$250=Z1),--($C$2:$C$250<Z2),--($C$2:$C$250-$B$2:$B$250+1))/sumproduct(--($C$2:$C$2500),--($B$2:$B$250=Z1),--($C$2:$C$250<Z2))
copydown for later years

Min for 2002 (in say AB1)
=MIN(IF($C$2:$C$2500,IF($B$2:$B$250=Z1,IF($C$2:$ C$250<Z2,$C$2:$C$250-$B$2:$B$250+1))))
This in an array formula, you must commit with control+shift+enter
Max (say in AC1) is the same as min except trade out the MIN for MAX

When you copy down the min and max functions, you must not paste over the
cell you copied, just select the rows below to paste.

If you want this adjusted for how to account for a case that is started in
one year and closed in the next, just respond to this thread and I'll see it
today or tomorrow.



"Kathryn J Bittman" wrote in
message ...
I need help with formulas counting dates.

I have open dates and close dates. I need to know the average, minimum and
maximum time a case is open by year. I have figured out the array for the
total period (2002-2006), but can't break it out the same by individual
year.

I also am having a problem getting a field to remain blank if no close
date
has been entered. Currently it reports 01/00/00 for blanks.

Any/all help appreciated!

This should work based on those specs.
Post some examples of the date format you are using or e-mail me a sample
spreadsheet.
Just remove the "_removethis_" from my email address.


"Kathryn J Bittman" wrote in
message ...
Bob,
Thanks for the help. Still not getting it yet, but closer. Does it matter
that all the dates for open are the first of the month and all the close
dates are the last day of the month? I am trying on use the year without
month specification in the Z1 part of your formula. Is that why I am not
getting the result I want?

I will need the help with the latter situation of open this year, but
closed
the next.

"Bob Tarburton" wrote:

Your description does not tell me how to account for a case that is
started
in one year and closed in the next. These will only work if started and
closed in the same year, but are easily adaptable.

Assuming start date in column B, close date in column C, headers in Row
1,
and cases down to row 250.
I would type in say column Z, in Z1 1/1/2002, n Z2 1/1/2003, in
Z31/1/2004
and so on up to 1/1/2007 just to simplify the formula.

Average for 2002
=sumproduct(--($C$2:$C$2500),--($B$2:$B$250=Z1),--($C$2:$C$250<Z2),--($C$2:$C$250-$B$2:$B$250+1))/sumproduct(--($C$2:$C$2500),--($B$2:$B$250=Z1),--($C$2:$C$250<Z2))
copydown for later years

Min for 2002 (in say AB1)
=MIN(IF($C$2:$C$2500,IF($B$2:$B$250=Z1,IF($C$2:$ C$250<Z2,$C$2:$C$250-$B$2:$B$250+1))))
This in an array formula, you must commit with control+shift+enter
Max (say in AC1) is the same as min except trade out the MIN for MAX

When you copy down the min and max functions, you must not paste over the
cell you copied, just select the rows below to paste.

If you want this adjusted for how to account for a case that is started
in
one year and closed in the next, just respond to this thread and I'll see
it
today or tomorrow.



"Kathryn J Bittman" wrote in
message ...
I need help with formulas counting dates.

I have open dates and close dates. I need to know the average, minimum
and
maximum time a case is open by year. I have figured out the array for
the
total period (2002-2006), but can't break it out the same by individual
year.

I also am having a problem getting a field to remain blank if no close
date
has been entered. Currently it reports 01/00/00 for blanks.

Any/all help appreciated!