IF/Nesting When Calculation Involves a Date

[Annie]

Any Nested IF help is gratefully received and acknowledged . . .

I have a calculation I need to perform for number of days to close a case
that is calculating based on subtracting one cell from another:

January 1, 2008 - January 15, 2008 = 14 days

There are three possible outcomes:

Case has an end date and it just needs to calculate the difference between
the two cells:

=SUM(R4-E4)

Case opened and closed on the same day (so it shows up as 0, but really
needs to be a 1. The forumula below is working for this outcome):

=IF(R4-E4=0,1,R4-E4)

Case is still open (no end date entered) -- this one shows up on my
worksheet as 39,472 using the same formula above -- I cannot figure out how
to have it just show "Open"

How can I put together a formula that provides the correct answer based on
all three conditions:

Open No end date entered yet
1 Number of Days to Close Case = 0
Perform the calculation between open and closed dates

 

[Bob Phillips]

=IF(R4-E4=0,1,IF(R4<>"",R4-E4,"Open"))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Annie]

Bob, you are my HERO! Truly, thank so very much. I'm going to have to
figure out what all this means so I can replicate it in the future.

Again, THANKS!

Ann

 

[Dave]

H Annie,
When you leave out the end date, you will get -39472, not 39472.
Therefore you just need another if statement to test for an answer less than
zero.
=IF(R4-E4<0,0,IF(R4-E4=0,1,R4-E4))
Regards - Dave.