Date format

[Andrew]

I am using a date field in English format that is then transposing from English to American format for no apparent reason.
Everything starts ok in the English format of DD/MM/YYYY.

The date is entered on a form with

Private Sub UserForm_Initialize()
Dim Today As Date
Today = Date = 05/07/2005 ie 05 July 2005.

' displays on the userform field correctly using
txtOrderDate.Value = Today = "05/07/2005"
'or
txtOrderDate.Value = Format(Today, "dd/mm/yyyy") also = "05/07/2005"

Private Sub Write_the_value_to_a_spreadsheet
Cells(1, 1).Value = txtOrderDate.Value
'or
Cells(1, 1).Value = Format(txtOrderDate.Value, "dd/mm/yyyy")
The spreadsheet displays 07/05/2005 ie 07 May 2005.

But the format options for the cell are showing as Customised "dd/mm/yyyy"
If I change the format of that cell to show the month then May is shown - so the spreadsheet is reading it correctly as English format, so I guess it must be the VBA code that is storing it in American format. I cant see where the transposition is occuring nor how to fix it.

Any ideas ?

Thanks
Andrew

 

[Black1]

I am using a date field in English format that is then transposing from English to American format for no apparent reason.
Everything starts ok in the English format of DD/MM/YYYY.

The date is entered on a form with

Private Sub UserForm_Initialize()
Dim Today As Date
Today = Date = 05/07/2005 ie 05 July 2005.

' displays on the userform field correctly using
txtOrderDate.Value = Today = "05/07/2005"
'or
txtOrderDate.Value = Format(Today, "dd/mm/yyyy") also = "05/07/2005"

Private Sub Write_the_value_to_a_spreadsheet
Cells(1, 1).Value = txtOrderDate.Value
'or
Cells(1, 1).Value = Format(txtOrderDate.Value, "dd/mm/yyyy")
The spreadsheet displays 07/05/2005 ie 07 May 2005.

But the format options for the cell are showing as Customised "dd/mm/yyyy"
If I change the format of that cell to show the month then May is shown - so the spreadsheet is reading it correctly as English format, so I guess it must be the VBA code that is storing it in American format. I cant see where the transposition is occuring nor how to fix it.

Any ideas ?

Thanks
Andrew

No help, but if you look 9 posts down, I've just posted the same question!
Guess moving to 01/07 has caused you problems too!

 

[John]

Hi Andrew,

I was trying format dates in Access this morning and the ddmmyyyy seems to work fine. However the problem below seems to want 3 M's. Don't really see why understand why d) should return the same as c) but e) returns both month name and number? Anyway hope it helps:

Cell(1, 1).Value =.....

a) "dd/mm/yyyy"= 07/05/2005 (US format)
b) "dd mm yyyy"= 05 07 2005 (as a string?)
c) "dd/mmm/yyyy"= 05/07/2005 (UK format)
d) "dd/mmmm/yyyy"= 05/07/2005
e) "dd/mmmmm/yyyy"=05/July7/2005

I hope I'm not adding to the confusion. If anyone can point me towards an official list I'd be very greatful.

Best regards

John

I am using a date field in English format that is then transposing from English to American format for no apparent reason.
Everything starts ok in the English format of DD/MM/YYYY.

The date is entered on a form with

Private Sub UserForm_Initialize()
Dim Today As Date
Today = Date = 05/07/2005 ie 05 July 2005.

' displays on the userform field correctly using
txtOrderDate.Value = Today = "05/07/2005"
'or
txtOrderDate.Value = Format(Today, "dd/mm/yyyy") also = "05/07/2005"

Private Sub Write_the_value_to_a_spreadsheet
Cells(1, 1).Value = txtOrderDate.Value
'or
Cells(1, 1).Value = Format(txtOrderDate.Value, "dd/mm/yyyy")
The spreadsheet displays 07/05/2005 ie 07 May 2005.

But the format options for the cell are showing as Customised "dd/mm/yyyy"
If I change the format of that cell to show the month then May is shown - so the spreadsheet is reading it correctly as English format, so I guess it must be the VBA code that is storing it in American format. I cant see where the transposition is occuring nor how to fix it.

Any ideas ?

Thanks
Andrew

 

[Dave Peterson]

VBA is USA centric--no matter your settings.

You might want to take a look at Ron de Bruin's site for some tips/code/free
calendar control:
http://www.rondebruin.nl/calendar.htm

 

[Black1]

I am using a date field in English format that is then transposing from English to American format for no apparent reason.
Everything starts ok in the English format of DD/MM/YYYY.

The date is entered on a form with

Private Sub UserForm_Initialize()
Dim Today As Date
Today = Date = 05/07/2005 ie 05 July 2005.

' displays on the userform field correctly using
txtOrderDate.Value = Today = "05/07/2005"
'or
txtOrderDate.Value = Format(Today, "dd/mm/yyyy") also = "05/07/2005"

Private Sub Write_the_value_to_a_spreadsheet
Cells(1, 1).Value = txtOrderDate.Value
'or
Cells(1, 1).Value = Format(txtOrderDate.Value, "dd/mm/yyyy")
The spreadsheet displays 07/05/2005 ie 07 May 2005.

But the format options for the cell are showing as Customised "dd/mm/yyyy"
If I change the format of that cell to show the month then May is shown - so the spreadsheet is reading it correctly as English format, so I guess it must be the VBA code that is storing it in American format. I cant see where the transposition is occuring nor how to fix it.

Any ideas ?

Thanks
Andrew

I've come up with a solution myself... may not be the most elegant, but it
works...

Private Sub TextBox1_afterupdate()
OrgDate = TextBox1

a = Len(OrgDate)
b = Application.WorksheetFunction.Find("/", OrgDate)
c = a - b
d = Right$(OrgDate, c)
e = Trim(Left$(d, Len(d) - Application.WorksheetFunction.Find("/", d)))
e = e * 1
g = b - 1
h = Right$(d, Len(d) - Application.WorksheetFunction.Find("/", d))


If e = 1 Then f = "-Jan-"
If e = 2 Then f = "-Feb-"
If e = 3 Then f = "-Mar-"
If e = 4 Then f = "-Apr-"
If e = 5 Then f = "-May-"
If e = 6 Then f = "-Jun-"
If e = 7 Then f = "-Jul-"
If e = 8 Then f = "-Aug-"
If e = 9 Then f = "-Sep-"
If e = 10 Then f = "-Oct-"
If e = 11 Then f = "-Nov-"
If e = 12 Then f = "-Dec-"

OrgDate = Left$(OrgDate, g) & f & h


End Sub

When Orgdate is applied to the spreadsheet the format selected there
converts it fine.

HTH

Black

 

[Andrew]

Sorted it I think. Thanks for the help anyway.

You need this for the form display format, but its this that does the
transposition.
txtOrderDate.Value = Format(Today, "dd/mm/yyyy")

To use it on the sheet
Cells(1, 1).Value = DateValue(txtOrderDate.Value)

Seems to work ok.

 

[okaizawa]

Hi,
how about converting a Date-type value to excel's serial number?

Sub Test()
Dim strDate As String
Dim VBDate As Date
Dim ExcelSerialNum As Double
strDate = Format(Date, "dd/mm/yyyy")
VBDate = StrToDate(strDate, "/", "DMY")
ExcelSerialNum = CDbl(VBDate)
ActiveCell.Value = ExcelSerialNum
End Sub

Function StrToDate(strDate As String, Deleimter As String, DateType As String)
As Variant
Dim a() As String
Dim y As Integer, m As Integer, d As Integer
Dim x As Date
On Error GoTo ErrorHandler
a = Split(strDate, Deleimter)
Select Case UCase(DateType)
Case "DMY": y = a(2): m = a(1): d = a(0)
Case "MDY": y = a(2): m = a(0): d = a(1)
Case "YMD": y = a(0): m = a(1): d = a(2)
Case Else: Exit Function
End Select
x = DateSerial(y, m, d)
If CLng(Format(x, "yyyymmdd")) = y * 10000& + m * 100 + d Then
StrToDate = x
End If
Exit Function
ErrorHandler:
Exit Function
End Function

Function DateToExcelSerialNum(VBDate As Date, WorkbookObj As Workbook) As Double
If WorkbookObj.Date1904 Then
DateToExcelSerialNum = VBDate - 1462
Else
DateToExcelSerialNum = VBDate
If VBDate <= 60 Then DateToExcelSerialNo = VBDate - 1
End If
End Function

 

[okaizawa]

sorry for misspelling.
please correct 'Deleimter' to 'Delimiter'.