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

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



??? wrote:

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

Thanks Dave!

"Dave Peterson" wrote:

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



??? wrote:

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

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.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote:

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



??? wrote:

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

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.

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

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



??? wrote:

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


--

Dave Peterson