Need help with formula

[Kathryn J Bittman]

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!

 

[Casey]

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

 

[Kathryn J Bittman]

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]

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 i
column "G" enter SUM(G:G) and you will have a count of all the ro
records meeting your criteria.

Question 1
Define these: which is close date, received date ect. and does thi
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/200

 

[Kathryn J Bittman]

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]

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]

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

Sorry I couldn't be more help

 

[Kathryn J Bittman]

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??

 

[Pete]

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

Pete

 

[Kathryn J Bittman]

Casey,
Thanks for all your help!

 

[Kathryn J Bittman]

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]

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

 

[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

 

[Kathryn J Bittman]

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