VBA date errors

[boofhead]

Can I add my experience in the hope that Microsoft may be tempted to fix
their error generation feature.
My workaround may also be of some use to others.
I use English Regional settings and imported a file to a spreadsheet
using DMY date settings. With VBA I set a string variable s to the
contents of a cell which contained 10/06/2005. When (without further
processing) s was stored in a new location, that cell showed 6/10/2005 -
i.e. it had been converted to US format.
I attempted to force the correct format by interposing
s = FormatDateTime(s,2) but this had no effect. However, changing this
to s= FormatDateTime(s,1) gave Friday, 10 June 2005, which is cumbersome
- but at least it is correct.

boofhead


*** Sent via Developersdex http://www.developersdex.com ***

[Jezebel]

What do you mean "imported a file to a spreadsheet using DMY date settings"
? How did you use the date setting in the file import?




"boofhead" <[email protected]> wrote in message
news:Oqcev3G%[email protected]...
>
> Can I add my experience in the hope that Microsoft may be tempted to fix
> their error generation feature.
> My workaround may also be of some use to others.
> I use English Regional settings and imported a file to a spreadsheet
> using DMY date settings. With VBA I set a string variable s to the
> contents of a cell which contained 10/06/2005. When (without further
> processing) s was stored in a new location, that cell showed 6/10/2005 -
> i.e. it had been converted to US format.
> I attempted to force the correct format by interposing
> s = FormatDateTime(s,2) but this had no effect. However, changing this
> to s= FormatDateTime(s,1) gave Friday, 10 June 2005, which is cumbersome
> - but at least it is correct.
>
> boofhead
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

[Ron Rosenfeld]

On Sat, 03 Dec 2005 16:59:08 -0800, boofhead <[email protected]> wrote:

>
>Can I add my experience in the hope that Microsoft may be tempted to fix
>their error generation feature.
>My workaround may also be of some use to others.
>I use English Regional settings and imported a file to a spreadsheet
>using DMY date settings. With VBA I set a string variable s to the
>contents of a cell which contained 10/06/2005. When (without further
>processing) s was stored in a new location, that cell showed 6/10/2005 -
>i.e. it had been converted to US format.
>I attempted to force the correct format by interposing
>s = FormatDateTime(s,2) but this had no effect. However, changing this
>to s= FormatDateTime(s,1) gave Friday, 10 June 2005, which is cumbersome
>- but at least it is correct.
>
>boofhead
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

I'm not certain exactly what you are doing since you've not posted any code.

However, depending on how you "set" a string variable to the contents of a cell
that contains a date, the behavior you describe is not unexpected. VBA is
somewhat US-centric.

Assuming the date is stored properly as an Excel date (e.g. a serial number),
then something like:

dim s as string
s = format(cell,"dd/mm/yy")

should do what you describe.


--ron

[boofhead]

Many thanks Ron. Your suggstion works.
For the record, I did not quote the code because it was so simple. eg
set r = range("A200")
s = r
set r = range ("V2")
r=s
Cell A200 contained the date as described.
The reason for "copying" this and other data in this manner was simply
for presentation purposes, to get the data in the same region as an
embedded chart, to show the "big picture" from quite detailed
calculations on a single screen.
I think this example shows that in some cases Excel/VBA can be too
clever by half - as we english speaking people would say!

Regards

boofhead


*** Sent via Developersdex http://www.developersdex.com ***

[boofhead]

Hi Jezebel
If you select data - import text file - etc, when Excel sees a data set
that is unusual - ie looks like date info, it asks you to specify the
format you want from a drop list. This includes DMY and several other
formats.

Regards

boofhead


*** Sent via Developersdex http://www.developersdex.com ***

[Ron Rosenfeld]

On Sat, 03 Dec 2005 23:28:07 -0800, boofhead <[email protected]> wrote:

>
>Many thanks Ron. Your suggstion works.

Glad to hear that.

It seems to me that where you require an explicit "machine independent" format,
then the VBA Format function should give you better control. The
FormatDateTime function, which I have never used, myself, should be "machine
dependent", and dependent on the settings in the Windows Control Panel --
something more difficult for the writer to control.


>For the record, I did not quote the code because it was so simple. eg
>set r = range("A200")
>s = r
>set r = range ("V2")
>r=s
>Cell A200 contained the date as described.

Yes but it gives more precise information. I have frequently found that what
posters assume me to understand is not the same as what I have inferred.

>I think this example shows that in some cases Excel/VBA can be too
>clever by half - as we english speaking people would say!


--ron

[SmartbizAustralia]

I always make dates appear in dd-mmm-yyyy format or dd-mmm-yy
This solves most date translation issues.

You'll never be confused and excel will also never be confused with
12-Dec-2005