1

Note:

xw.__version__
Out[84]: '0.10.2'
pd.__version__
Out[85]: '0.16.2'

I have the following df:

>>> df.head()
    data
1900-01-31  0.0315
1900-02-28  0.0314583333333
1900-03-31  0.0314166666667
1900-04-30  0.031375
1900-05-31  0.0313333333333

and when I run:

xw.sheets(str(sht)).range(k).value = d_of_dfs[k]

I see the following in excel:

            data
1900-02-01  0.0315
1900-02-29  0.031458333
1900-03-31  0.031416667
1900-04-30  0.031375
1900-05-31  0.031333333
1900-06-30  0.031291667
1900-07-31  0.03125
1900-08-31  0.031208333
1900-09-30  0.031166667
1900-10-31  0.031125

is xlwings hijacking the early date and messing it up?

Also - xlwings cannot handle dates prior to 1900 at all.

jason m
  • 6,519
  • 20
  • 69
  • 122

1 Answers1

3

This issue stems from the way excel stores dates and a bug that harkens back to Lotus 123.

From http://www.cpearson.com/excel/datetime.htm

Dates

The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0. The number 1 represents 1900-Jan-1. It should be noted that the number 0 does not represent 1899-Dec-31. It does not. If you use the MONTH function with the date 0, it will return January, not December. Moreover, the YEAR function will return 1900, not 1899.

Actually, this number is one greater than the actual number of days. This is because Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a "bug". Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123. When Excel was introduced, 123 has nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus' bug, in order to fully compatible. Users who switched from 123 to Excel would not have to make any changes to their data.
As long as all your dates later than 1900-Mar-1, this should be of no concern.

This answer has more detail though I've just been converting the date to string which excel seems to parse correctly.

converted = str(datetime.date(1900, 1, 1))
Community
  • 1
  • 1
Schnottus
  • 31
  • 2