adding columns - having serious difficulties

[Katie]

Subject: Adding columns
From: "Katie" <[email protected]>
Sent: 9/2/2004 8:43:00 AM




A B C D E F G H
1 - 07/08/04 07/12/04 5 07/12/04 07/30/04 19 23
2 - 05/21/04 05/25/04 5 5
3 - 05/26/04 06/24/04 30 30
4 - -1
5 - 07/14/04 07/26/04 13 07/26/04 07/21/04 1 13

=IF(M7=O7,N(Q7)+N(N7)-1,N(Q7)+N(N7))
Can anyone help - I am using the formula above to
determine the number of days a person in in a Nursing Home
in column a & B -- and number of days in a Skilled Nursing
Facility in E & F - my formula needed to tell if C & E
were same day to not count them twice and if A & B are
same day to count it as one day -- the problem I am having
is in line 4 in above example - if there is no information
for the month - it puts a -1 in column H - I need to have
a 0 if it is blank or I see it as they owe me one day of
costs. Can you tell me how to fix it? I am not good at
computer language -- Thanks so much and have a great
day !!
I have since tried =IF(COUNTA(N7,M7,O7,Q7)=0,0,IF(M&-O7,N
(Q7)+N(N7)-1,N(Q7)+N(N7))) now I get a FALSE in my box -
is there someone out there who can help?

 

[Phil Webb]

Katie,
I was not clear on the description of your data and exactly what you are
trying to do, but here's what I think you have:

Column A = Date Into Nursing Home
Column B = Date Out of Nursing Home
Column C = Number of Days Stay in Nursing Home
Column D = Date Into Skilled Nursing facility
Column E = Date Out of Skilled Nursing Facility
Column F = Number of Days Stay in Skilled Nursing Facility
Column G = Number of Total Days Stay in Nursing Facility and Skilled Nursing
Facility

If the above is true, then try these formulas (assumes your data starts in
A2):
In cell C2: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),B2-A2+1,0)
In cell F2: =IF(AND(ISNUMBER(D2),ISNUMBER(E2)),IF(D2=B2,E2-D2,E2-D2+1),0)
In cell G2: =C2+F2

Note the formulas above can yield negative numbers if you enter a "date in"
that is greater than a "date out". The formula also allots any common day
as a day in the nursing home rather than in the skilled nursing facility.
This can easily be adjusted by modifying the formulas in C2 and F2.

If these formulas work properly in row 2, copy each one down in their
respective columns. If I have misinterpreted your problem, please
elaborate. I could not determine why you were referring to cells M, N, O,
and Q in your formula when your sample data does not include these columns.
Also, it seems to me that the sample data in your row 5 is incorrect. A
date in of 7/26/04 cannot be later than the date out (7/21/04).
Regards,
Phil Webb