Need help with formula
 

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!

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Pete

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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

Need help with formula
 

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