IF/Nesting When Calculation Involves a Date

  • Thread starter Thread starter Annie
  • Start date Start date
A

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
 
=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)
 
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
 
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.
 
Back
Top