Date Format

[gabch]

Dear all,

Win local format is "dd/mm/yy"

in VBA

Cells(1, 1) = "08/02/2006"

in Excel sheet display

"02/08/06" in A1

What happen?

[Norman Jones]

Hi Gabch,

Try:

Cells(1, 1).Value = DateValue("08/02/2006")


---
Regards,
Norman



"gabch" <[email protected]> wrote in
message news:[email protected]...
>
> Dear all,
>
> Win local format is "dd/mm/yy"
>
> in VBA
>
> Cells(1, 1) = "08/02/2006"
>
> in Excel sheet display
>
> "02/08/06" in A1
>
> What happen?
>
>
> --
> gabch
> ------------------------------------------------------------------------
> gabch's Profile:
> http://www.excelforum.com/member.php...o&userid=30662
> View this thread: http://www.excelforum.com/showthread...hreadid=536337
>

[Mat P:son]

The Windows Regional Settings dictates, among other things, how Excel should
present dates.

However, the VBA code is primarily using US format for dates. Hence,
Cells(1, 1) = "08/02/2006" means 02-Aug-2006 (US mm/dd/yyy), and it then gets
translated into "02/08/2006" (which means 02-Aug-2006 in your UK format)
during the presentation stage, i.e., when Excel displays the date in cell A1.

By the way, you can also use the following construct in the future:

Cells(1, 1) = #2/8/2006#

The #..# literal is explicitly of type Date (and, yes, it's in US format) so
you know what you'll get. A string, however, can basically be anything you
like...

Cheers,
/MP

"gabch" wrote:

>
> Dear all,
>
> Win local format is "dd/mm/yy"
>
> in VBA
>
> Cells(1, 1) = "08/02/2006"
>
> in Excel sheet display
>
> "02/08/06" in A1
>
> What happen?
>
>
> --
> gabch
> ------------------------------------------------------------------------
> gabch's Profile: http://www.excelforum.com/member.php...o&userid=30662
> View this thread: http://www.excelforum.com/showthread...hreadid=536337
>
>

[gabch]

Hi,

How can I convert UK date format(dd/mm/yy) at cell to US format (mm/dd/yy) through VBA