| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| compare dates using a variable | Excel Worksheet Functions | |||
| compare dates | Excel Worksheet Functions | |||
| Compare two dates and if they are equal | Excel Discussion (Misc queries) | |||
| how do I compare two dates? | Excel Worksheet Functions | |||
| Compare range of dates and sum... | Excel Discussion (Misc queries) | |||
Linear Mode
