 |
 | 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 2008Compare dates with lag
I need a formula to check for status=”Closed” and delivery within 2 days of requested date. A B C Status Requested Date Delivered Date ===== ============ =========== Closed 10/16/07 13:31 12/5/07 15:17 Submitted 10/16/07 13:31 12/5/07 15:17 Submitted 10/16/07 13:31 12/5/07 15:17 Closed 10/16/07 16:11 12/18/07 16:29 Submitted 10/19/07 9:58 10/19/07 12:02 Submitted 10/19/07 9:58 10/19/07 12:08 Closed 10/19/07 9:58 10/19/07 12:08 Submitted 10/19/07 9:58 10/19/07 12:08 Closed 10/19/07 10:44 10/31/07 17:06 Submitted 10/19/07 10:44 10/31/07 17:06 Thank you. =AND(A2="Closed",C2-B2<=2)  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I need a formula to check for status="Closed" and delivery within 2 days of > requested date. [quoted text clipped - 15 lines] > > Thank you. Can you please suggest a sumproduct format? I need to count how many items in the array satisfy this condition. When I attempt sumproduct I get a #VALUE error. BTW, some dates may not be filled in, so I need to check valid date as well. Thank you. > =AND(A2="Closed",C2-B2<=2) > [quoted text clipped - 17 lines] > > > > Thank you. =SUMPRODUCT(--(A2:A20="Closed"),--(C2:C20-B2:B20<=2)) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Can you please suggest a sumproduct format? > [quoted text clipped - 27 lines] >> > >> > Thank you. One more Question: Since this one works fine: RawData!$AF$1:$AF$10000>RawData!$AD$1:$AD$10000 Why does this return a #VALUE error? RawData!$AF$1:$AF$10000-1>RawData!$AD$1:$AD$10000 Thank you. > =SUMPRODUCT(--(A2:A20="Closed"),--(C2:C20-B2:B20<=2)) (RawData!$AF$1:$AF$10-1)>RawData!$AD$1:$AD$10 Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > One more Question: > [quoted text clipped - 7 lines] > >> =SUMPRODUCT(--(A2:A20="Closed"),--(C2:C20-B2:B20<=2)) |
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.