| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
For the data below B1A1 is true. A B 12/3/07 11:48 AM 12/3/07 1:26 PM How do I check the dates are at least 1 day apart, or 24 hours apart? I am looking for a check to inlcude in a sumproduct, using external sheet reference, like: SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001)) where C and D contain Scheduled date and Shipped date. Also I am trying to avoid reformatting the source data or adding suplementary, intermediate columns. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date comparison
You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least 1
day apart","Dates are the same day"). This formula assumes there are valid dates in A1 and B1 as it does no checking. Excel maintains dates and times as numbers. The integral part of the number is the day, the fractional part the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1, 1900 is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00 AM, midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To see the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press Ctrl+Accent Grave to return to normal display. Tyro "adimar" wrote in message ... For the data below B1A1 is true. A B 12/3/07 11:48 AM 12/3/07 1:26 PM How do I check the dates are at least 1 day apart, or 24 hours apart? I am looking for a check to inlcude in a sumproduct, using external sheet reference, like: SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001)) where C and D contain Scheduled date and Shipped date. Also I am trying to avoid reformatting the source data or adding suplementary, intermediate columns. Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date comparison
Thank you for the explanation. Yes, this makes sense and works for a quick test I did. But I have one example that I don't know how to fix. 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. "Tyro" wrote: You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least 1 day apart","Dates are the same day"). This formula assumes there are valid dates in A1 and B1 as it does no checking. Excel maintains dates and times as numbers. The integral part of the number is the day, the fractional part the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1, 1900 is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00 AM, midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To see the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press Ctrl+Accent Grave to return to normal display. Tyro "adimar" wrote in message ... For the data below B1A1 is true. A B 12/3/07 11:48 AM 12/3/07 1:26 PM How do I check the dates are at least 1 day apart, or 24 hours apart? I am looking for a check to inlcude in a sumproduct, using external sheet reference, like: SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001)) where C and D contain Scheduled date and Shipped date. Also I am trying to avoid reformatting the source data or adding suplementary, intermediate columns. Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date comparison
What is the whole formula?
Tyro "adimar" wrote in message ... Thank you for the explanation. Yes, this makes sense and works for a quick test I did. But I have one example that I don't know how to fix. 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. "Tyro" wrote: You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least 1 day apart","Dates are the same day"). This formula assumes there are valid dates in A1 and B1 as it does no checking. Excel maintains dates and times as numbers. The integral part of the number is the day, the fractional part the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1, 1900 is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00 AM, midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To see the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press Ctrl+Accent Grave to return to normal display. Tyro "adimar" wrote in message ... For the data below B1A1 is true. A B 12/3/07 11:48 AM 12/3/07 1:26 PM How do I check the dates are at least 1 day apart, or 24 hours apart? I am looking for a check to inlcude in a sumproduct, using external sheet reference, like: SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001)) where C and D contain Scheduled date and Shipped date. Also I am trying to avoid reformatting the source data or adding suplementary, intermediate columns. Thank you. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date comparison
This one works: =SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0))))) This one returns #VALUE error, see next to last multiplier: =SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0))))) Thank you. "Tyro" wrote: What is the whole formula? Tyro "adimar" wrote in message ... Thank you for the explanation. Yes, this makes sense and works for a quick test I did. But I have one example that I don't know how to fix. 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. "Tyro" wrote: You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least 1 day apart","Dates are the same day"). This formula assumes there are valid dates in A1 and B1 as it does no checking. Excel maintains dates and times as numbers. The integral part of the number is the day, the fractional part the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1, 1900 is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00 AM, midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To see the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press Ctrl+Accent Grave to return to normal display. Tyro "adimar" wrote in message ... For the data below B1A1 is true. A B 12/3/07 11:48 AM 12/3/07 1:26 PM How do I check the dates are at least 1 day apart, or 24 hours apart? I am looking for a check to inlcude in a sumproduct, using external sheet reference, like: SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001)) where C and D contain Scheduled date and Shipped date. Also I am trying to avoid reformatting the source data or adding suplementary, intermediate columns. Thank you. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date comparison
Looks like in $AF$1:$AF$10000 you have non-numerics so that when the "-1" is
attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000) to see how many numeric cells there are and do a =SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells there are. If you have only numbers in that range, both functions should produce the same number. If they don't you have some non-numerics in your range. Tyro "adimar" wrote in message ... This one works: =SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0))))) This one returns #VALUE error, see next to last multiplier: =SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0))))) Thank you. "Tyro" wrote: What is the whole formula? Tyro "adimar" wrote in message ... Thank you for the explanation. Yes, this makes sense and works for a quick test I did. But I have one example that I don't know how to fix. 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. "Tyro" wrote: You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least 1 day apart","Dates are the same day"). This formula assumes there are valid dates in A1 and B1 as it does no checking. Excel maintains dates and times as numbers. The integral part of the number is the day, the fractional part the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1, 1900 is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00 AM, midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To see the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press Ctrl+Accent Grave to return to normal display. Tyro "adimar" wrote in message ... For the data below B1A1 is true. A B 12/3/07 11:48 AM 12/3/07 1:26 PM How do I check the dates are at least 1 day apart, or 24 hours apart? I am looking for a check to inlcude in a sumproduct, using external sheet reference, like: SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001)) where C and D contain Scheduled date and Shipped date. Also I am trying to avoid reformatting the source data or adding suplementary, intermediate columns. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help With Formula For Date Comparison | Excel Discussion (Misc queries) | |||
| Date comparison | Excel Discussion (Misc queries) | |||
| date comparison help | Excel Discussion (Misc queries) | |||
| Date comparison | Excel Worksheet Functions | |||
| Date comparison | Excel Discussion (Misc queries) | |||
Hybrid Mode
