Sleeper: Macro's and Dates

[Whitney2002]

I am running Windows XP and Office 2003. Excell 2003. I am also using XLQ to retrieve the data from Yahoo. I am trying to run a macro that I have had for several months. I have not ran it since I changed from Office XP to Office 2003. My friend has no problem with running the macro. His start date is 03/01/2005. I can not get my date to read like that. The closest I can get get is 03/01/05 or 3/1/05. I am unsure that the date format would make any difference.

We are running a macro to determine the RSI of a number of Closedend Funds. When I run the macro all of the RSI numbers end up to be 100.

Any help would be appreciated. Whitney

[MWE]

I am running Windows XP and Office 2003. Excell 2003. I am also using XLQ to retrieve the data from Yahoo. I am trying to run a macro that I have had for several months. I have not ran it since I changed from Office XP to Office 2003. My friend has no problem with running the macro. His start date is 03/01/2005. I can not get my date to read like that. The closest I can get get is 03/01/05 or 3/1/05. I am unsure that the date format would make any difference.

We are running a macro to determine the RSI of a number of Closedend Funds. When I run the macro all of the RSI numbers end up to be 100.

Any help would be appreciated. Whitney

Re the date problem: can you clarify the circumstances when the date is not what you want? Is the problem a display using, say, MsgBox? The values written to a worksheet cell? Something else?

Normally date information is stored as a Date variable. The display format will depend on how you specify things; Excel often times defaults to how you told the system to display dates, unless you tell Excel something else. Dates written to a worksheet are often displayed based on how the cell is formatted.

[Whitney2002]

The date that is in the macro is 03/06/2005. When I got to the format cell tab, there is no like format for me to choose from. I have 3-6-05, or 3/6/05, or others like that.

Irrespectively, I do not think that the format of the date should matter. As long as Yahoo recognizes the date, why should the format of the date matter. Am I wrong? Or is there some other reason why I can't get the macro to run correctly? Whitney

[sheeeng]

Welcome to VBAX, Whitney2002.

The date that is in the macro is 03/06/2005. When I got to the format cell tab, there is no like format for me to choose from. I have 3-6-05, or 3/6/05, or others like that.

Irrespectively, I do not think that the format of the date should matter. As long as Yahoo recognizes the date, why should the format of the date matter. Am I wrong? Or is there some other reason why I can't get the macro to run correctly? Whitney

Could you specify more in detail the dates you have? What is the format you need?

If cannot find default format that suit you, try
DatePart Function
http://msdn.microsoft.com/library/de...ctdatepart.asp


Dim the date and format you need in a string.

[MWE]

The date that is in the macro is 03/06/2005. When I got to the format cell tab, there is no like format for me to choose from. I have 3-6-05, or 3/6/05, or others like that.

you have rather more control over the date format via the format cell tab than you might think. If you select date from the menu of categories, you will see formats like "3/14", "3/14/98", "14-Mar-09", etc. What you need to remeber is that you can either accept a standard date format or, via the Custom category, you can create anything you want. For example, assuming that 03/06/2005 is June 3, 2005 you can specify dd/mm/yyyy as the format you want and get 22/06/2005 for today's date. If 03/06/2005 means March 6, 2005, then you should specify mm/dd/yyyy and today's date would be 06/22/2005. If you specified, say, dd-mmm-yyyy today's date would be 22-Jun-2005. This latter format is generally unambiguous; unambiguous date formats can be important.

Irrespectively, I do not think that the format of the date should matter. As long as Yahoo recognizes the date, why should the format of the date matter. Am I wrong? Or is there some other reason why I can't get the macro to run correctly? Whitney

If the date is passed around as a true date variable, then the format is not important (except to a person attempting to read the date). If the date is just "text", then the format can be very important because the provider and receiver must agree on the format or problems will occur. Let's consider 03/06/2005. If one side assumes "US convention", then 03/06/2005 (as text) means 06-Mar-2005. But if the other side assumes "Intl conventions or US military or just about anything other than US", then 03/06/2005 means 03-Jun-2005. Sometimes a person can figure out what the format is, e.g., 03/22/2005 must mean 22-Mar-2005 as there is no 22nd month.

[johnske]

....... "Intl conventions or US military or just about anything other than US", then 03/06/2005 means 03-Jun-2005. ......

Hi Mark,

I found the above bit interesting. So the US military DOES use international convention for dates but civilians don't? (which makes a lot of sense if you're planning to launch a joint operation with another country on a certain date - you don't want them attacking 3 months late (or early) - but what about a corporate raid? )

As you mentioned, such confusion can be avoided by always using mmm format rather than dd mm, or, mm dd

Regards,
John

[Bob Phillips]

As you mentioned, such confusion can be avoided by always using mmm format rather than dd mm, or, mm dd

Or use the ISO standard yyyy-mm-dd

[Bob Phillips]

If the date is passed around as a true date variable, then the format is not important

What is a true date variable? Excel's certainly isn't, but then whose is? A database timestamp is not the same as a Unix date/time, and so on. You still need a defined protocol to interop.