jsonkenl
Trouble parsing a date field
I have a xlsx file, which has a field with the content "01.05.2018". xlsxir parses this into {2014, 4, 30}. While changing the date from May 1st into Apr 30th could be explained as some timezone adjustment (note that this is Berlin, which is both german - i.e. uses DD.MM.YYYY - and UTC+1/UTC+2) the year change cannot be explained that way.
Any ideas how I could fix this? Is this a bug with xlsxir? I can easily provide the XLSX file if that helps.
Note that I do
[ headers | rows ] = Xlsxir.stream_list(path, 0) |> Enum.map(fn s -> s end)
@radiospiel Thanks for letting me know. I'll take a look as soon as possible and get back to you.
@radiospiel it might help to have the xlsx file as I've been unable to reproduce the error. I've formatted a cell in a new workbook as DD.MM.YYYY and it's parsing it as {2018, 5, 1} for me.
@jsonkenl ah I missed the notification email, thanks for the speedy response :) So, here it is: forum-bug.xlsx
@jsonkenl I wonder if you found the time to look into this? Any help is greatly appreciated :)
@radiospiel My apologies for the delay. I've added this to my to-do for the weekend. I should have something for you soon.
Hey @radiospiel, I think it has something to do with your regional date/time settings on your operating system or settings within Excel. I created a custom date field in a new workbook I opened up and the sequential serial number I get from 01.05.2018 using dd.mm.yyyy is '43221' while the sequential serial number from your workbook for the same date is '41759'. Only date formats that begin with an asterisk respond to changes in regional date and time settings. Custom dates to not.
Xlsxir is designed to interpret dates where a serial date of '1' is equal to 1/1/1900. When I convert '1' to a date in your workbook, I get 1/2/1904.
@jsonkenl well, that sounds super weird, but I will pick deeper. If i find something I'll check back with you. In the meantime lets close this issue for now. Thanks for your help!
Me again :) It seems someone else also found out about this, see https://www.accountingweb.com/technology/excel/when-excel-dates-mysteriously-shift-by-4-years
@radiospiel Ok, I see it now. When you crack open a .xlsx file, it's made up of a bunch of .xml files (among others). In the workbook.xml file, I found the following line in your workbook: <workbookPr codeName="ThisWorkbook" date1904="1"/>. I should be able to use this line to programmatically check for this issue and then adjust the date calculation accordingly. I'll add it to my todo and will notify you when done.
In the meantime, you can go into "Excel Options > Advanced > When calculating this workbook:" and uncheck the "Use 1904 date system" option to force your workbook to work with Xlsxir.
@jsonkenl yea, I did this and it seems to do the trick. An enhancement in xlsxir would make it more awesome still :) Thanks for the good work!