Почему в экселе 1900 год

Обновлено: 07.07.2024

В статьях [1–2] указывалось, что не всякий год, порядковый номер которого кратен четырем, является високосным — годы, номера которых кратны 100, являются високосными только при условии, что их значения кратны 400. Поэтому 2000 год являлся високосным, а 1900 год — нет, и в нем было 365 дней, как у “обычного” года, а не 366 (в феврале — 28 дней, а не 29). Однако программа Microsoft Excel воспринимает 1900-й как високосный! Если ввести в ячейку дату 29.02.1900 , то Excel распознает такое значение как дату и, как обычно, выровняет его в ячейке по правому краю. Если же ввести другую недопустимую дату, например, 29.02.1901 , то Excel воспримет введенное значение как текстовую строку (выравнивание текста в ячейке по умолчанию происходит по левому краю).

Возникает вопрос: как программный продукт, который ежедневно используют миллионы людей во всем мире, может содержать такую ошибку? Ответ кроется в истории его создания. Исходная версия электронной таблицы Lotus 1-2-3, на которой базировалась программа Excel, содержала дефект, по причине которого 1900 год рассматривался как високосный. Разработчики Excel знали об этой ошибке и позднее, при выпуске приложения, специально перенесли этот дефект в новую программу, чтобы обеспечить его совместимость с рабочими листами файлов Lotus.

Но почему же эта ошибка существует в более поздних версиях Excel? Компания Microsoft утверждает, что недостатки, которые могут возникнуть при исправлении этой ошибки, значительно перевешивают преимущества. Если бы существующая ошибка была устранена, это привело бы в беспорядок сотни тысяч существующих рабочих книг. Кроме того, исправление этой проблемы сказалось бы на совместимости самого приложения Excel с другими программами, использующими данные типа “дата”. Именно поэтому в действительности эта ошибка влечет не так уж много проблем, как ее исправление. К тому же большинство пользователей не использует даты ранее 1 марта 1900 года.

1. Високосные годы. / “В мир информатики” № 17 (“Информатика” № 5/2004).

2. Как узнать номер дня недели? / “В мир информатики” № 114 (“Информатика” № 20/2008).

Возникла следующая проблема:
(условно) в ячейке А1 ввожу дату 01.06.2018
В В1 другой ввожу [code]=МЕСЯЦ(А1).
Если формат Общий, то значение не отображается, если числовой, то отображается 6,00, если ставлю форматы даты, то вместо Июнь отображается Январь.
Около ячейки A1 и В1 в правом нижнем углу появляется значок календарика, если на него кликнуть, то показывает 5 января 1900 года, а низу можно поставить галочку на сегодняшнюю дату.
Работаю в Excel 2016.
Помогите, пожалуйста, как разобраться с параметрами?
Спасибо заранее.

Здравствуйте.
А что нужно-то? Чтобы отобразилось слово Июнь, достаточно использовать для даты Формат ячейки ММММ
Про Пользовательский формат

И оформите формулу тегами с помощью кнопки fx в режиме правки поста

И оформите формулу тегами с помощью кнопки fx в режиме правки поста Pelena

И оформите формулу тегами с помощью кнопки fx в режиме правки поста Автор - Pelena
Дата добавления - 09.04.2018 в 09:02

Задача: Excel хранит дату как число дней, прошедших с 1 января 1900 года. Это означает, что все функции даты не работают для дат XVIII в. и более ранних. Это проблема для историков и генеалогов (людей, занимающихся генеалогией).

Решение: предлагается формула, которая работает с любыми датами с 1 января 1000 г. Она основана на добавлении 1000 лет ко всем датам. Таким образом, допустимая дата, например, 23 января 2009, станет 23 января, 3009. При этом недопустимая дата, например, 17 февраля 1865, станет допустимой – 17 февраля 2865. Поскольку Excel может работать с датами вплоть до 9999 года, то с этой стороны проблем не предвидится.

Введите начальную и конечную даты в ячейки А4 в В4 (рис. 1). Используйте, например, формат 17/02/1865. Если ваша дата после 1900, Excel автоматически преобразует дату в порядковый номер. Если дата до 1900, Excel сохранит ее в виде текста. Я использовал условное форматирование для области А4:В14 на основе формулы =НЕ(ЕТЕКСТ(A4)), которое подкрасило ячейки с датами.

Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете спецформулу

Если ячейка содержит допустимую дату, вы добавляете 1000 лет. Простой способ сделать это – использовать функцию ДАТАМЕС(А4;12000). Эта функция возвращает дату, которая на 12 000 месяцев (1000 лет) отстоит от начальной даты, указанной в первом аргументе функции ДАТАМЕС. Обратите внимание, что эта функция появилась в Excel 2007. В более ранних версиях эта функция доступна после установки надстройки Пакет анализа. Вместо этого можете использовать формулу: =ДАТА(ГОД(А4)+1000;МЕСЯЦ(А4);ДЕНЬ(А4)).

Если ячейка содержит недопустимую дату (до 1900 г.), вам нужно разбить дату в текстовом виде на элементы, добавить 1000 лет к годам, после чего обратно склеить элементы:

Получите левую часть даты (дни, месяцы и разделитель) =ЛЕВСИМВ(A4;ДЛСТР(A4)-4)
Получите год =ПРАВСИМВ(A4;4)
Добавьте 1000 лет к году =ПРАВСИМВ(A4;4)+1000
Сцепите дни, месяцы и год =ЛЕВСИМВ(A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000
Преобразуйте текст, полученный на предыдущем шаге в дату =ДАТАЗНАЧ(ЛЕВСИМВ(A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000)

Не забудьте, что в Англии и колониях переход с юлианского на григорианский календарь привел к тому, что вслед за 2 сентября 1752 года наступило сразу 14 сентября. В России вслед за 31 января 1918 года наступило 14 февраля (подробнее см. Григорианский календарь). Предложенная формула не обрабатывает эти аномалии.

Резюме: несмотря на то, что Excel не умеет напрямую работать с датами до 1900 года, вы можете добавить ко всем датам некое фиксированное число лет (например, 1000), и сделать доступным обработку формулами дат.

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

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

2 ответа

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

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

ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ

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

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

«Другие электронные таблицы» относятся к Lotus 1-2-3 , который был довольно популярен в то время, и неправильно предположил, что в 1900 году был високосный год. Это объясняется еще более подробно в KB 214326 :

Excel 2000 неправильно предполагает, что 1900 год является високосным годом

ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ

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

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

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

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

А?

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

«О, - сказал он мне. «Отъезд 28 февраля 1900 года».

«Это 59», - сказал я.

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

«Это 61!»

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

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

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

К сожалению. Я провел некоторое исследование. Годы, которые делятся на 100, не являются високосные годы, если они также не делятся на 400.

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

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

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

«Итак, это ошибка в Lotus 123?»

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

Читайте также: