Help with Multi-Condition Formula

[Guest]

I am trying to write a conditional formula that calculates the number of days
outstanding, as described below. Here are the pertinent column designations:

Column P = Priority level (1 or 2)
Column R = Date Assigned
Column U = Date Closed
Column V = Status (Open or Closed)
Column S = Days Outstanding

If Column P is 1, and today is more than 30 days past Column R, and Column U
is more than 30 days past Column R, calculate Column S, BUT

If Column P is 2, and today is more than 45 days past Column R, and Column U
is more than 45 days past Column R, calculate Column S
..
So far, this is what I’ve come up with:

=IF(ISNONTEXT(U12),IF(AND(TODAY()>=(R12+30),P12=1),TODAY()-R12,IF(AND(TODAY()>=(R12+45),P12=2),TODAY()-R12)))

Thanks to anyone who can help. I don’t even know if what I’m trying to
accomplish is possible.

--VB

 

[vezerid]

VB,

I cannot understand from your formula how you calculate S (is it maybe
TODAY()-R12?). Anyway, leaving space for this in _your calculation_,
the following formula implements the conditions you describe:

=IF(OR(AND(P12=1,TODAY()-R12>30,U12-R12>30),AND(P12=2,TODAY()-
R12>45,U12-R12>45)),_your calculation_,"")

HTH
Kostis Vezerides

 

[Guest]

Yes. S is calculated as TODAY()-R12, based on the conditions stated. I
copied and pasted your suggestion, replacing "_your calculation_" with
"TODAY()-R12", and the result is an empty cell. Using the data below, cell
S1 should be 272, and cell S2 should be blank, because Column U is not more
than 30 days from Column R.

Row 1: P = 2; R = 6-1-2006; U = _empty cell_; V = Open; S = 272
Row 2: P = 1; R = 6-1-2006; U = 6-30-06; V = Closed; S = _empty cell_

I hope this helps. I'm trying to be as clear as I can.