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

Kathyrn,
I hate leaving someone hanging, but as for as question 1, I tried a
number of variations using the YEAR, MONTH, TODAY functions trying to
calculate the number of months, but no joy. I would recommend posting
that question again separately from the other. I'm sure one of the
Excel gurus will have a solution.

Sorry I couldn't be more help.


--
Casey


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

Casey,
Thanks for the help on this one. Was hoping not to build another table, but
your solution is working great.

Any ideas on my first problem??

"Casey" wrote:


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

What is the first problem? Your first posting (with 2 problems) doesn't
appear, only Casey's response to it.

Pete

Casey,
Thanks for all your help!

"Casey" wrote:


Kathyrn,
I hate leaving someone hanging, but as for as question 1, I tried a
number of variations using the YEAR, MONTH, TODAY functions trying to
calculate the number of months, but no joy. I would recommend posting
that question again separately from the other. I'm sure one of the
Excel gurus will have a solution.

Sorry I couldn't be more help.


--
Casey


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

Pete,
Here it is:
I need 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

C=Open date
D=Close date
The purpose it to track the number of months a case is in open status, but I
need it to stop counting when a close date is entered.

Thanks for any assistance.


Question
Subject: Need help with formula 2/8/2006 10:04 AM PST

By: Kathryn J Bittman In: microsoft.public.excel.worksheet.functions

Pete,
Here it is:
I need 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

C=Open date
D=Close date
The purpose it to track the number of months a case is in open status, but I
need it to stop counting when a close date is entered.

Thanks for any assistance.



"Pete" wrote:

What is the first problem? Your first posting (with 2 problems) doesn't
appear, only Casey's response to it.

Pete

Try this formula in E2:

=IF(D2="","",ROUNDUP((D2-C2)/30,0))

Format the cell as Number with 0 decimal places. This assumes 30 days
to a month, and will give you the number of months between D2 and C2 if
D2 is not blank. Copy the formula down.

Is this what you wanted?

Pete

Having re-read your posting, I think you need a slight amendment to my
suggested formula, as follows:

=IF(D2="",ROUNDUP((TODAY()-C2)/30,0),ROUNDUP((D2-C2)/30,0))

This will give you the number of months to date, unless there is a
close date which will give the number of months between start and
close.

Hope this helps.

Pete

Pete,
It worked like a charm! Thanks for the assist.

"Pete" wrote:

Having re-read your posting, I think you need a slight amendment to my
suggested formula, as follows:

=IF(D2="",ROUNDUP((TODAY()-C2)/30,0),ROUNDUP((D2-C2)/30,0))

This will give you the number of months to date, unless there is a
close date which will give the number of months between start and
close.

Hope this helps.

Pete