|
Compare 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. |
Compare dates with lag
=AND(A2="Closed",C2-B2<=2)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "adimar" wrote in message ... 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. |
Compare dates with lag
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. "Bob Phillips" wrote: =AND(A2="Closed",C2-B2<=2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "adimar" wrote in message ... 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. |
Compare dates with lag
=SUMPRODUCT(--(A2:A20="Closed"),--(C2:C20-B2:B20<=2))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "adimar" wrote in message ... 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. "Bob Phillips" wrote: =AND(A2="Closed",C2-B2<=2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "adimar" wrote in message ... 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. |
Compare dates with lag
One more Question: Since this one works fine: RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000 Why does this return a #VALUE error? RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000 Thank you. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20="Closed"),--(C2:C20-B2:B20<=2)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) |
Compare dates with lag
(RawData!$AF$1:$AF$10-1)RawData!$AD$1:$AD$10
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "adimar" wrote in message ... One more Question: Since this one works fine: RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000 Why does this return a #VALUE error? RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000 Thank you. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20="Closed"),--(C2:C20-B2:B20<=2)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) |
| All times are GMT +1. The time now is 11:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com