Использовать систему дат 1904 что это

Microsoft Excel

трюки • приёмы • решения

Как в Excel работать с отрицательными значениями времени

Рис. 94.1. Ячейка В8 показывает оставшееся время 40-часовой рабочей недели

Если количество отработанных часов превышает 40, то формула в ячейке В9 возвращает отрицательное число. Но, как вы можете видеть на рис. 94.2, Excel не может справиться с отрицательным значением времени.

Рис. 94.2. Excel отображает ошибку для отрицательных значений времени

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

Одно из решений заключается в применении дополнительной системы дат 1904, которая задает серийный номер 1 дате 2 января 1904 года. Выберите Файл ► Параметры, чтобы открыть окно Параметры Excel. Перейдите в раздел Дополнительно и в области При пересчете этой книги установите флажок Использовать систему дат 1904. Отрицательные значения времени сейчас будут выводиться правильно, как показано на рис. 94.3.

Рис. 94.3. Переход на систему дат 1904 приведет к тому, что отрицательные значения времени будут корректно отображаться

Будьте осторожны, если ваша книга содержит ссылки на другие книги, которые не используют систему дат 1904. В таком случае несоответствия систем дат могут привести к ошибочным результатам.

Источник

Изменение системы дат, формата даты и двузначного представления года

Даты часто являются важной частью анализа данных. Вы часто задаете такие вопросы, как: когда был приобретен продукт, сколько времени займет задача в проекте или каков средний доход за финансовый квартал? Правильное ввод дат важно для обеспечения точных результатов. Но форматирование дат для их понимания не менее важно для обеспечения правильной интерпретации результатов.

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

Excel даты в качестве последовательных чисел, которые называются серийными значениями. Например, в Excel для Windows 1 января 1900 г. является число 1, а 1 января 2008 г. — число 39448, поскольку после 1 января 1900 г. это 39 448 дней.

Excel время в десятичных дробях, так как время считается частью дня. Десятичная цифра — это значение в диапазоне от 0 до 0,999999999, представляющее время от 0:00:00 до 23:59:59.

Так как даты и время являются значениями, их можно добавлять, вычитать и включать в другие вычисления. Вы можете просмотреть дату как порядковый номер, а время — как дробную часть, изменив формат ячейки с датой или временем на Общий.

Обе Excel для Mac и Excel для Windows поддерживают системы дат 1900 и 1904. Система дат по умолчанию для Excel для Windows — 1900; и система дат по умолчанию для Excel для Mac — 1904.

Изначально Excel для Windows была основана на системе дат 1900, так как она стала лучшей совместимостью с другими программами электронных таблиц, которые запускались в ms-DOS и Microsoft Windows, поэтому она стала системой дат по умолчанию. Изначально система Excel для Mac была основана на системе дат 1904 г., так как она была лучшей совместимости с компьютерами macintosh, которые не поддерживают даты до 2 января 1904 г., поэтому она стала системой дат по умолчанию.

В следующей таблице показаны первая и последняя даты для каждой системы дат и порядковый номер, связанный с каждой датой.

Дата последней встречи

1 января 1900 г.
(порядковый номер 1)

31 декабря 9999 г.
(порядковый номер 2958465)

2 января 1904 г.
(порядковый номер 1)

31 декабря 9999 г.
(порядковый номер 2957003)

Так как в двух системах дат используются разные начальные дни, в каждой системе дат одинаковые даты представлены разными серийными значениями. Например, 5 июля 2007 г. может иметь два разных серийных значения в зависимости от используемой системы дат.

Порядковый номер от 5 июля 2007 г.

Разница между двумя системами дат составляет 1462 дня; то есть порядковый номер даты в системе дат 1900 всегда на 1462 дня больше, чем в системе дат 1904. И наоборот, порядковый номер даты в системе дат 1904 года всегда на 1462 дня меньше, чем в системе дат 1900. 1462 дня — это четыре года и один день (включая високосный день).

Важно: Чтобы обеспечить интерпретацию значений года как нужно, введите значения года как четыре цифры (например, 2001, а не 01). Вводя четырехзначные годы, Excel не будет интерпретировать век.

Если ввести дату с двузначным номером года в текстовую ячейку или в качестве текстового аргумента функции, например =ГОД(“1.01.31”), Excel интерпретирует год следующим образом:

00-29 интерпретируется как годы с 2000 по 2029 год. Например, если ввести дату 28.05.2019,Excel предполагается, что это 28 мая 2019 г.

30-99 интерпретируется как годы с 1930 по 1999. Например, если ввести дату 28.05.98,Excel предполагается, что это 28 мая 1998 г.

В Microsoft Windows вы можете изменить способ интерпретации двухзначного года для всех установленных Windows программ.

В области Часы, язык и регионщелкните Изменить форматы даты, времени или числа

Выберите язык и региональные параметры.

В диалоговом окне Регион нажмите кнопку Дополнительные параметры.

Перейдите на вкладку Дата.

В поле При входе двухзначного года интерпретировать его как год между, измените верхний предел для года.

Когда вы изменяете верхний предел года, он автоматически изменяется.

Проведите пальцем от правого края экрана к краю экрана, коснитесь пункта Поиск (или, если вы используете мышь, наведите указатель на правый верхний угол экрана, переместите указатель мыши вниз, а затем нажмите Поиск ),введите Панель управления в поле поиска, а затем нажмите или щелкните Панель управления.

В области Часы, язык и регионщелкните Изменить форматы даты, времени или числа.

В диалоговом окне Регион нажмите кнопку Дополнительные параметры.

Перейдите на вкладку Дата.

В поле При входе двухзначного года интерпретировать его как год между, измените верхний предел для года.

Когда вы изменяете верхний предел года, он автоматически изменяется.

Нажмите кнопку Пуск и выберите пункт Панель управления.

Щелкните Язык и регион.

В диалоговом окне Регион нажмите кнопку Дополнительные параметры.

Перейдите на вкладку Дата.

В поле При входе двухзначного года интерпретировать его как год между, измените верхний предел для года.

Когда вы изменяете верхний предел года, он автоматически изменяется.

По умолчанию при вводе дат в книгу даты форматируется для отображения двухзначных лет. При изменении формата даты по умолчанию с помощью этой процедуры отображение дат, которые были ранее введены в книгу, изменится на новый, если даты не отформатированы с помощью диалогового окна Формат ячеек (на вкладке Главная в группе Число нажмите кнопку запуска диалогового окна ).

В области Часы, язык и регионщелкните Изменить форматы даты, времени или числа

Выберите язык и региональные параметры.

В диалоговом окне Регион нажмите кнопку Дополнительные параметры.

Перейдите на вкладку Дата.

В списке Краткий формат даты выберите формат, использующий четыре цифры для года (“yyyy”).

Проведите пальцем от правого края экрана к краю экрана, коснитесь пункта Поиск (или, если вы используете мышь, наведите указатель на правый верхний угол экрана, переместите указатель мыши вниз, а затем нажмите Поиск ),введите Панель управления в поле поиска, а затем нажмите или щелкните Панель управления.

В области Часы, язык и регионщелкните Изменить форматы даты, времени или числа.

В диалоговом окне Регион нажмите кнопку Дополнительные параметры.

Перейдите на вкладку Дата.

В списке Краткий формат даты выберите формат, использующий четыре цифры для года (“yyyy”).

Нажмите кнопку Пуск и выберите пункт Панель управления.

Щелкните Язык и регион.

В диалоговом окне Регион нажмите кнопку Дополнительные параметры.

Перейдите на вкладку Дата.

В списке Краткий формат даты выберите формат, использующий четыре цифры для года (“yyyy”).

Система дат автоматически изменяется при открытом документе с другой платформы. Например, если вы работаете в Excel и открываете документ, созданный в Excel для Mac, то автоматически будет выбрана система дат 1904.

Вы можете изменить систему дат следующим образом:

Щелкните Файл > Параметры > Дополнительно.

В разделе При вычислении данной книги выберите книгу, а затем выберите или сберем из нее поле Система дат 1904.

Могут возникнуть проблемы при копировании и вклеии дат или создании внешних ссылок между книгами на основе двух разных систем дат. Даты могут отображаться на четыре года и на один день раньше или позже нужной даты. Эти проблемы могут возникнуть при использовании Excel для Windows, Excel для Mac или обоих этих методов.

Например, если скопировать дату 5 июля 2007 г. из книги, использующей систему дат 1900, а затем вкопировать ее в книгу, в которой используется система дат 1904, она будет отсвеяна 6 июля 2011 г., то есть через 1 462 дня. Кроме того, если вы скопируете дату 5 июля 2007 г. из книги, использующей систему дат 1904, а затем вкопируете ее в книгу, в которой используется система дат 1900, она будет даты 4 июля 2003 г., которая на 1 462 дня раньше. Сведения о фоновом режиме см.в Excel.

Исправление проблемы с копированием и вской

В пустой ячейке введите значение 1462.

Выйдите из этой ячейки, а затем на вкладке Главная в группе Буфер обмена нажмите кнопку Копировать.

Выберем все ячейки, содержащие неправильные даты.

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе. Дополнительные сведения см. в статье Выбор ячеек, диапазонов, строк и столбцов на сайте.

На вкладке Главная в группе Буфер обмена нажмите кнопку Вироватьи выберите специальная вкладки.

Чтобы сделать дату четырехлетней и на один день позже, нажмите кнопку Добавить.

Чтобы сделать дату на четыре года раньше, нажмите кнопку Вычесть.

Устранение проблемы с внешними ссылками

Если вы используете внешнюю ссылку на дату в другой книге с другой системой дат, вы можете изменить внешнюю ссылку одним из следующих способов:

Чтобы сделать дату четырехлетней и на один день позже, добавьте к ней 1462. Например:

Чтобы сделать дату на четыре года раньше, вычесть из нее 1462. Например:

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Системы дат в Excel

Excel поддерживает две системы дат: систему дат 1900 и систему дат 1904. В каждой системе дат используется уникальная начальная дата, с которой вычисляются все остальные даты книги. Все версии Excel для Windows вычисляют даты на основе системы дат 1900. Excel 2008 для Mac и более ранних Excel для Mac вычисляют даты на основе системы дат 1904. Excel 2016 для Mac и Excel для Mac 2011 используют систему дат 1900, которая гарантирует совместимость с Excel для Windows.

При копировании дат из книги, созданной в более ранней версии, в книгу, созданную в Excel 2016 или 2011 для Mac, они будут преобразовываться автоматически, если в параметрах > > Параметры даты не будет отключен параметр “Автоматически преобразовывать систему дат”. Если этот параметр отключен, вы получите сообщение с вопросом, следует ли преобразовывать даты при вклеии. У вас есть два варианта действий. Вы можете преобразовать даты, чтобы использовать систему дат 1900 (рекомендуется). Этот параметр делает даты совместимыми с другими датами в книге. Вы также можете сохранить систему дат 1904 только для вклеиных дат.

Примечание: Даты, скопированные в составе диаграммы, нельзя преобразовать из системы дат 1904 г. и могут различаться примерно на четыре года. Эти даты необходимо преобразовать вручную.

Система дат 1900

В системе дат 1900 даты вычисляются с использованием 1 января 1900 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1900 г. Например, если ввести 5 июля 2011 г., Excel преобразует дату в число 40729. Это стандартная система дат в Excel для Windows, Excel 2016 для Mac и Excel для Mac 2011. Если вы решите преобразовать вкопированные данные, Excel скорректирует значения, а вкопированные даты будут соответствовать скопированные даты.

Система дат 1904 г.

В системе дат 1904 даты вычисляются с использованием 1 января 1904 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1904 г. Например, если ввести 5 июля 2011 г., Excel преобразует дату в число 39267. Это стандартная система дат в более ранних версиях Excel для Mac. Если вы решили не преобразовывать данные и сохранить систему дат 1904 г., то вкопированные даты будут отличаться от дат, которые вы скопировали.

Разница между системами дат

Так как в двух системах дат используются разные начальные дни, в каждой системе дат одинаковые даты представлены разными серийными номерами. Например, 5 июля 2011 г. может иметь две разные серийные номера:

Разница между двумя системами дат составляет 1462 дня. Это означает, что порядковый номер даты в системе дат 1900 всегда на 1462 дня больше, чем в системе дат 1904. 1462 дня — это четыре года и один день (включая один високосный день).

Изменение системы дат по умолчанию

Выберите Файл > Параметры.

(В Excel 2007 нажмите кнопку Microsoft Office кнопку и выберите Excel параметры.)

Выберите пункт Дополнительно.

В области При вычислении этой книгивыберите или clear Use 1904 date system.

В меню Excel выберите пункт Параметры.

В списке Формулы и спискивыберите вычисление .

Выберите или сберем его, чтобы использовать систему дат 1904.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Различия между системой дат 1900 и 1904 годов в Excel

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Сводка

Microsoft Excel поддерживает две разные системы дат. Эти системы являются системой дат 1900 и системой дат 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 г., начиная с 0 на 1 января 1904 г. Например, если вы вводите 5 июля 1998 г., Excel преобразует дату в серийный номер 34519.

Из-за разработки ранних компьютеров Macintosh даты до 1 января 1904 г. не поддерживались. Эта конструкция была предназначена для предотвращения проблем, связанных с тем, что 1900 год не был високосным. В прошлом Excel Macintosh по умолчанию использовал систему дат 1904 года для книг, происходящих на Macintosh. Однако Excel Macintosh теперь по умолчанию относится к системе дат 1900 и поддерживает даты уже 1 января 1900 г.

Дополнительные сведения см. в Excel, что 1900год — это високосный год.

Разница между системами дат

Поскольку в двух системах дат используются разные начальные дни, эта же дата представлена разными серийными номерами в каждой системе дат. Например, 5 июля 1998 года могут иметь два разных серийных номера, как по этому.

Разница между двумя системами дат составляет 1462 дня; то есть серийный номер даты в системе дат 1900 года всегда на 1462 дня больше, чем серийный номер той же даты в системе дат 1904 года. 1462 дня равно четырем годам и одному дню (включая один високосный день).

Настройка системы дат для книги

В Excel каждая книга может иметь свои собственные параметры системы дат, даже если несколько книг открыты.

Чтобы установить систему дат для книги в Microsoft Office Excel 2003 г. и в более ранних версиях Excel, выполните следующие действия:

Чтобы установить систему дат для книги в Microsoft Office Excel 2007 г., выполните следующие действия:

Обратите внимание, что при изменении системы дат для книги, которая уже содержит даты, даты сдвига на четыре года и один день. Сведения о том, как исправить перенесенные даты, см. в разделе “Исправление перенесенных дат”.

Проблемы, связанные с привязкой и копированием дат между книгами

Если в двух книгах используются различные системы дат, при ссылке или копировании дат между книгами могут возникнуть проблемы. В частности, даты могут быть перенесены на четыре года и один день.

Чтобы увидеть пример такого поведения, выполните следующие действия:

В Excel создайте две новые книги (Book1 и Book2).

Выполните действия в разделе “Настройка системы дат для книги”, чтобы использовать систему дат 1900 в книге 1. Используйте систему дат 1904 года в книге 2.

В Книге1 введите дату 5 июля 1998 г.

Выберите ячейку, содержаную дату, и нажмите кнопку Скопируйте в меню Редактирование.

Примечание В Excel 2007 г. выберите ячейку, содержаную дату, и нажмите кнопку Скопируйте в группе Clipboard на вкладке Главная.

Перейдите на Book2, выберите ячейку и нажмите кнопку Вайт в меню Изменить.

Примечание В Excel 2007 г. перейдите на Book2, выберите ячейку, а затем щелкните вклейку в группу Clipboard на вкладке Home.

Дата вклейки 6 июля 2002 г. Обратите внимание, что дата на четыре года и один день позже даты на шаге 3, так как в Книге 2 используется система дат 1904 года.

В книге 2 введите дату 5 июля 1998 г. Выберите ячейку, содержаную дату, и нажмите кнопку Скопируйте в меню Редактирование.

Примечание В Excel 2007 г. введите дату 5 июля 1998 г. в книге 2. Выберите ячейку, содержаную дату, и нажмите кнопку Скопируйте в группе Clipboard на вкладке Главная.

Перейдите на Book1, выберите ячейку и нажмите кнопку Вайт в меню Изменить.

Примечание В Excel 2007 г. перейдите на Book1, выберите ячейку, а затем щелкните вклейку в группу Clipboard на вкладке Главная.

Дата вклейки — 4 июля 1994 г. Она была перенесена на четыре года и один день, так как Book1 использует систему дат 1900.

Исправление перенесенных дат

Если вы связываете даты между книгами или копируете их, или измените систему дат для книги, которая уже содержит даты, даты могут быть перенесены на четыре года и один день. Вы можете исправить перенесенные даты, следуя следующим шагам:

В пустой ячейке введите значение 1462.

Выберите ячейку. В меню Правка выберите команду Копировать.

Примечание В Excel 2007 г. выберите ячейку и нажмите кнопку Скопируйте в группе Clipboard на вкладке Главная.

Выберите ячейки, содержащие перенесенные даты. В меню Редактирование нажмите кнопку Paste Special.

Примечание В Excel 2007 г. выберите ячейки, содержащие перенесенные даты, щелкните вклейку в группу Clipboard на вкладке Главная, а затем нажмите кнопку Paste Special.

В специальном диалоговом окне Вставки выберите флажок Значения под вставки, а затем выберите один из следующих флажков в операции.

Нажмите кнопку OK.

Повторите эти действия, пока не будут исправлены все перенесенные даты.

Если вы используете формулу для ссылки на дату в другой книге, и если дата, возвращенная формулой, неправильная, так как в книгах используются различные системы дат, измените формулу, чтобы вернуть правильную дату. Например, используйте следующие формулы:

В этих формулах добавляется или удаляется 1462 из значения даты.

Источник

Differences between the 1900 and the 1904 date system in Excel

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Summary

Microsoft Excel supports two different date systems. These systems are the 1900 date system and the 1904 date system. This article describes the two date systems and the problems that you may encounter when you use workbooks that use different date systems.

More Information

The 1900 Date System

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days starting with 1 for January 1, 1900. For example, if you enter July 5, 1998, Excel converts the date to the serial number 35981.

By default, Microsoft Excel for Windows uses the 1900 date system. The 1900 date system enables better compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.

The 1904 Date System

In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904, starting with 0 for January 1, 1904. For example, if you enter July 5, 1998, Excel converts the date to the serial number 34519.

Because of the design of early Macintosh computers, dates before January 1, 1904, were not supported. This design was intended to prevent problems related to the fact that 1900 was not a leap year. In the past, Excel for Macintosh defaulted to using the 1904 date system for workbooks originating on a Macintosh. However, Excel for Macintosh now defaults to the 1900 date system and supports dates as early as January 1, 1900.

The Difference Between the Date Systems

Because the two date systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, 1998 can have two different serial numbers, as follows.

The difference between the two date systems is 1,462 days; that is, the serial number of a date in the 1900 Date System is always 1,462 days bigger than the serial number of the same date in the 1904 date system. 1,462 days is equal to four years and one day (including one leap day).

Setting the Date System for a Workbook

In Excel, each workbook can have its own date system setting, even if multiple workbooks are open.

To set the date system for a workbook in Microsoft Office Excel 2003 and in earlier versions of Excel, follow these steps:

To set the date system for a workbook in Microsoft Office Excel 2007, follow these steps:

Notice that if you change the date system for a workbook that already contains dates, the dates shift by four years and one day. For information about how to correct shifted dates, see the “Correcting Shifted Dates” section.

Problems Linking and Copying Dates Between Workbooks

If two workbooks use different date systems, you may encounter problems when you link or copy dates between workbooks. Specifically, the dates may be shifted by four years and one day.

To see an example of this behavior, follow these steps:

In Excel, create two new workbooks (Book1 and Book2).

Follow the steps in the “Setting the Date System for a Workbook” section to use the 1900 date system in Book 1. Use the 1904 date system in Book2.

In Book1, enter the date July 5, 1998.

Select the cell that contains the date, and then click Copy on the Edit menu.

Note In Excel 2007, select the cell that contains the date, and then click Copy in the Clipboard group on the Home tab.

Switch to Book2, select a cell, and then click Paste on the Edit menu.

Note In Excel 2007, switch to Book2, select a cell, and then click Paste in the Clipboard group on the Home tab.

The date is pasted as July 6, 2002. Notice that the date is four years and one day later than the date in step 3 because Book2 uses the 1904 date system.

In Book2, type the date July 5, 1998. Select the cell that contains the date and then click Copy on the Edit menu.

Note In Excel 2007, type the date July 5, 1998 in Book2. Select the cell that contains the date, and then click Copy in the Clipboard group on the Home tab.

Switch to Book1, select a cell, and then click Paste on the Edit menu.

Note In Excel 2007, switch to Book1, select a cell, and then click Paste in the Clipboard group on the Home tab.

The date is pasted as July 4, 1994. It has been shifted down by four years and one day because Book1 uses the 1900 date system.

Correcting Shifted Dates

If you link from or copy dates between workbooks, or if you change the date system for a workbook that already contains dates, the dates may be shifted by four years and one day. You can correct shifted dates by following these steps:

In an empty cell, enter the value 1462.

Select the cell. On the Edit menu, click Copy.

Note In Excel 2007, select the cell, and then click Copy in the Clipboard group on the Home tab.

Select the cells that contain the shifted dates. On the Edit menu, click Paste Special.

Note In Excel 2007, select the cells that contain the shifted dates, click Paste in the Clipboard group on the Home tab, and then click Paste Special.

In the Paste Special dialog box, click to select the Values check box under Paste, and then click to select either of the following check boxes under Operation.

Click OK.

Repeat these steps until all the shifted dates have been corrected.

If you are using a formula to link to a date in another workbook, and if the date returned by the formula is incorrect because the workbooks use different date systems, modify the formula to return the correct date. For example, use the following formulas:

In these formulas, 1,462 is added or deleted from the date value.

Источник