Jump to content
Macro Express Forums

Working With Dates From Excel


suns11111
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...