date field data value 1 doesn't convert to date

[LeeLofgren]

xlsx data field of 01/01/1900 converts to 1 Found that changing self.data from 1 to 2 fixes problem added code in def handleCharData(self,data) after line 617 if format_type = 'date': if self.data == "1": self.data = "2"

[ImBadr]

Hi Lee Lofgren, can you re-explain me your solution to this problem, please.😊

[LeeLofgren]

Hi,

The problem is

The python code is using 12/30/1899 as a launch point to convert the excel datevalue() and the reason python is using 12/30/1899 instead of 12/31/1899 is because Excel has a bug in it where it thinks 1900 is a leap year even though 1900 is not divisible by 400 so is not a leap year (See: https://en.wikipedia.org/wiki/Century_leap_year )

In reality, the 02/29/1900 serial numbers should be shifted by 1 on the 02/29/1900 mark instead of starting back on 12/30/1899 which would mean that the dates between 12/30/1899 and 03/01/1900 would be wrong

Most of the times, this issue would not be relevant except some excel export systems default empty dates to 1/1/1900 which becomes 1 which returns 12/31/1899 instead of 1/1/1900

But changing 1 to 2 while not adding to the value field other than that was an easy way of dealing with my issue

However, 3 comes out to 1/2/1900 which is incorrect

Probably the code should say if int(self.data)<=60 then use 12/31/1899 else use 12/30/1899

If you experiment with Excel you get 59 -> 02/28/1900 60 -> 02/29/1900 61 -> 03/01/1900 62 -> 03/02/1900 63 -> 03/03/1900

With Python where you use datetime.datetime(1899,12,30) + datetime.timedelta(float(x)) you get 59 -> 1900-02-27 60 -> 1900-02-28 61 -> 1900-03-01 62 -> 1900-03-02 63 -> 1900-03-03

so as you can see, the answer is wrong from 1 to 60 and then becomes correct

I was just trying to get rid of a 12/31/1899 error because the spreadsheet I was trying to pull from was using 1 in their un-assigned date fields

But the correct code should be

if int(self.data)<=60: date = datetime.datetime(1899,12,31) + datetime.timedelta(float(self.data)) else: date = datetime.datetime(1899,12,30) + datetime.timedelta(float(self.data))

Or if int(self.data)>=60 then self.data = int(self.data) - 1 date = datetime.datetime(1899,12,31) + datetime.timedelta(float(self.data))

Also note that Excel does not support date numbers before 01/01/1900 so it returns the actual date in these cases

The date1904 shouldn't occur anymore though it technically is possible since that was a fix on the Mac to skip over Microsoft's 1900 leap-year mistake and to deal with Mac not supporting an earlier date https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487

However spreadsheets are able to still be set to the 1904 type so it doesn't hurt to support it

Hope this helps

Lee

Lee Lofgren Accounting Enhancements, Inc 17800 Excelsior Blvd Minnetonka, MN 55345 (612)396-4171 lee@lofgrens.org

----- Original Message -----

From: "ImBadr" notifications@github.com To: "dilshod/xlsx2csv" xlsx2csv@noreply.github.com Cc: "LeeLofgren" lee@lofgrens.org, "Author" author@noreply.github.com Sent: Thursday, October 29, 2020 10:02:42 AM Subject: Re: [dilshod/xlsx2csv] date field data value 1 doesn't convert to date (#196)

Hi Lee Lofgren, can you re-explain me your solution to this problem, please.😊

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub , or unsubscribe .