Как даты рассчитываются в таблицах Excel
Серийный номер или серийная дата – это число, которое Excel использует для вычисления дат и времени, введенных в лист. Серийный номер рассчитывается либо вручную, либо в результате формул, включающих вычисления даты. Excel считывает системные часы компьютера, чтобы отслеживать количество времени, прошедшего с даты начала системы дат.
Примечание . Информация, приведенная в этом руководстве, относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и Excel для Mac.
Две возможные системы дат
По умолчанию в версиях Excel, работающих в Windows, дата хранится в виде значения, представляющего количество полных дней с полуночи 1 января 1900 года, а также количество часов, минут и секунд для текущего дня.
Версии Excel, работающие на компьютерах Macintosh, по умолчанию используют одну из следующих двух систем дат:
- Excel для Mac версий 2019, 2016 и 2011: системой дат по умолчанию является система дат 1900, которая гарантирует совместимость дат с Excel для Windows.
- Excel 2008 и более ранние версии: система дат по умолчанию начинается с 1 января 1904 года и называется системой дат 1904 года.
Все версии Excel поддерживают обе системы дат, и возможно переключение с одной системы на другую.
Примеры серийных номеров
В системе 1900 серийный номер 1 представляет 1 января 1900 года, 12:00:00 утра, а номер 0 представляет фиктивную дату 0 января 1900 года.
В системе 1904 серийный номер 1 представляет 2 января 1904 г., а номер 0 представляет 1 января 1904 г., 12:00:00 утра.
Время хранится в десятичных числах
Время в обеих системах хранится в виде десятичных чисел от 0,0 до 0,99999, где:
- 0.0 – 00:00:00 (часы: минуты: секунды)
- 0.5 – 12:00:00 (12:00)
- 0,9999 – 23:59:59
Чтобы отобразить даты и время в одной и той же ячейке на листе, объедините целые и десятичные части числа.
Например, в системе 1900 года 12 часов вечера. 1 января 2016 года – серийный номер 42370,5, потому что это 42370 и полдня (время хранится в виде долей полного дня) после 1 января 1900 года. Аналогично, в системе 1904 года число 40908,5 представляет 12 часов вечера. 1 января 2016 г.
Использование серийного номера
Многие проекты, которые используют Excel для хранения данных и расчетов, так или иначе используют даты и время. Например:
- Долгосрочный проект, который подсчитывает количество дней между текущими и прошлыми датами, используя функцию NETWORKDAYS.
- Расчет ссуды, который определяет будущую дату с использованием функции EDATE.
- Таблицы времени, которые рассчитывают истекшее время между временем начала и окончания, а также часы и сверхурочные по мере необходимости, используя формулы, которые добавляют или вычитают даты и время.
- Отметка времени на листе с текущей датой и временем с помощью сочетаний клавиш, которые читают текущий серийный номер.
- Обновление отображаемой даты и времени всякий раз, когда лист открывается или пересчитывается с помощью функций СЕЙЧАС и СЕГОДНЯ.
В одной книге можно использовать только одну систему дат. Если система дат для рабочей книги, содержащей даты, изменяется, эти даты сдвигаются на четыре года и один день из-за разницы во времени между двумя системами дат.
Изменить систему дат по умолчанию
Чтобы установить систему дат для книги в Excel, работающей на ПК с Windows:
-
Откройте рабочую книгу для изменения.
-
Выберите Файл . За исключением Excel 2007, где вы нажимаете кнопку Офис .
-
Выберите Параметры , чтобы открыть диалоговое окно «Параметры Excel».
-
Выберите Дополнительно на левой панели диалогового окна.
-
В разделе “Расчет этой книги” установите или снимите флажок Использовать систему дат 1904 года .
-
Выберите ОК , чтобы закрыть диалоговое окно и вернуться в рабочую книгу.
Чтобы установить систему дат для книги в Excel для Mac:
-
Откройте рабочую книгу для изменения.
-
Выберите меню Excel .
-
Выберите Настройки , чтобы открыть диалоговое окно «Настройки Excel».
-
В разделе “Формулы и список” выберите Расчет.
-
В разделе «При расчете рабочих книг» установите или снимите флажок Использовать систему дат 1904 года .
-
Закройте диалоговое окно «Настройки Excel».
Почему две системы дат?
ПК-версии Excel (операционные системы Windows и DOS) первоначально использовали систему дат 1900 года для совместимости с Lotus 1-2-3, самой популярной программой для работы с электронными таблицами того времени.
Проблема в том, что при создании Lotus 1-2-3 1900 год был запрограммирован как високосный, а на самом деле его не было. В результате для исправления ошибки потребовались дополнительные шаги программирования. Текущие версии Excel сохраняют систему дат 1900 года для совместимости с таблицами, созданными в предыдущих версиях программы.
Поскольку не было версии Lotus 1-2-3 для Macintosh, начальные версии Excel для Macintosh не должны были беспокоиться о проблемах совместимости. Система дат 1904 года была выбрана, чтобы избежать проблем программирования, связанных с проблемой не високосного года 1900 года.
С другой стороны, это создало проблему совместимости между листами, созданными в Excel для Windows и Excel для Mac. Вот почему все новые версии Excel используют систему дат 1900 года.
