 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2007Help with Multi-Condition Formula
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 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 > I am trying to write a conditional formula that calculates the number of days > outstanding, as described below. Here are the pertinent column designations: [quoted text clipped - 19 lines] > > --VB 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.  SignatureVB > VB, > [quoted text clipped - 31 lines] > > > > --VB |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.