Excel Date format screw-up

[corsair2]

Hi Folks!

Have come across the following "feature" in Excel regarding dates which I thought users should be aware of.
(see also faq68-5827).

If you're regional settings are not USA then take care if using a third party program to transfer date-type data into an Excel spreadsheet.
I've a sheet with 33800 rows in which two of the columns are dates ('dd/mm/yyyy' format).
36% (!) of the dates in these two columns have been randomly transposed into "mm/dd/yyyy" format by Excel.
Have exhaustively checked the data being transferred and the fault definitely appears to be at the Excel end...
If your running batch programs feeding data to spreadsheets for financial analyisis then this could be a bit of a problem?

Regards

Steve

 

[BobStubbs]

Is there a pattern to which dates are transposed? For example, are they dates which would be valid in both dd/mm/yyyy and mm/dd/yyyy formats?

e.g. 01/12/2005 is also 'valid' as 12/01/2005, but
24/01/2005 is invalid when transposed to 01/24/2005.



Bob Stubbs

 

[zbnet]

dd-mmm-yyyy is a good format for precisely this reason.

 

[xlbo]

Almost certainly gonna be what Bob has alluded to

"valid" dates ie those with 'days' less than 12 will be transposed to months. Non valid dates will be imported as text as excel tries to convert them but cannot

The reason for this is not actually to do with excel but with VBA. Almost certainly, the 3rd party tool is making use of VBA to import the data and it is this which is causing the headaches with dates etc.

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[corsair2]

Dear All,

Thanks for the quick response!
Bob, a cursory check seems to indicate that dates which would be invalid if transposed are being left.

zbnet, have just tried your suggestion (dd/mmm/yyyy hh:mm:ss)and now most dates are formatted on the sheet as 'dd/mm/yy hh:mm'!!
Also and apparently randomly, some dates are appearing as '31/Dec/1899 00:00:00'

Regards

Steve

 

[corsair2]

Hi xlbo,

I'm using Borland Delphi and linking to Excel using an automation object (ole).
Only the date data-types seem to be affected with Excel defaulting to an in-built Custom Format.
No VBA...

Regards

Steve

 

[corsair2]

Hi All!

Have also just tried sending the dates formatted from the host app. as pure text strings, not dates, but Excel still manages to randomly transpose them ...

Regards

Steve

 

[DPlank]

send the date as a string with a ' in front of it.

Excel will treat the date as text, after which you can reformat in Excel if required.

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[xlbo]

corsair2 - there will be VBA used in the interface of the 2 products - I guarentee it.

31/12/1899 would be excel trying to create a date form a value of -1 (1 = 1/1/1900)

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[corsair2]

Almost there!

placed ' ' in front of the string sent to Excel and all looked fine..however, have discovered 1200 (of 33800) with date as ' 31/12/1899 00:00:00'.
Geoff, you're right in that Delphi uses the 'object' structure of Excel (as does VBA) when connecting via automation.
It's possible something buried in this structure is causing the problem.
Don't have a clue where the '-1' might be coming from as it certainly isn't originating in Delphi...

Regards

Steve

 

[xlbo]

So these 1200 - what values do they have in Delphi ??

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[corsair2]

Geoff,

They all have a variety of dates from this current year - I've a seperate app. which also displays the data (which is originally from an Oracle Db) and this 'agrees' with the Delphi app re. dates and formatting.
Have also stepped through the Delphi app. (for a couple of hundred records...:-( ) and no problems there.
The fact that most of the dates/strings get through to Excel OK has me convinced this is an Excel problem.

Regards

Steve