«Пух посмотрел на свои передние лапки. Он знал, что одна из них была правая, знал он, кроме того, что если решит, какая из них правая, то остальная будет левая. Но он никак не мог вспомнить, с чего надо начать ...» А.А. Милн «Винни-Пух и все-все-все»

Загадка Excel

Довольно часто таблицы Excel выполняют роль промежуточного инструмента в материальном учёте в магазине, это уже не калькулятор, но ещё и не специальная учётная программа для магазина. Такой плавный переход от ручки с тетрадкой к полноценному и удобному, но чуть более сложному инструменту учёта. 12.07.2014 мой клиент озадачил меня переносом таблицы Excel клиентов с их днями рождения в нормальную базу данных и работой с ними прямо в кассовой программе, в модуле кассира-операциониста.

Чтобы кассир заносил данные покупателя в базу данных при обработке его подарочной дискотной карты, это потом используется для поздравления клиентов с днём рождения, рассылки смс-сообщений, вот такой серьёзной уровень работы с клиентами. И при переносе данных из таблицы Excel в базу на Foxpro получилась загадочная для меня ситуация. Дело в том, Excel хранит дату как целое число, например, 1 — это 1.1.1900 — это так сказать точка отсчёта, в результате такого подхода возможны простые арифметические операции с датами, например разница между ними, и вот на картинке ниже видна разница между 12.07.2014 и 1.1.1900 = 41831, собственно 41831 это и есть дата 12.07.2014. Время, кстати, хранится как дробная часть числа, например, число 2,5 — это 2.1.1900 12:00.

Разница в Excel

При импорте из файла XLS в таблицу Foxpro дата, естественно, импортируется как число, и для перевода в нормальную дату требуется к начальной дате Excel нужно прибавлять это число. Начальная дата — 1.1.1900, чтобы получить правильный расчёт надо сдвинуться к началу, к нулю, то есть расчёт такой: 1.1.1900 — 1 + число-дата-Excel. Но при попытке рассчитать дату и сравнить с той, которая была в таблице Excel, выяснилось, что дата на один день больше! Что за казус? Зачем-то надо отнимать ещё один день! Зачем? Физика процесса непонятна, а раз непонятна, возможна ошибка, а программисты — они народ дотошный, любят точность во всём, точность и программист это почти синонимы :). Что делать — иначе просто программы работать не будут, программа не понимает — пойди туда и принеси немножко. Что значит немножно? Сколько вешать в граммах?

Исследования этого загадочного и непонятого явления привели к следующему, смотрите картинки ниже.

Разница в FoxPro

Разница в Python

Разница в planetcalc.ru

Получается что в других разных программах и языках программирования разница между 12.07.2014 и началом прошлого века составляет 41830 дней! Это что? Косяк Excel? И много у него таких? Дальнейшие раскопки привели к тому, что Excel считает 1900 год — високосным и в нём возможна дата 29.02.1900. Поэтому для корректного перевода числовой даты Excel в нормальную дату нужно использовать формулу:


DateFox = {01.01.1900} — 1 + DateExcel + iif(DateExcel<60, 0, -1)

где первая -1 — это корректировка к началу отсчёта, а iif(DateExcel<60, 0, -1) - это учёт 1900-го високосного года для дат после 28.02.1900, точнее трактовки Excel его как високосного года в отличии от Python, Calc, Foxpro и PlanetCalc.ru.

Если попытаться найти этому объяснение, то приходим в википедию. В странах, использовавших на тот момент юлианский календарь, в том числе в России, 1900 год был високосным. Поскольку в юлианском календаре високосный год — всякий, делящийся на 4, так как год в этом календаре считался равен 365,25 = 365 + 1/4. Григорианский в России был введен в 1918 году. Он более точный, в странах, использовавших григорианский (большинство стран Европы) 1900 год високосным не был, так как делится на 100, но не делится на 400. В григорианском календаре длительность года принимается равной 365,2425 суток — можно записать так = 365 + 0,25 — 0,01 + 0,0025 или записывая дробями = 365 + 1/4 — 1/100 + 1/400.

То есть каждый 4-ый или 400-ый год — високосный, а каждый год, кроме 400-го, 100-ый год, столетие то есть — нет, например високосные 1600 и 2000, а 1700, 1800 и 1900 не високосные. Остаётся не понятным только — почему именно Excel, в отличии от многих других программ, считает 1900 как в юлианском календаре :). Удачи в бизнесе!

Оставить комментарий

E-mail не будет опубликован. Обязательные поля отмечены *

Это не спам.