Как преобразовать дату MS Excel из формата с плавающей запятой в формат даты в Ruby?

У вас есть ошибка в нумерации дней — из-за ошибки в Lotus 1-2-3, с которой Excel и другие программы для работы с электронными таблицами тщательно поддерживали совместимость в течение 30 с лишним лет.

Первоначально день 1 должен был быть 1 января 1900 года (что, как вы заявили, сделало бы день 0 равным 31 декабря 1899 года). Но Lotus неправильно считает 1900 год високосным, поэтому, если вы используете числа Lotus для настоящего времени и считаете в обратном порядке, правильно делая 1900 год простым, числа дней для всего до 1 марта 1900 года будут слишком большими. День 1 становится 31 декабря 1899 года, а день 0 смещается обратно на 30-е число. Таким образом, эпохой арифметики дат в электронных таблицах на основе Lotus является суббота, 30 декабря 1899 года. 0 "31 декабря" при условии, что это была суббота! Но другие электронные таблицы на основе Lotus этого не делают, и Ruby, конечно же, тоже.)

Однако даже с учетом этой ошибки указанный вами пример неверен: день Lotus 40 396 приходится на 6 августа 2010 г., а не на 15 октября. Я подтвердил это соответствие в таблицах Excel, LibreOffice и Google, и все они совпадают. Вы, должно быть, где-то сталкивались с примерами.

Вот один из способов сделать преобразование:

Time.utc(1899,12,30) + 40396.days #=> 2010-08-06 00:00:00 UTC

В качестве альтернативы вы можете воспользоваться другой известной перепиской. Нулевым временем для Ruby (и систем POSIX в целом) является момент 1 января 1970 года, полночь по Гринвичу. 1 января 1970 года — 25 569 день Лотоса. Если вы не забываете выполнять свои расчеты в формате UTC, вы также можете сделать это:

Time.at( (40396 - 25569).days ).utc # => 2010-08-06 00:00:00 UTC

В любом случае вы, вероятно, захотите объявить символическую константу для даты эпохи (либо объект Time, представляющий 1899-12-30, либо значение POSIX «день 0» 25 569).

Вы можете заменить эти вызовы .days умножением на 86400 (секунд в день), если вам не нужно active_support/core_ext/integer/time ни для чего другого, и вы не хотите загружать его только для этого.

«Excel хранит даты и время в виде числа, представляющего количество дней с 1900-0 января, плюс дробную часть 24-часового дня: ddddd.tttttt . Это называется последовательной датой или последовательной датой-временем». (http://www.cpearson.com/excel/datetime.htm)

Если ваш столбец содержит дату и время, а не просто дату, полезен следующий код:

 dt = DateTime.new(1899, 12, 30) + excel_value.to_f

Также имейте в виду, что на листе Excel есть 2 режима дат: на основе 1900 и 1904, которые обычно включены по умолчанию для электронных таблиц, созданных на Mac. Если вы постоянно обнаруживаете, что ваши даты отстают на 4 года, вам следует использовать другую базовую дату:

 dt = DateTime.new(1904, 1, 1) + excel_value.to_f

Вы можете включить/отключить режим даты 1904 года для любой электронной таблицы, но тогда даты будут отображаться в электронной таблице на 4 года позже, если вы измените настройку после добавления данных. В общем, вы всегда должны использовать режим даты 1900, так как большинство пользователей Excel в дикой природе основаны на окнах.

Примечание. Проблема с этим методом заключается в том, что округление может происходить на +/- 1 секунду. Для меня даты, которые я импортирую, «достаточно близки», но их просто нужно иметь в виду. Лучшее решение может использовать округление долей секунды для решения этой проблемы.

Вы неправильно делаете расчет. Как вы пришли к ожидаемому результату 2010-10-15?

В Excel 40396 равно 2010-08-06 (конечно, без использования календаря 1904 года). Чтобы продемонстрировать это, введите 40396 в ячейку Excel и установите формат yyyy-mm-dd.

В качестве альтернативы:

40396 / 365.2422 = 110.6 (years -- 1900 + 110 = 2010)
0.6 * 12 = 7.2 (months -- January = 1; 1 + 7 = 8; 8 = August)
0.2 * 30 = 6 (days)

Календарь Excel неправильно включает 1900-02-29; это объясняет разницу в один день между вашим результатом 2010-08-08; Я не уверен в причине второго дня разницы.