Новости

Загадка 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 не будет опубликован. Обязательные поля помечены *

Это не спам.