suns11111 Posted May 31, 2007 Report Share Posted May 31, 2007 I am somewhat familar with Macro Express, but never worked with comparing dates (dd/mm/yy). I have an MS Excel spreadsheet and the macro copies 5 cells (in a row) and <TAB> and gives me the larger number, but does not work with dates it appears. It shows 11/1/05 is greater than 1/1/06.....which is not good. Any ideas to get the greatest (newest) date? Many thanks! Jeff S Quote Link to comment Share on other sites More sharing options...
johnboy691 Posted May 31, 2007 Report Share Posted May 31, 2007 Jeff I did this a while back. Not sure how you are able to manipulate or time consuming it is to work with your excel file. So you'll have to determine if this would work for you. In Excel, each date is assigned a value by excel. I changed the date format by going into Format, Cell, Number tab and then number with no decimal places and it switches the date format to a number. If your macro works on regular numbers it should then work on the date number also. Hope this helps. If someone else has a different idea, I'd like to hear that too. John Quote Link to comment Share on other sites More sharing options...
Cory Posted June 1, 2007 Report Share Posted June 1, 2007 Why don't you just use the MAX function in Excel? E.g. =MAX(B1:B5). Your problem is that when copied to the clipboard it's coping text values hence why in your example it seems backwards. But to the computer it's exactly correct because it's sorting it alphabetically. One solution would be to parse the dates into year, month and day. Then compare them as integers. However this is a much more complex algorithm but it would be fun to write I think. Another solution would be to reformat your dates in Excel to be in the format 03/14/2006. That is 4 digit years and padded month and day. This will sort alphabetically. Yet another consideration is to change the format to integer. You see Excel actually keeps dates as integers counting up from 1/1/1900. E.g. Excel thinks today is 39233! If you have ME change the format (CTRL+1) to get these integer values then you could switch it back when done. Yet another alternative would be to convert the date to integer in ME and compare that. Now that would be a fun project too. Quote Link to comment Share on other sites More sharing options...
suns11111 Posted June 4, 2007 Author Report Share Posted June 4, 2007 Thank you for your replys. I should have mentioned that I have tried to format the information in Excel. The dates are from a old legacy system which Macro Express easily copies and pastes on a Excel spreadsheet, but no matter what i try it will not change formats. Tried Copy and PASTE SPECIAL (Values) and still must see it as text. Meaning I can not change from 05/05/05 to 05/05/2005 or even 2005/05/05. My search will continue! I will keep you posted! Thanks JeffS Quote Link to comment Share on other sites More sharing options...
Cory Posted June 4, 2007 Report Share Posted June 4, 2007 Check your formatting. Make sureit's "General" or a date format and not Text. If it's Text all kinds of things won't work. Often CSV files and such will att a ingle quote mark (') to the beginning of the cell data to slave Excel to make it text. It doens't display unless you actually look at the raw data or the formula. But any version of Excel should be able to reformat if the cells format is not text or has this single quote. I am confident your problem can be solved in Excel. If you attach a sample file I could look at it. But my other suggestions would still work. The ones that do the comparisons in ME. Would be fairly easy logic to implement. Quote Link to comment Share on other sites More sharing options...
suns11111 Posted June 11, 2007 Author Report Share Posted June 11, 2007 Thanks for your reply. I will keep working with Excel to figure out the problem. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.