Date Range Multiple Columns

[PCCS13]

I have a list of cases which includes the date they were opened and the date they were closed. I need for formula which will check which cases remained open at the end of the year. I am working across several years, starting from April 2007.

For example, if a case was opened in 12/06/2007 and closed in 05/05/2008, then it must have remained open at the end of 2007/08. Therefore how many cases like this were closed during a year AFTER they year they were opened.

Hope this makes sense. I am using Excel 2003.

Thanks for looking at this problem. I suspect there is an easy solution and its not occuring to me.

 

[JoeMo]

This will tell you how many were opend & closed in a specific year. You can get the output you need from there. In the example below ten cases were opened in 2007 and 7 of those closed in 2007 so 3 cases were still open at the start of 2008.
Sheet6

*	A	B	C	D	E	F
Case #	Opened	Closed	*	Open/Close in
*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]2007[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/7/2007[/TD]
[TD="align: right"]1/2/2008[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2/3/2007[/TD]
[TD="align: right"]12/31/2007[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2/4/2007[/TD]
[TD="align: right"]12/31/2009[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2/5/2007[/TD]
[TD="align: right"]1/31/2008[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2/6/2007[/TD]
[TD="align: right"]12/31/2007[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2/7/2007[/TD]
[TD="align: right"]12/31/2007[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2/8/2007[/TD]
[TD="align: right"]12/31/2007[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2/9/2007[/TD]
[TD="align: right"]12/31/2007[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2/10/2007[/TD]
[TD="align: right"]12/31/2007[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/11/2007[/TD]
[TD="align: right"]12/31/2007[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =SUMPRODUCT(--(YEAR(B2:B11)=$F$1)*--(YEAR(C2:C11)=$F$1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[PCCS13]

Hi JoeMo

This works a treat, and I have amended it to check for each of the years following 2007. However I didn't explain clearly enough a couple of factors.

The year isn't a calendar year, its 1 April 2007 to 31 March 2008, and so on for each year afterwards.

I want to identify the cases in question to analyse the time taken to complete them, or time they were open.

Is there a way of doing this?

Thanks for taking the time to solve my original query.

J

 

[JoeMo]

Hi JoeMo

This works a treat, and I have amended it to check for each of the years following 2007. However I didn't explain clearly enough a couple of factors.

The year isn't a calendar year, its 1 April 2007 to 31 March 2008, and so on for each year afterwards.

I want to identify the cases in question to analyse the time taken to complete them, or time they were open.

Is there a way of doing this?

Thanks for taking the time to solve my original query.

J

If you have the open and close dates for a case in cells A1 and B1,respectively then the duration of the case in days (say in C1) is:

= B1-A1

independent of whether your fiscal year coincides with the calendar year or not.