Excel spreadsheets - detecting dates based on 1900 vs 1904 1

[dbMark]

I just recently encountered a problem that could happen again sometime in the future regarding the date system flag in Excel at Tools / Options / Calculation / 1904 date system.

Background: Excel allows two date offset systems that are 1462 days apart. The Most common is the default 1900 date system. The other for 1904 originated on the Mac back in the 1980s and is likely used only by some Mac users. Each day was assigned its own sequential number, but at different starting points for each system. Naturally, Excel has multiple layers of formatting that hides this behavior "under the hood" so most users don't know about it.

Description of the differences between the 1900 date system and the 1904 date system in Excel

http://support.microsoft.com/kb/214330

So the dilemma was that a spreadsheet was submitted and all the dates were 4 years off and the mystery was due to the conflicting date systems. Now we are concerned that other users may submit similarly configured spreadsheets and we want to be able to identify those files up front so erroneous dates are not saved and propagated, perhaps not being detected for days, months or longer.

Of course, we could always restrict submissions to CSV delimited text files, but we all know that won't happen, spreadsheets are simply too much of a common standard.

Clearly, the setting is saved in the file, but we were unable to identify the flag by examining the data collections in ADO. (We use ADO rather than IMPORT FROM or APPEND FROM for several reasons, one of them being that VFP SP1 can't read some of the newest Excel 2007 formats such as XLSX. I haven't installed SP2 so I don't yet know if it can read them natively.)

objExcConStr.ConnectionString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=]+mySpreadSheet+[;Extended Properties='Excel 12.0;HDR=No;IMEX=1']

I did an internet search and didn't find any help for how to locate that flag. I searched two otherwise identical Excel 2003 spreadsheets and found that byte 765 seems to be the location of that flag.

Questions:

Is it always in the same location for all versions of Excel? I'm just not sure that every version of Excel will have this flag in the same location.

Does anyone know another or better way to identify this date system flag?


Does VFP SP2 read the newest Excel 2007 formats natively?

 

[Mike Lewis]

dbMark,

You've raised some good points.

To answer your main question, I don't know how to do this with ADO. Perhaps you can't. After all, ADO is concerned with retrieving data, whereas the item you are interested in is related to the application. But I might be wrong.

However, you can do it with Automation:

oxl = CREATEOBJECT("excel.application")
owb = oxl.Workbooks.Add()
? owb.Date1904

Regarding versions of Excel, you said:

VFP SP1 can't read some of the newest Excel 2007 formats such as XLSX

It's worse than that. No version of VFP (including SP2) can read XLSX, but, more importantly, nor they can read the original XLS format if it was saved in Excel 2007 (even if saved in "compatibility mode").

We had a long thread here on this subject a year or so ago, and I have also written an article about it for FoxPro Advisor.

The other for 1904 originated on the Mac back in the 1980s and is likely used only by some Mac users.

That might be true, but, if I remember right, the 1904 base was introduced for compatibility with Lotus 1-2-3, which at the time was the dominant PC spreadsheet. Of course, it could be that Lotus chose that date for compatibility with Visicalc, which dominated on Apple computers for a while.

Hope this helps.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dbMark]

It's worse than that. No version of VFP (including SP2) can read XLSX, but, more importantly, nor they can read the original XLS format if it was saved in Excel 2007 (even if saved in "compatibility mode").

And that is exactly why we are using ADO/OLEDB for importing and appending from spreadsheets. There were multiple new file types in Excel 2007 that were breaking our code. There was no way we could tell our users not to use the latest spreadsheet types.

That other thread was thread1251-1338029

As for creating the Excel Application object, doesn't that require having Excel installed? We're running this on a server and we'd rather not install Excel there. Is there another way to call that object?

 

[Mike Lewis]

As for creating the Excel Application object, doesn't that require having Excel installed?

Yes, that's right. I don't know any other way to access the 1904 option. It must be stored in the workbook somewhere (since the setting is specific to the workbook, not the application), but I haven't a clue how to find out where it is.

The only thing I can suggest is to create two identical workbooks -- one with 1904 dates and the other without -- and compare them byte by byte. If you discover where the setting is stored, you could then use FILETOSTR() to read the file into VFP, and work on it from there. But you'd still be subject to changes in the file format.

Tough problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dbMark]

It is one bit in byte 765, at least in the spreadsheets I've checked. I'm not sure if the other bits are used for anything else, so I added the MOD() or BITTEST() functions to filter them out. Without any released documentation, this location could change in other versions.

xx=FILETOSTR("mySS.xls")
? IIF(MOD(ASC(SUBSTR(xx,765,1)),2)=0,"1900","1904")
? IIF(BITTEST(ASC(SUBSTR(xx,765,1)),0),"1904","1900")

 

[Mike Lewis]

dbMark,

Glad to see you found the answer. But you are absolutely right not to rely on this in other versions. The location could well be different.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk