☰

Почему в Excel неверные дни недели для 1900 года?

tag-icon • tag-icon microsoft-excel date
Почему в Excel неверные дни недели для 1900 года?

Этот вопрос основан на наблюденияхАдамВвего ответнаКак вставить название дня в ячейку Excel?

Если A1 имеет значение 2009-08-01, то:

  • =WEEKDAY(A1)получит7
  • =TEXT(7, "dddd")получитSaturday
  • =TEXT(7,"dddd, yyyy-mm-dd")получитSaturday, 1900-01-07
  • =TEXT(1,"dddd, yyyy-mm-dd")получитSunday, 1900-01-01
  • =TEXT("1900-01-01","dddd, yyyy-mm-dd")также получитSunday, 1900-01-01

Последние два неверны: 1 января 1900 года на самом деле понедельник.
Различные источники, похоже, подтверждают это:

Что я упускаю? Почему Excel делает это неправильно?

решение1

Как описано в MicrosoftКБ 214058:

Дни недели до 1 марта 1900 года указаны неверно в Excel

БОЛЬШЕ ИНФОРМАЦИИ

Когда система дат в Microsoft Excel была изначально создана, она была разработана с учетом полной совместимости с системами дат, используемыми в других программах для работы с электронными таблицами.

Однако в этой системе дат 1900 год неправильно интерпретируется как високосный. Поскольку в 1900 году нет 29 февраля («високосного дня»), день недели для любой даты до 1 марта 1900 года (дня после «високосного дня») вычисляется неправильно.

Под «другими программами для работы с электронными таблицами» подразумеваютсяЛотос 1-2-3, который был довольно популярен в то время, и ошибочно предполагал, что 1900 год был високосным. Это объясняется еще более подробно вКБ 214326:

Excel 2000 ошибочно предполагает, что 1900 год — високосный

БОЛЬШЕ ИНФОРМАЦИИ

Когда Lotus 1-2-3 был впервые выпущен, программа предполагала, что 1900 год был високосным, хотя на самом деле он не был високосным. Это облегчило программе обработку високосных лет и не нанесло никакого вреда почти всем расчетам дат в Lotus 1-2-3.

Когда были выпущены Microsoft Multiplan и Microsoft Excel, они также предполагали, что 1900 год был високосным. Это предположение позволило Microsoft Multiplan и Microsoft Excel использовать ту же систему серийных дат, что и Lotus 1-2-3, и обеспечить большую совместимость с Lotus 1-2-3. Рассмотрение 1900 года как високосного года также упростило для пользователей перемещение рабочих листов из одной программы в другую.

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

Если бы это поведение было исправлено, возникло бы много проблем, включая следующие:

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

Если поведение не будет исправлено, возникнет только одна проблема:

  • Функция WEEKDAY возвращает неверные значения для дат до 1 марта 1900 года. Поскольку большинство пользователей не используют даты до 1 марта 1900 года, эта проблема встречается редко.

решение2

Вот как сам Джоэл объясняет причину:Мой первый обзор BillG

В Basic в качестве эпохи используется 31 декабря 1899 года вместо 1 января 1900 года, но по какой-то причине сегодняшняя дата в Excel оказалась такой же, как и в Basic.

Хм?

Я пошел искать разработчика Excel, который был достаточно стар, чтобы помнить, почему. Эд Фрайс, похоже, знал ответ.

«О, — сказал он мне. — Проверь 28 февраля 1900 года».

«59», — сказал я.

«А теперь попробуйте 1 марта».

«61!»

«Что случилось с 60?» — спросил Эд.

«29 февраля 1900 года был високосным годом! Он делится на 4!»

«Хорошая догадка, но сигары нет», — сказал Эд, и я на некоторое время задумался.

Упс. Я провел небольшое исследование. Годы, делящиеся на 100, не являются високосными, если только они не делятся на 400.

1900 год не был високосным.

«Это ошибка в Excel!» — воскликнул я.

«Ну, не совсем», — сказал Эд. «Нам пришлось сделать это таким образом, потому что нам нужно было импортировать рабочие листы Lotus 123».

«Значит, это ошибка в Lotus 123?»

«Да, но, вероятно, это сделано намеренно. Lotus пришлось втиснуться в 640 Кб. Это не так уж много памяти. Если не брать в расчет 1900 год, то можно определить, является ли данный год високосным, просто посмотрев, равны ли два крайних правых бита нулю. Это действительно быстро и просто. Вероятно, ребята из Lotus решили, что не имеет значения, что они ошиблись в этих двух месяцах в далеком прошлом.

решение3

Одним из решений этой проблемы является добавление к году 400 лет, чтобы вычислить день недели по следующей формуле:

=WEEKDAY(DATE(A4+400,B4,C4),1)

Итак, следующие переменные

A4 = 1834
B4 = 12
C4 = 14

вернется 1(воскресенье), что совпадает с 14 декабря 2234 года.
Это перестает работать для дат до 1753, года после перехода на григорианский календарь.

Связанный контент