If and statement

[tykuhn99]

If and statement help

I have a SS with 2 columns (dates).
in some instances column 1 may be blank, while column 2 has a value entered, visa versa or both columns may be blank.

My goal is to find the number of days between the two dates in column 1 and 2

*if column 1 contains text and column 2 does not, i'd like to find the difference between todays date and column 1 date

*if neither column contains text id like column 3 to remain blank

I have entered the following formula, but am getting responses of 40,000 days, can anyone help me figure out where i've gone wrong?

=IF((AND(A2="*",B2="")),(TODAY()-A2),(B2-A2))

[JBeaucaire]

Small correction:

=IF((AND(A2<>"",B2="")),(TODAY()-A2),(B2-A2))



My version of the same logic:

=IF(A2="", "", MIN(B2, TODAY()) - A2)

[Marcol]

What happens when Column B has a date and Column A is blank?

Please Login or Register  to view this content.

[EDIT]
J.B.s' second formula is probably the correct one, just thought I'd check.

[tykuhn99]

thanks for the help guys, unfortunately the SS is on my computer that I left at work. can't wait to try it out on monday (sad i'm actually looking forward to going to work)

However, when column b has a date and column a does not, it returns a value of 40,000 something. in a perfect world it would return as blank.

[Marcol]

Okay that's what J.Bs' second formula does. ie. returns a blank.

And just for the record mine adjusted
=IF(AND(A1="",B1=""),"",IF(A1="","",IF(B1<>"",B1-A1,TODAY()-A1)))

Go with Jerry

[tykuhn99]

JB's second formula did the trick. Thanks again guys! Huge help!

[tykuhn99]

Hey guys, I'm back.

I went with the formula =IF(A2="", "", MIN(B2, TODAY()) - A2)

A request just came from above asking that the formula return nothing if Column B has text in it. My brain is freezing can someone please help?

[arthurbr]

Do you mean that B contains "text" ( a number is OK) or "is not empty" ?

[tykuhn99]

I'm sorry, both column a (start date) and b (end date) contain dates. my equation is going in column c, signifying days outstanding. If column b contains a date, it is no longer outstanding. By returning the days that it was outstanding I.E. Column B-Column A it is confusing people into thinking it still is outstanding, therefore we would like it to return blank or NA.

Make Sense?

[tykuhn99]

given a little more time I could figure this one out, but I'm under the gun right now, so I appreciate any help.

[arthurbr]

Try =IF(or(A2="",b2=""), "", MIN(B2, TODAY()) - A2)

[tykuhn99]

this still returns a value.

[arthurbr]

Care to post a small sample?

[JBeaucaire]

Maybe:

=IF(OR(A2="", ISTEXT(B2)), "", MIN(B2, TODAY()) - A2)

[JBeaucaire]

My goal is to find the number of days between the two dates in column 1 and 2

*if column 1 contains text and column 2 does not, i'd like to find the difference between todays date and column 1 date

All of these formulas are based on your premise above, from post #1. Is this premise in error? Are you saying now:

"If column B is empty and column A is not, I need the difference in days between today's date and the date in column A. If column B is not empty or if column A is empty, then return nothing."


If so, then:

=IF(OR(A2="", B2<>""), "", TODAY() - A2)

[tykuhn99]

Date of New Orders Date PA Received # of Days Outstanding
8/18/10 10/19/10 (Should return 0 since PA is on file)

12/9/10 (Blank) Should return 169 since PA not on f (Today-12/9/10)

[tykuhn99]

Sorry for all the confusion guys, I think I have finally come up with a solution:

"=IF(AND(A1>0,B1>0),"",IF(AND(A1=0,B1=0),"",(TODAY()-A1)))"

I'm sure there is a much more concise way to come up with this, but I am only a beginner. Thanks again for all the help and sorry if I added to the confusion. (I too was a little confused as to what I wanted at times).