Пишу этот текст совершенно без намерения оскорбить авторов Excel. Это фантастически успешный софт, который по праву считается золотым стандартом среди табличных редакторов. Тем не менее, в этом году Excel уже стукнуло 40. В таком почтенном возрасте его семантика всерьёз страдает из-за костылей, которые накапливались не один десяток лет, обеспечивая обратную совместимость.

Одно из самых интересных дел, которыми мне приходится заниматься на работе — это обратная разработка Excel. Я выискиваю причуды в его поведении и решаю, как поступать с ними в нашем фирменном табличном редакторе. Благодаря моему повседневному труду, наши пользователи, возможно, и не подозревают, сколько недокументированных грехов совершила Microsoft ради совместимости. Приглашаю вас одним глазком взглянуть на те ужасы, с которыми мне приходится тягаться — и тогда вы тоже станете бояться Microsoft Excel — не потому, что чего-то не знаете, а наоборот, так как знаете слишком много.

Магия чисел

Много лет генетики мучились с чрезмерно придирчивым парсингом дат в Excel. Например, в качестве дат редактор трактовал такие названия как MARCH1 или SEPT2, которые датами, естественно, не являются. Но в парсере дат в Excel встречаются и гораздо более дикие пограничные случаи.

Так, если ввести в ячейку текст ="1/2", то, конечно же, в ней будет текст «1/2».

А если приплюсовать к этому единицу?

45660? Что?? Подсказка: если в будущем ещё раз попробуете повторить этот фокус, то у вас может получиться другое число.

Причём, такое происходит не только с датами! Иногда парсер времени в Excel откусывает больше, чем способен прожевать. Разумеется, если ввести в ячейку 10:25, то получится 10:25 утра. Но что произойдёт, если ввести 10:75?

0,46875?? Откуда что берётся?

Обещаю: я объясню, что здесь происходит. Но сначала нужно сделать небольшой экскурс в техническую документацию и в историю католической церкви.

(Не) читайте мануал

В обоих этих случаях мы стараемся перехитрить Excel, чтобы он разбирал наш ввод именно как дату или время, а отображал как число. В официальной документации по функции DATEVALUE() сказано:

Даты в Microsoft Excel хранятся как последовательные номера, которые могут использоваться в расчётах. По умолчанию 31 декабря 1899 года – это день номер 1. Соответственно, 1 января 2008 года – это день номер 39448, так как он наступает через 39 448 дней после 1 января 1900 года.

Эта модель удобна, но в ней есть две неточности. Во-первых, получается, что день номер 1 — это 1 января 1900 года, а не 31 декабря 1899 года. На самом деле, в Excel вообще не отображаются даты до 1900 года, и программа действует так, как будто настаивает, что 0 — это нулевое января 1900 года.

К счастью, ошибка с днём номер 1 исправлена в других местах, а именно в документации по функции MONTH() и во многих других функциях, но там остаётся ещё одна неточность, гораздо более коварная.

На самом деле, между 1 января 1900 года и 1 января 2008 года всего 39 446 дней, а не 39 448. Да, я могу понять ошибку смещения на единицу, но почему у Excel получилось смещение на 2?

Допустим, я присваиваю номер каждому дню недели. Понедельник – день номер 1, вторник – день номер 2, …, а пятница – день номер 5. Но это же не означает, что пятница наступает через 5 дней после понедельника. Всего через 4. Легко сосчитать, что, если вычесть номер понедельника из номера пятницы, то получится  5 - 1 = 4. Здесь та же история: чтобы узнать, сколько дней разделяет 1 января 1900 года и первое января 2008 года, нужно вычесть 1900 из 2008, переведя годы в дни: 39448 - 1 = 39447. Уже ближе, но всё равно ошибка на единицу. Чтобы понять, откуда она взялась, перенесёмся почти на  450 лет в прошлое.

История календаря

В октябре 1582 года Папа Римский Григорий XIII официально объявил, что католическая церковь переходит на новую календарную систему, разработанную Алоизием Лилием  (на тот момент уже покойным) и Христофором Клавием. Юлианский календарь, где на каждые 4 года приходился один високосный год, действовал к тому времени уже более 1600 лет, но так отстал от времени, что Пасха стала сильно не совпадать с весенним равноденствием. В новоиспечённом григорианском календаре эту разбежку удалось скорректировать, добавив новое правило. Годы, номера которых делятся на 100, не являются високосными, за исключением тех лет, номера которых делятся на 400 – и эти годы високосными являются . Вот почему 2000 год был високосным (он делится на 400), а 1900 год — нет.

В 1983 году, почти ровно через 400 лет после принятия григорианского календаря, компания Lotus Software выпустила Lotus 1-2-3 – революционный табличный редактор, также приспособленный к работе с базой данных и построению графиков. К сожалению, разработчики Lotus 1-2-3 оказались не в курсе папского декрета 1582 года, поэтому программисты решили, что  1900 год (кратный 4) – был високосным. Через несколько лет компания Microsoft выпустила первую версию Excel, в которой фигурировал тот же самый лишний високосный год. Если ввести в Excel Feb 28, 1900  Excel и добавить единицу, получится Feb 29, 1900. Такого дня никогда не было, но он нужен для поддержания совместимости с  Lotus 1-2-3.

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

Почему же так?

В начале статьи, когда у вас ещё не зашёл ум за разум, я обещал объяснить, почему  "1/2"+1 равно 45660, и почему Excel превращает 10:75 в 0.46875.

Первый момент должен быть понятен уже после того, как вы догадались, что Excel трактует 1/2 как второе января 2025 года (год, в котором написана эта статья). Прибавив 1, получаем 3 января 2025 года – и оказывается, что прошло ровно 45 658 дней между этой датой и 1 января 1900 года. Учтём две причины, описанные выше — и получим 45 660, в точности, как получается у Excel. Я не знаю, почему он выводит число вместо даты, но хотя бы понятно, как получилось это число.

Чтобы ответить на второй вопрос, нужно глубоко копнуть, затронув философский подтекст. Что именно показывают часы, кроме как долю времени, истёкшего в сутках? Например, 6:00 – это 0,25 суток, поэтому Excel представляет эту величину как 0.25. По той же логике, 0.46875 должно соответствовать 11:15, то есть, момент через 75 минут после 10:00, своего рода 10:75 – это если не усложнять. Но в глубине души Excel знает, что это глубоко неправильно, поэтому отображает эту величину в виде числа.  

Можно получить даже значения времени свыше 23.59, если подставлять на место часов значения более 23. Если ввести в ячейку 37:30, получим число 1.5625, которое соответствует 13:30  на следующий день. Число 1 – это ровно полночь 1 января 1900 года, а 1.5625 – это 13:30 в день 1 января 1900 года.

Какова ситуация в таблицах Google?

Разработчики Google Sheets выдали блестящую идею и, чтобы избавиться от 29 февраля 1900 года, просто сдвинули два первых месяца 1900 года на единицу. Поэтому 1 января 1900 года соответствует номер 2, а не 1. Это очень толково, пусть начинать с 2 и немного неудобно. Кроме того, в таком случае даты до 1 марта 1900 года при импорте из Excel получаются сдвинуты на единицу.