Date Calculation with exceptions

[copgal09]

I have created a log that will calculate number of days between an inmate's intake date and release date using Excel 2010.
BUT, when the inmates are taken in, I don't want the day of intake to be counted EXCEPT when the inmate is taken in and released on the SAME date. I don't know how to make the formula count days when there are two or more days, but NOT count the day of intake.
I am using the hidden DATEIF function.
I'm sure this is a "duh" for most of you, but for me it has been a three-day constant frustration.
Currently using:
=DATEDIF(A2,G2,"D") to calculate number of days between dates but can't find the way to add the exclusion of start date when more than 2 days are the returned value. Does this make any sense?

 

[copgal09]

I am able to get the current formula to return correctly when there IS data in the A2 (intake date) and G2 (release date), but the problem is when that formula is copied into the cells below, where NO data has yet been entered, the formula returns a "1", and I need it to either remain BLANK or to return a "0". That "number of days incarcerated" column has an auto sum at the bottom of the page, and all those ones (1) skew my value for total number of days of incarceration on that page! Am I confusing the situation?

 

[Rick Rothstein]

Hi, Rick!
Here's the scoop:
1) 9/1/2014 to 9/1/2014 should be 1
2) 9/1/2014 to 9/2/2014 should be 1
3) 9/1/2014 to 9/3/2014 should be 2
4) 9/1/2014 to 9/4/2014 should be 3
5) 9/1/2014 to 9/5/2014 should be 4
Now...those are all working fine using my current formula.
However, the sticky wicket is the rows with NO data entered in the date columns. Once I copy my current formula into the rest of the column cells, the empty rows return a value of "1", which I need to be returned as BLANK, OR NIL, OR at the very least, "0". If it returns a "1", then my column auto sum will be incorrect.

I don't know what yor current formula is, but this one should be simpler and do what you are indicating you want above...

=IF(OR(A2="",B2=""),"",B2-A2+(B2=A2))

 

[copgal09]

MY current formula is...

=IF(G2-A2>2,DATEDIF(A2,G2,"d"),1)

Mine returns all the correct values EXCEPT when A2 AND G2 are empty.

I entered your formula, but it gave me a #VALUE! error. I suspect the empty [""] value is what created the error, since it is not a number(date). Am I right? I tried to change the cell format to accept the formula, but to no avail. What's next?

 

[Rick Rothstein]

I entered your formula, but it gave me a #VALUE! error.

That is because I forgot to adjust it from the columns I used for testing to your actual columns. Try this instead...

=IF(OR(A2="",G2=""),"",G2-A2+(G2=A2))

 

[copgal09]

That is because I forgot to adjust it from the columns I used for testing to your actual columns. Try this instead...

=IF(OR(A2="",G2=""),"",G2-A2+(G2=A2))

Yay!! You Got It!! (As Did Joe...!!) Both You And He Came From Different Directions, But Got The Right Outcome. Thanks!!!!

 

[copgal09]

Received the solution from JoeMo. Thank you, Sir! I'm rocking along, thanks to you and Rick Rothstein!

 

[JoeMo]

Received the solution from JoeMo. Thank you, Sir! I'm rocking along, thanks to you and Rick Rothstein!

You are welcome - thanks for the reply.