Date problem

[Guest]

Hi

Can anyone tell me please why the results of the following code:

Dim dte As Date
dte = Now()
ActiveCell.Value = Format(dte, "Short Date")
End Sub

Is 07/05/2007 (May 7, 2007) instead of 05/07/2007???

Thanks in advance

Eli

 

[Dave Peterson]

If you type a date into a cell, excel will parse it and display it according to
its rules. And your code is essentially just typing into that cell.

You want to control the format of the cell--not the format of what you're typing
(via code).

dim dte as date
dte = date
with activecell
.numberformat = "mm/dd/yyyy" 'or whatever you want
.value = dte
end with

 

[Guest]

Thanks Dave!

If you type a date into a cell, excel will parse it and display it according to
its rules. And your code is essentially just typing into that cell.

You want to control the format of the cell--not the format of what you're typing
(via code).

dim dte as date
dte = date
with activecell
.numberformat = "mm/dd/yyyy" 'or whatever you want
.value = dte
end with

 

[Guest]

to add to Dave's explanation,

when you pass a date STRING from VB to Excel, it will be interpreted using
US date format (mm/dd/yyyy) if that would be a valid date.

As Dave said, pass it as a dateserial and it won't be misinterpreted.

 

[Dave Peterson]

Thanks for the addition.

I would have guessed that it picked it up from regional settings regional short
date format--but a minor test proved that you were right <vbg>.