Thread: How to give the numberformat the systemdefault date format?

Hi All,

How can I format some cells use the system default date format in Excel ?
So that when the regional settings in Windows changes, the date format will
be also changed accordingly.

Although without numberFormat does excel function automatically, however
here we have an array, I have to assign the numberFormat because of the
other cells.

A code expamle is listed as following: Thanks in advance.

Zuxiang DAI


sub doIt()

Dim aValues(0 To 1) As Variant
Dim aFormats(0 To 1) As String

aValues(0) = "000100.123456000"
aFormats(0) = "@"


aValues(1) = #2/10/2006#

'how can I tell excel this should be a system default date format?
aFormats(1) = ????????

Application.ErrorCheckingOptions.NumberAsText = False

Range("A1", "B1").numberFormat = aFormats
Range("A1", "B1").Value = aValues

End Sub

My guess is that you are overcomplicating something that would take
care of itself if you used.
aValues(1) = DateSerial(2000,2,10)

are you saying that #2/10/2000# as a date constant in VBA is not
US centric. Anyway the use of Date function is unambibuous and
should result in your own regional formatting if the formatting is General
to begin with.. As you know Excel will change the defaulted format
when the Regional Settings change.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"ajd" <someone@mi.com> wrote in message news:OcgM98iLGHA.3272@tk2msftngp13.phx.gbl...
> Hi All,
>
> How can I format some cells use the system default date format in Excel ?
> So that when the regional settings in Windows changes, the date format will
> be also changed accordingly.
>
> Although without numberFormat does excel function automatically, however
> here we have an array, I have to assign the numberFormat because of the
> other cells.
>
> A code expamle is listed as following: Thanks in advance.
>
> Zuxiang DAI
>
>
> sub doIt()
>
> Dim aValues(0 To 1) As Variant
> Dim aFormats(0 To 1) As String
>
> aValues(0) = "000100.123456000"
> aFormats(0) = "@"
>
>
> aValues(1) = #2/10/2006#
>
> 'how can I tell excel this should be a system default date format?
> aFormats(1) = ????????
>
> Application.ErrorCheckingOptions.NumberAsText = False
>
> Range("A1", "B1").numberFormat = aFormats
> Range("A1", "B1").Value = aValues
>
> End Sub
>
>