 |
 | 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 2008Date comparison
For the data below B1>A1 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$10000>1)) 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. 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 > For the data below B1>A1 is true. > [quoted text clipped - 13 lines] > > Thank you. 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$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. > 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 [quoted text clipped - 25 lines] > > > > Thank you. What is the whole formula? Tyro > Thank you for the explanation. Yes, this makes sense and works for a quick > test I did. [quoted text clipped - 45 lines] >> > >> > Thank you. 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)*(RawData!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$AF$1:$AF$10000>RawData!$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)*(RawData!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$AF$1:$AF$10000-1>RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6,0))))) Thank you. > What is the whole formula? > [quoted text clipped - 49 lines] > >> > > >> > Thank you. 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 > 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)*(RawData!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$AF$1:$AF$10000>RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6,0))))) [quoted text clipped - 60 lines] >> >> > >> >> > Thank you. =SUMPRODUCT(--($AF$1:$AF$10000<>"") should be =SUMPRODUCT(--($AF$1:$AF$10000<>"")) or even =COUNTIF($AF$1:$AF$10000,"<>"&"") Tyro > 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 [quoted text clipped - 73 lines] >>> >> > >>> >> > Thank you. Yes, that's correct - I have some 4600 records in RawData (equal to number of ADs) and some 3000 non-blank AFs. How then can I change the formula to allow for blank date fields in AF. ADs should always be non-blank but if there's a way to check for that, that would be great. Thank you. > =SUMPRODUCT(--($AF$1:$AF$10000<>"") should be > =SUMPRODUCT(--($AF$1:$AF$10000<>"")) or even [quoted text clipped - 79 lines] > >>> >> > > >>> >> > Thank you. Your problem is elsewhere. If a cell is blank and you subtract 1 from it, you get -1. If a cell contains text, and you subtract -1 from it you get #VALUE Do you have some cells containing text in your range? Tyro > Yes, that's correct - I have some 4600 records in RawData (equal to number > of ADs) and some 3000 non-blank AFs. [quoted text clipped - 102 lines] >> >>> >> > >> >>> >> > Thank you. Or if you have a formula that returns the empty string, which looks like a blank, and you subtract 1 from that, you'll also get #VALUE Tyro > Your problem is elsewhere. If a cell is blank and you subtract 1 from it, > you get -1. If a cell contains text, and you subtract -1 from it you get [quoted text clipped - 112 lines] >>> >>> >> > >>> >>> >> > Thank you. The columns I'm comparing (AD and AF) are dates generated by an Oracle report. I assume they are "", when not a regular/valid date. I have these types of situations: AD AF 9/30/2007 12/21/2007 10/18/2007 That is, AF could be "" but all ADs are valid dates. I need to count how many records have AF>AD, when AF<>"". Thank you. ================ > Or if you have a formula that returns the empty string, which looks like a > blank, and you subtract 1 from that, you'll also get #VALUE [quoted text clipped - 117 lines] > >>> >>> >> > > >>> >>> >> > Thank you. Assuming your dates occupy the first 100 rows, cells in AF1:AF100 that are truly empty will not be greater than a date. However, cells with the empty string will be greater than a date. So use this formula =SUMPRODUCT(--(AF1:AF100>AD1:AD100))-COUNTIF(AF1:AF100,"="&""). Tyro > The columns I'm comparing (AD and AF) are dates generated by an Oracle > report. [quoted text clipped - 146 lines] >> >>> >>> >> > >> >>> >>> >> > Thank you. (For some reason my lasy post didn’t post… let me try again.) This only works if all ADs are valid dates. How do I specify a dynamic range, so instead of the 10000 max records in this formula ($AF$1:$AF$1000>$AD$1:$AD$1000) I use something like indirect for the range. I tried several formulas and cannot et over syntax errors. Thank you. ========== > Assuming your dates occupy the first 100 rows, cells in AF1:AF100 that are > truly empty will not be greater than a date. However, cells with the empty [quoted text clipped - 154 lines] > >> >>> >>> >> > > >> >>> >>> >> > Thank you. I just learned how to use the evaluate formula tool and solved this one. Thank you for your help. > Assuming your dates occupy the first 100 rows, cells in AF1:AF100 that are > truly empty will not be greater than a date. However, cells with the empty [quoted text clipped - 154 lines] > >> >>> >>> >> > > >> >>> >>> >> > Thank you. This one seems to work: ((NOT(ISERROR((RawData!$AF$1:$AF$10000-1>RawData!$AD$1:$AD$10000))) Is this correct? > Or if you have a formula that returns the empty string, which looks like a > blank, and you subtract 1 from that, you'll also get #VALUE [quoted text clipped - 117 lines] > >>> >>> >> > > >>> >>> >> > Thank you. |
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.