An either/or function

[HeatherBelle]

I want the following to happen:
If O5 is blank, then P5 will be also (this much is done in the formula below)
If O5 has a date, then the below function will be done (also done)
If Q5 gets populated, then the function will stop (also done)
However, I want to now add something that will perform the same
function/situations in P5 if <b>R5</b> has a date (O5 and R5 will NOT both
have dates in them)

=IF(O5="","",IF(Q5="",DAYS360(O5,TODAY()),DAYS360(O5,Q5)))

 

[Sean Timmons]

Sounds like you want:

=IF(AND(O5="",R5=""),"",IF(R5="",IF(Q5="",DAYS360(O5,TODAY()),DAYS360(O5,Q5)),IF(Q5="",DAYS360(R5,TODAY()),DAYS360(R5,Q5)))

This will first verify if both your criteria cells are blank. If so, "",
else..
If R5 is blank (Thus, O5 is not), do the O5 IF. Otherwise, R5 must not be
blank, and per your statement, O5 is. So, do the same calculation, but with
R5 instead of o5.

 

[John C]

If I understand you correctly, you want your new formula to do the same thing
for the other 2 cells. But now you are wanting a limitatino that only 1 date
can be had, either O5 or R5. Well, assuming O5 and R5 would be entered by the
user, then your formula would be exactly the same, but you would want to use
Data Validation on O5 and R5.
O5 data validation (Data-->Validation), choose custom, and then in the
formula box type =$R$5=""
for R5 type =$O$5=""
If you try to work these checks into each formula, you will run into
circular references. If this isn't the solution to your question, post back
and let us know what your expectations based on the data (and how the data is
entered).

 

[HeatherBelle]

Here is a sample from my workbook: (Yes, my columns have changed, I've added
another column since I posted earlier)

P Q R S
Date to AOV Days Out Effective Date Denial Date
02/15/08 ???? 03/05/08
1/31/07 ???? 3/01/07

I don't want anything to show in Q if P is blank (otherwise, I get a really
large number sitting there because there's no data for it to compute)
I want the count to keep rolling until a date is put into either R OR S
(there will never be one in both)

 

[John C]

=IF(P5="","",IF(AND(R5="",S5=""),DAYS360(P5,TODAY()),IF(AND(R5<>"",S5<>""),"Error",DAYS360(P5,IF(R5="",S5,R5)))))

I think this is what you want. Hope it helps

 

[HeatherBelle]

Perfect! Thanks!!

=IF(P5="","",IF(AND(R5="",S5=""),DAYS360(P5,TODAY()),IF(AND(R5<>"",S5<>""),"Error",DAYS360(P5,IF(R5="",S5,R5)))))

I think this is what you want. Hope it helps

 

[John C]

You are welcome, and thanks for the feedback.