I need actual two different formulas

1) The first is to count dates in months for start to current, but to stop
counting if an end date is listed. - i.e.
c2 = 01/01/2006
d2 = blank
C3 = 02/01/2003
D3 = 11/31/2005

2) This one needs to filter for a criteria from one field and calculate the
results from 2 others. All the "Y" need to be counted in column D and all the
"N" need to be ignored. This field is called emergency. I then need to know
the number of days between a request and completion that is greater than 7,
but less than 16.

Here is an example:

D=emergency (Y/N)
E=request (Date)
F=Completion (Date)

d2=y
e2=01/01/2006
f2=01/10/2006
d3=n
e3=01/01/2006
f3=01/10/2006

All help will be really appreciated!

Kathryn,
I think I can help on your second question, but I'm not understanding
the first one. For your second question, try the following formula in
an empty cell in row 2.

=IF(AND(D2="y",(F2-E2)7,(F2-E2)<16),"Meets Criteria","Does not meet
Criteria")

HTH


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=510158

Casey,
It is getting closer, but a need a count of episodes within the number of
days - not "meets/does not meet criteria". I need to know how many times an
emergency situation is handled more than 7 days and less than 16 days. They
could be as many as 900 rows of data and we are trying to identify periods of
compliance.

As to the first problem:
I need a count of months the file has been with the company in open status.
My problem is getting it to stop counting when I enter a close date.

"Casey" wrote:


Kathryn,
I think I can help on your second question, but I'm not understanding
the first one. For your second question, try the following formula in
an empty cell in row 2.

=IF(AND(D2="y",(F2-E2)7,(F2-E2)<16),"Meets Criteria","Does not meet
Criteria")

HTH


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=510158

Kathryn,
Question 2 try this formula
=IF(AND(D2="y",(F2-E2)7,(F2-E2)<16),1,0)
copy paste down as far as you need.
Then say these formulas are all in column "G" In some cell not in
column "G" enter SUM(G:G) and you will have a count of all the row
records meeting your criteria.

Question 1
Define these: which is close date, received date ect. and does this
group of 4 cells occur once on a sheet or does this repeat for 900+
records?
c2 = 01/01/2006
d2 = blank
C3 = 02/01/2003
D3 = 11/31/2005


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=510158

Casey,
I am so sorry for being dense. Regarding Q2:

I am working on a summary sheet and actually need a breakdown for 8-15 days,
16-30 days and finally 31-45 days so copying the formula into a full column
isn't going to work.

Here are the actual formulas (thanks to your assistance) I am working with
the actual named ranges/worksheets.
Number of applications at 8 - 15 days
=IF(AND((Expedite="y"),('Data Entry'!Q2:Q240)7,('Data
Entry'!Q2:Q240)<16),1,0)

Number of applications at 16 - 30 days
=IF(AND((Expedite="y"),('Data Entry'!Q2:Q240)15,('Data
Entry'!Q2:Q240)<31),1,0)

Number of applications at 31 - 45 days
=IF(AND((Expedite="y"),('Data Entry'!Q2:Q240)30,('Data
Entry'!Q2:Q240)<46),1,0)

Now I am getting the answer as 1 for each line, but actual it should read
0,1,1 from my sample data.

What am I not getting? I know this should be fairly straight forward, but....

As to Q1 - entries will be made for start/end in each row.

"Casey" wrote:


Kathryn,
Question 2 try this formula
=IF(AND(D2="y",(F2-E2)7,(F2-E2)<16),1,0)
copy paste down as far as you need.
Then say these formulas are all in column "G" In some cell not in
column "G" enter SUM(G:G) and you will have a count of all the row
records meeting your criteria.

Question 1
Define these: which is close date, received date ect. and does this
group of 4 cells occur once on a sheet or does this repeat for 900+
records?
c2 = 01/01/2006
d2 = blank
C3 = 02/01/2003
D3 = 11/31/2005


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=510158

Kathryn,
If I understand you correctly, you are entering the formulas on a
different worksheet you are using to hopefully summarize you data.
Are you entering each formula into 240 rows, one column for each
formula on your summary sheet?
I created 240 rows of data matching yours and the formulas worked fine
when entered into columns A-D respectively on a different sheet. **I
added a formula for 45 days also I modified the formulas a bit using
<= and = in places to handle when the formulas actually hit on say 16
days.

Here are my four formulas in columns A-D from row 2-240
=IF(AND((Expedite="y"),('Data Entry'!Q2:Q240)7,('Data
Entry'!Q2:Q240)<16),1,0)
=IF(AND((Expedite="y"),('Data Entry'!Q2:Q240)=16,('Data
Entry'!Q2:Q240)<31),1,0)
=IF(AND((Expedite="y"),('Data Entry'!Q2:Q240)=31,('Data
Entry'!Q2:Q240)<=45),1,0)
=IF(AND((Expedite="y"),('Data Entry'!Q2:Q240)45),1,0)

I then Sum each column to get the count of criteria met.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=510158