When does 3 Nov = March 11?

[AaronM_NZ]

Always according to Excel/VBA I have been working in VBA for the last month or two, and it is really driving me spare! This one has been fustrating me today, after getting some strange results I made the following sub:

Sub Test()
Dim aryDates(0) As Date

aryDates(0) = "03-Nov-2008"

ActiveCell.Value = ""
For z = 0 To UBound(aryDates)
    ActiveCell.Value = ActiveCell.Text & Format(aryDates(z), "dd-mm-yyyy")
Next

End Sub

One would think the result (here in NZ) would be 03-11-2008, but alas Excel thinks I want all my dates formatted in US format, despite my regional settings being set to EN-NZ, and makes the cell value 11-03-2008.... Even after specifically making the month format mmm. Gahh!!

[Andy_L_J]

I think I got around this the same way...can't get my hands on the code at the moment.

I don't speak Illidiot - please talk slowly and clearly

[AaronM_NZ]

I am building a string value of concatanated dates for updating to a SQL database, so thankfully I dont have to actually write the dates to the sheet, but I thought I would so I could see what was going on, it just slowed me down and fustrated the heck out of me! The DB is updating correctly, and I am just adding a space to the front of the cell so it doesnt think its a date and then kindly reformats it. Still very annoying, especially when I am specifically stating the month as text and the date format....

[Andy_L_J]

I have looked at a large "ERP" spreadsheet and noticed we have set the date formats as: Date Format *14/03/2001 "Date formats that begin with an asterix (*) respond to changes in regional date and time settings that are specified for the os..." I am looking for the code to see how we handled it there.

I don't speak Illidiot - please talk slowly and clearly

[Andy_L_J]

OK, so we were creating dynamic sql for writing to the Excel sheets using ADO.NET. The dates were formated MM/dd/yyyy before being passed to the OleDbCommand. Something like this (but better)...

Public Function convToUS(ByVal d As Date) As String
convToUS = d.Month.ToString & "/" & d.Day.ToString & "/" & d.Year.ToString
End Function

Dim d1 As String = convToUSDate(dtp1.Value)

Dim s2 As String = "INSERT INTO [DailyProd$] ([Date],[WO_Number],[Qty],[ActWgt],[ActMmin]," & _
"[Status],[ProdCat],[Formula]) VALUES (" & d1 & ",'" & txtWONo.Text & "'," & _
CInt(txtQty.Text) & "," & CInt(txtWgt.Text) & "," & CDbl(txtMmin.Text) & ",'" & _
cboStatus.SelectedItem & "','" & txtProdCat.Text & "','" & txtFormula.Text & "')"

This resulted in correct formatting of the date values in the Excel sheets when opened. Although in effect we have converted to us format and then the excel sheet has re-converted to local format. Does that make sense? It worked! (A dirty front end written in VB.NET 2005)

I don't speak Illidiot - please talk slowly and clearly

[Marc Clifton]

wormer90 wrote:

"dd-mm-yyyy"

Erm, isn't the formatter confused because you're telling it to expect a 2 digit month but you're giving it:

wormer90 wrote:

"03-Nov-2008"

? I guess I'm too lazy to check this out myself. :) Marc

Available for consulting and full time employment. Contact me. Interacx

[supercat9]

I prefer to use the long-form yyyy-mm-dd because that's almost unambiguous, but I read recently that in some cases Microsoft can interpret that as yyyy-dd-mm? Why would anyone ever use yyyy-dd-mm? If I had my druthers, yyyy-mm-dd would be the standard method for storing machine-readable dates in text form. Regardless of locale, 2009-02-05 would always be the the day that Americans would call February 5, 2009. I am baffled by why anyone would think that the proper behavior for a date that's syntactically but not semantically valid is to try parsing it differently, but I've seen programs do that. If one is importing a bunch of date/time data, which is improperly formatted, it would be better for it to interpreted as:

Oct 2, 2000
Nov 2, 2000
Dec 2, 2000
INVALID DATE
INVALID DATE

than

Oct 2, 2000
Nov 2, 2000
Dec 2, 2000
Feb 13, 2000
Feb 14, 2000

but seen the latter in some cases. Bizarre. (Incidentally, for the example data in question, the correct interpretation would have been Feb. 10-14, but despite the fact that the latter interpretation got two of the dates right, I'd consider the former interpretation more helpful).

[Megidolaon]

Excel does a lot of automatic conversion. Like when entering numbers with some other signs, it usually makes a date out of them. For example I coded a DB access in VBA and it should display ID numbers in cells. Just display them was fine, but don't you dare try to click into a cell, then Excel made a date out of them.

[Kevin Li Li Ken un]

Gee, why couldn't everyone just pick an endianness. Big or little endian is okay, but the US has to go with mixed endianness. :doh:

---

My GUID: ca2262a7-0026-4830-a0b3-fe5d66c4eb1d :) Now I can Google this value and find all my Code Project posts!

[Vikram A Punathambekar]

WTF? Nobody in their right mind would use yyyy-dd-MM. :wtf: It's bad enought the Americans use the middle-Endian MM-dd-yyyy, but MS screws up the standard xxxx-xx-xx by having a yyyy-dd-MM interpretation? :doh:

Cheers, Vıkram.

Carpe Diem.