Почему в экселе 1900 год
Microsoft Excel поддерживает две разные системы дат. Это система дат 1900 и 1904. В этой статье описываются две системы дат и проблемы, которые могут возникнуть при использовании книг, использующих разные системы дат.
Дополнительная информация
При первом выпуске 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 листах и других документах будут уменьшены на один день. Исправление этой смены займет много времени и усилий, особенно в формулах, использующих даты.
- Некоторые функции, такие как функция WEEKDAY, возвращают разные значения. Это может привести к неправильной работе формул на листах.
- Исправление этого поведения приведет к разрыву совместимости последовательной даты между Microsoft Excel и другими программами, использующими даты.
Если поведение остается некорrected, возникает только одна проблема:
- Функция WEEKDAY возвращает неверные значения для дат до 1 марта 1900 г. Так как большинство пользователей не используют даты до 1 марта 1900 г., эта проблема возникает редко.
ПРИМЕЧАНИЕ. Microsoft Excel правильно обрабатывает все остальные високосные годы, включая високосные годы, которые не являются високосными (например, 2100). Неправильно обрабатывается только 1900 год.
Проблемы с связыванием и копированием дат между книгами
Если две книги используют разные системы дат, могут возникнуть проблемы при связывание или копировании дат между книгами. В частности, даты могут быть сдвинуты на четыре года и один день.
Чтобы просмотреть пример такого поведения, выполните следующие действия:
В Excel создайте две новые книги (Book1 и Book2).
Выполните действия, описанные в разделе "Настройка системы дат для книги", чтобы использовать систему дат 1900 в книге 1. Используйте систему дат 1904 в Книге 2.
В book1 введите дату 5 июля 1998 г.
Выберите ячейку, содержащую дату, и нажмите кнопку "Копировать " в меню "Правка ".
Примечание В Excel 2007 выберите ячейку, содержащую дату, и нажмите кнопку "Копировать" в группе буфера обмена на вкладке " Главная".
Переключитесь на Book2, выберите ячейку и щелкните " Вставить " в меню "Правка ".
Примечание В Excel 2007 переключитесь на Book2, выберите ячейку и нажмите кнопку "Вставить" в группе буфера обмена на вкладке " Главная".
Дата вставляется как 6 июля 2002 г. Обратите внимание, что дата на четыре года и на один день позже даты на шаге 3, так как в Book2 используется система дат 1904.
В book2 введите дату 5 июля 1998 г. Выберите ячейку, содержащую дату, и нажмите кнопку "Копировать " в меню "Правка ".
Примечание В Excel 2007 г. введите дату 5 июля 1998 г. в book2. Выделите ячейку, содержащую дату, и нажмите кнопку " Копировать" в группе буфера обмена на вкладке "Главная ".
Переключитесь на Book1, выберите ячейку и щелкните " Вставить " в меню "Правка ".
Примечание В Excel 2007 переключитесь на Book1, выберите ячейку и нажмите кнопку "Вставить" в группе буфера обмена на вкладке " Главная".
Дата вставляется как 4 июля 1994 г. Он был сдвинут на четыре года и один день, так как book1 использует систему дат 1900.
Способ 1. Заранее в настройках
Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл - Параметры - Дополнительно (File - Options - Advanced) :
Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator) .
После этого можно смело импортировать данные - проблем не будет.
More Information
When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.
When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.
Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.
If this behavior were to be corrected, many problems would arise, including:
- Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.
- Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.
- Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.
If the behavior remains uncorrected, only one problem occurs:
- The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.
NOTE: Microsoft Excel correctly handles all other leap years, including century years that are not leap years (for example, 2100). Only the year 1900 is incorrectly handled.
Исправление сдвига дат
В пустой ячейке введите значение 1462.
Выделите ячейку. В меню Правка выберите команду Копировать.
Примечание В Excel 2007 выберите ячейку и нажмите кнопку "Копировать" в группе буфера обмена на вкладке "Главная".
Выделите ячейки, содержащие сдвинутые даты. В меню "Правка " щелкните " Специальная вставка".
Примечание В Excel 2007 выделите ячейки, содержащие сдвинутые даты, щелкните "Вставить" в группе буфера обмена на вкладке "Главная", а затем нажмите кнопку "Специальная вставка ".
В диалоговом окне "Специальная вставка" установите флажок "Значения" в разделе "Вставка ", а затем установите один из следующих флажков в разделе "Операция".
Повторите эти действия, пока не будут исправлены все сдвинутые даты.
Если вы используете формулу для связывания с датой в другой книге и если дата, возвращенная формулой, неправильная, так как книги используют разные системы дат, измените формулу, чтобы она возвращала правильную дату. Например, используйте следующие формулы:
Microsoft Excel incorrectly assumes that the year 1900 is a leap year. This article explains why the year 1900 is treated as a leap year, and outlines the behaviors that may occur if this specific issue is corrected.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Microsoft Excel неправильно предполагает, что 1900 год является високосным. В этой статье объясняется, почему 1900 год рассматривается как високосный год, и описывается поведение, которое может произойти при исправлении этой конкретной проблемы.
Способ 3. Макросом
Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставляем в нашу книгу новый пустой модуль через меню Insert - Module и копируем туда такой код:
Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer - Macros) . Все испорченные числа будут немедленно исправлены.
Разница между системами дат
Так как в двух системах дат используются разные начальные дни, в каждой системе дат одинаковые даты представлены разными серийными номерами. Например, 5 июля 2011 г. может иметь две разные серийные номера:
Система дат
Серийный номер
Разница между двумя системами дат составляет 1462 дня. Это означает, что порядковый номер даты в системе дат 1900 всегда на 1462 дня больше, чем в системе дат 1904. 1462 дня — это четыре года и один день (включая один високосный день).
Система дат 1904 г.
В системе дат 1904 первый поддерживаемый день — 1 января 1904 г. При вводе даты дата преобразуется в серийный номер, представляющий количество затраченных дней с 1 января 1904 г., начиная с 0 для 1 января 1904 г. Например, если ввести 5 июля 1998 г., Excel преобразует дату в серийный номер 34519.
Из-за проектирования ранних компьютеров Macintosh даты до 1 января 1904 г. не поддерживались. Эта структура была предназначена для предотвращения проблем, связанных с тем, что 1900 не был високосным годом. В прошлом для Excel Macintosh по умолчанию используется система дат 1904 для книг, созданных на компьютере Macintosh. Однако Excel macintosh теперь по умолчанию используется система дат 1900 и поддерживает даты уже 1 января 1900 г.
Ссылки
Дополнительные сведения о том, является ли данный год високосным, см. в разделе "Метод определения того, является ли год високосным годом".
При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема - дробные числа превращаются в даты:
Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью - точка), а в Excel у вас российские настройки (где между целой и дробной частью - запятая, а точка используется как разделитель в дате).
При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике - если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату - оно будет конвертировано в дату. Всё, что на дату не похоже - останется текстом.
Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:
- В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
- В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года - самая ранняя дата, с которой может работать Excel - 1 января 1900 г.
- А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:
Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel - количество дней от 01.01.1900 до текущей даты:
Лечится вся эта история тремя принципиально разными способами.
References
For more information about determining whether a given year is a leap year, see Method to Determine Whether a Year Is a Leap Year.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Excel поддерживает две системы дат: систему дат 1900 и систему дат 1904. В каждой системе дат используется уникальная начальная дата, с которой вычисляются все остальные даты книги. Все версии Excel для Windows вычисляют даты на основе системы дат 1900. Excel 2008 для Mac и более ранних Excel для Mac вычисляют даты на основе системы дат 1904. Excel 2016 для Mac и Excel для Mac 2011 используют систему дат 1900, которая гарантирует совместимость с Excel для Windows.
Примечание: Даты, скопированные в составе диаграммы, нельзя преобразовать из системы дат 1904 г. и могут различаться примерно на четыре года. Эти даты необходимо преобразовать вручную.
Система дат 1900 г.
В системе дат 1900 года первый поддерживаемый день — 1 января 1900 г. При вводе даты дата преобразуется в серийный номер, представляющий количество затраченных дней, начиная с 1 на 1 января 1900 г. Например, если ввести 5 июля 1998 г., Excel преобразует дату в серийный номер 35981.
По умолчанию Microsoft Excel для Windows использует систему дат 1900. Система дат 1900 обеспечивает лучшую совместимость между Excel и другими программами электронной таблицы, такими как Lotus 1-2-3, которые предназначены для работы в MS-DOS или Microsoft Windows.
Система дат 1904 г.
В системе дат 1904 даты вычисляются с использованием 1 января 1904 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1904 г. Например, если ввести 5 июля 2011 г., Excel преобразует дату в число 39267. Это стандартная система дат в более ранних версиях Excel для Mac. Если вы решили не преобразовывать данные и сохранить систему дат 1904 г., то вкопированные даты будут отличаться от дат, которые вы скопировали.
Изменение системы дат по умолчанию
Выберите Файл > Параметры.
Выберите пункт Дополнительно.
В области При вычислении этой книгивыберите или clear Use 1904 date system.
В меню Excel выберите пункт Параметры.
В списке Формулы и спискивыберите вычисление .
Выберите или сберем его, чтобы использовать систему дат 1904.
Система дат 1900
В системе дат 1900 даты вычисляются с использованием 1 января 1900 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1900 г. Например, если ввести 5 июля 2011 г., Excel преобразует дату в число 40729. Это стандартная система дат в Excel для Windows, Excel 2016 для Mac и Excel для Mac 2011. Если вы решите преобразовать вкопированные данные, Excel скорректирует значения, а вкопированные даты будут соответствовать скопированные даты.
Разница между системами дат
Поскольку в двух системах дат используются разные начальные дни, в каждой системе дат одинаковые даты представлены разными серийными номерами. Например, 5 июля 1998 г. могут иметь два разных серийных номера, как показано ниже.
Система дат | Серийный номер от 5 июля 1998 г. |
---|---|
система дат 1900 | 35981 |
Система дат 1904 | 34519 |
Разница между двумя системами дат — 1462 дня; то есть серийный номер даты в системе дат 1900 всегда на 1462 дня больше, чем серийный номер той же даты в системе дат 1904 года. 1462 дня — это четыре года и один день (включая один високосный день).
Настройка системы дат для книги
В Excel каждая книга может иметь собственные системные параметры дат, даже если открыто несколько книг.
Чтобы задать систему дат для книги в Microsoft Office Excel 2003 и более ранних версиях Excel, выполните следующие действия.
Чтобы задать систему дат для книги в Microsoft Office Excel 2007 г., выполните следующие действия:
Дополнительная информация
Способ 2. Формулой
Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:
=--ЕСЛИ( ЯЧЕЙКА("формат";A1)="G" ; ПОДСТАВИТЬ(A1;".";",") ; ТЕКСТ(A1;"М,ГГГГ") )
В английской версии это будет:
=--IF (CELL ("format ";A1)="G"; SUBSTITUTE (A1;".";","); TEXT (A1;"M ,YYYY "))
Логика здесь простая:
- Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата "G" для текста/чисел или "D3" для дат.
- Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) .
- Если в исходной ячейке дата, то выводим её в формате "номер месяца - запятая - номер года" с помощью функции ТЕКСТ (TEXT) .
- Чтобы преобразовать получившееся текстовое значение в полноценное число - выполняем бессмысленную математическую операцию - добавляем два знака минус перед формулой, имитируя двойное умножение на -1.
Читайте также: