 |
 | 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 / October 2007Comparing two columns of dates
I'm working in Excel 2007. I have two columns of dates, the first being a "buy" date and the second being a "sell" date for the item in that row. If the data is valid, obviously each date in the second column should be later than the corresponding date in the first column. I'd like to create a function that will compare each date in the first column to the adjacent date in the second column, and tell me if the data is valid or if there is a date-pair in which the "buy" date falls after the "sell" date. I don't need this comparison returned for each pair, I just need to check the columns as a whole and get a "VALID" OR "INVALID" value. I'm thinking that this needs to be some sort of composite function, maybe involving a COUNTIF(), but I just can't figure out how to work it. Thanks for the help! A couple of questions... Can the sell date be the same as the buy date? If you haven't sold yet then there probably isn't a sell date entered yet, correct?  SignatureBiff Microsoft Excel MVP > I'm working in Excel 2007. > [quoted text clipped - 17 lines] > > Thanks for the help! No, the sell date can never be the same as the buy date - it should always be at least a day later. And every pair is full - there are no missing dates. I.E. every item in this list has been sold. > A couple of questions... > [quoted text clipped - 24 lines] > > > > Thanks for the help! Try this: =IF(SUMPRODUCT(--(A1:A10<B1:B10))=COUNT(A1:A10),"Valid","Invalid") Note that you can't use entire columns as range references with SUMPRODUCT unless you're using Excel 2007. SignatureBiff Microsoft Excel MVP > No, the sell date can never be the same as the buy date - it should always > be [quoted text clipped - 39 lines] >> > >> > Thanks for the help! Works like a charm - now I get to play around with it and try and figure out how it works. Thanks! > Try this: > [quoted text clipped - 46 lines] > >> > > >> > Thanks for the help! You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > Works like a charm - now I get to play around with it and try and figure > out [quoted text clipped - 57 lines] >> >> > >> >> > Thanks for the help! |
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.