from_excel function for Julian dates less than 60

Issue #408 resolved
Kiril Ivanoski
created an issue

I've noticed that from_excel function (/date_time/ini.py) returns previous date than one saved in excel cell for dates less than 1900-2-28. Ex. when cell date is '1900-01-01' from_excel will return 1899-12-31.

As function to_excel decreases date for one day when *jul is lower than 60:

@lru_cache()
def to_excel(dt, offset=CALENDAR_WINDOWS_1900):
    jul = sum(gcal2jd(dt.year, dt.month, dt.day)) - offset
    if jul <= 60 and offset == CALENDAR_WINDOWS_1900:
        jul -= 1
    if hasattr(dt, 'time'):
        jul += time_to_days(dt)
    return jul

I'm supposing that reverse operation (increase for one day) should be placed in function from_excel:

@lru_cache()
def from_excel(value, offset=CALENDAR_WINDOWS_1900):
    if value is None:
        return
    if value < 60 and offset == CALENDAR_WINDOWS_1900:
        value += 1
    parts = list(jd2gcal(MJD_0, value + offset - MJD_0))
    _, fraction = divmod(value, 1)
    diff = datetime.timedelta(days=fraction)
    if 0 < abs(value) < 1:
        return days_to_time(diff)
    return datetime.datetime(*parts[:3]) + diff

Test function for bug simulation:

def test_date():
        some_date = datetime.datetime(1900, 1, 1, 0, 0, 0, 0)
        book = Workbook()
        sheet = book.get_active_sheet()
        sheet.cell("A1").value = some_date
        dest_filename = 'date_1900.xlsx'
        book.save(dest_filename)

        test_book = load_workbook(dest_filename)
        test_sheet = test_book.get_active_sheet()
        assert test_sheet.cell("A1").value == some_date

Comments (6)

  1. Kiril Ivanoski reporter

    There is test module /date_time/test_datetime.py where test cases for method to_excel are:

    @pytest.mark.parametrize("value, expected",
                             [
                                 (date(1899, 12, 31), 0),
                                 (date(1900, 1, 15), 15),
                                 (date(1900, 2, 28), 59),
                                 (date(1900, 3, 1), 61),
                                 (datetime(2010, 1, 18, 14, 15, 20, 1600), 40196.5939815),
                                 (date(2009, 12, 20), 40167),
                                 (datetime(1506, 10, 15), -143618.0),
                             ])
    

    From test cases above, date(1900, 2, 28) should be interpreted as Julian value 59, (third item in test cases is (date(1900, 2, 28), 59)).

    Method from_excel should perform in opposite way. Test cases for method from_excel are:

    @pytest.mark.parametrize("value, expected",
                             [
                                 (40167, datetime(2009, 12, 20)),
                                 (21980, datetime(1960,  3,  5)),
                                 (60, datetime(1900, 2, 28)),
                                 (-25063, datetime(1831, 5, 18, 0, 0)),
                                 (40372.27616898148, datetime(2010, 7, 13, 6, 37, 41)),
                                 (40196.5939815, datetime(2010, 1, 18, 14, 15, 20, 1600)),
                                 (0.125, time(3, 0)),
                                 (None, None),
                             ])
    

    Please notice that third item is (60, datetime(1900, 2, 28)).

    We can conclude that to_excel expects (date(1900, 2, 28), 59), and from_excel expects (60, datetime(1900, 2, 28)),. So, one of mentioned items must be wrong.

    I think this is enough for you to simulate reported bug.

  2. CharlieC

    Thanks but a PR is always easier. I think this is fixed but I had to add special handling for x < 1 because Excel likes to think of values less than 1 as times.

  3. Log in to comment