Даты часто являются важной частью анализа данных. Вы часто задаете себе такие вопросы, как: когда был куплен продукт, сколько времени займет выполнение задачи в проекте или каков средний доход за финансовый квартал? Правильный ввод дат важен для получения точных результатов. Но форматирование дат таким образом, чтобы они были понятными, также важно для обеспечения правильной интерпретации этих результатов.
Важно
Поскольку правила интерпретации дат в любой программе вычислений являются сложными, следует указать даты как можно точнее при их вводе. Это обеспечит высочайший уровень точности вычислений дат.
Подробнее о вычислении и форматировании дат
В Excel даты хранятся в виде порядковых чисел, которые называются числовыми числами. Например, в Excel для Windows дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.
Excel хранит значения времени в виде десятичных дробей, поскольку оно считается частью дня. Десятичное число — это значение в интервале от 0 (ноль) до 0,99999999, представляющее время от 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 года, и поэтому она стала системой дат по умолчанию.
В таблице ниже показаны первая и последняя даты для каждой системы дат, а также численные значения, связанные с каждой датой.
| Система дат | Первое свидание | Последняя дата |
|---|---|---|
| 1900 | 1 января 1900 г. (серийное значение 1) |
31 декабря 9999 г. (серийное значение 2958465) |
| 1904 | 2 января 1904 г. (серийное значение 1) |
31 декабря 9999 г. (серийное значение 2957003) |
Поскольку в этих двух системах дат используются разные начальные дни, одна и та же дата в каждой системе дат представлена различными порядковыми значениями. Например, 5 июля 2007 года может иметь два различных числовых значения в зависимости от используемой системы дат.
| Система дат | Номинал в числовом формате от 5 июля 2007 г. |
|---|---|
| 1900 | 37806 |
| 1904 | 39268 |
Разница между двумя системами дат составляет 1 462 дня; То есть серийное значение даты в системе дат 1900 года всегда на 1462 дня больше, чем номинальное значение той же даты в системе дат 1904 года. Напротив, порядковое значение даты в системе дат 1904 года всегда на 1462 дня меньше, чем в системе дат 1900 года. 1462 дня равны четырем годам и одному дню (включая один високосный день).
Изменение способа интерпретации двузначных чисел года
Важно
Чтобы значения лет интерпретировались нужным образом, введите значения года как четыре цифры (например, 2001, а не 01). При вводе четырехзначных чисел года Excel не будет интерпретировать век за вас.
Если ввести дату с двумя цифрами года в ячейку с текстовым форматом или в качестве текстового аргумента в функции, например =ГОД("01.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.
Windows 10.
- Чтобы получить доступ к дополнительным параметрам, введите панель управления в поле поиска на панели задач, а затем в списке результатов выберите Панель управления.
- В разделе "Часы", "Язык" и "Регион" щелкните "Изменение форматов даты, времени и чисел"
- Щелкните "Региональные и языковые параметры".
- В диалоговом окне " Регион " нажмите кнопку "Дополнительные параметры".
- Откройте вкладку "Дата ".
- В поле Когда вводится двузначный год, интерпретируйте его как год между полями, измените верхнюю границу столетия.
При изменении года верхнего предела автоматически изменяется год нижнего предела. - Нажмите кнопку ОК.
Windows 8
- Проведите пальцем от правого края экрана, нажмите кнопку "Поиск" (если используется мышь, переместите указатель в правый верхний угол экрана, затем вниз и нажмите кнопку "Поиск"), в поле поиска введите "панель управления", а затем выберите пункт "Панель управления".
- В разделе "Часы", "Язык и регион" щелкните "Изменение форматов даты, времени и чисел".
- В диалоговом окне " Регион " нажмите кнопку "Дополнительные параметры".
- Откройте вкладку "Дата ".
- В поле Когда вводится двузначный год, интерпретируйте его как год между полями, измените верхнюю границу столетия.
При изменении года верхнего предела автоматически изменяется год нижнего предела. - Нажмите кнопку ОК.
Windows 7.
- Нажмите кнопку Пуск и выберите пункт Панель управления.
- Щелкните "Регион и язык".
- В диалоговом окне " Регион " нажмите кнопку "Дополнительные параметры".
- Откройте вкладку "Дата ".
- В поле Когда вводится двузначный год, интерпретируйте его как год между полями, измените верхнюю границу столетия.
При изменении года верхнего предела автоматически изменяется год нижнего предела. - Нажмите кнопку ОК.
Изменение формата даты по умолчанию для отображения четырехзначных чисел года
По умолчанию при вводе дат в книгу они форматируются так, чтобы они отображались двузначными числами. Если этим способом выбрать другой формат даты по умолчанию, даты будут отображаться в книге, если они не отформатированы с помощью диалогового окна "Формат ячеек " (на вкладке " Главная " в группе "Число " нажмите кнопку вызова диалогового окна).
Windows 10.
- Чтобы получить доступ к дополнительным параметрам, введите панель управления в поле поиска на панели задач, а затем в списке результатов выберите Панель управления.
- В разделе "Часы", "Язык" и "Регион" щелкните "Изменение форматов даты, времени и чисел"
- Щелкните "Региональные и языковые параметры".
- В диалоговом окне " Регион " нажмите кнопку "Дополнительные параметры".
- Откройте вкладку "Дата ".
- В списке Краткий формат даты выберите формат, в котором год состоит из четырех цифр ("гггг").
- Нажмите кнопку ОК.
Windows 8
- Проведите пальцем от правого края экрана, нажмите кнопку "Поиск" (если используется мышь, переместите указатель в правый верхний угол экрана, затем вниз и нажмите кнопку "Поиск"), в поле поиска введите "панель управления", а затем выберите пункт "Панель управления".
- В разделе "Часы", "Язык и регион" щелкните "Изменение форматов даты, времени и чисел".
- В диалоговом окне " Регион " нажмите кнопку "Дополнительные параметры".
- Откройте вкладку "Дата ".
- В списке Краткий формат даты выберите формат, в котором год состоит из четырех цифр ("гггг").
- Нажмите кнопку ОК.
Windows 7.
- Нажмите кнопку Пуск и выберите пункт Панель управления.
- Щелкните "Регион и язык".
- В диалоговом окне " Регион " нажмите кнопку "Дополнительные параметры".
- Откройте вкладку "Дата ".
- В списке Краткий формат даты выберите формат, в котором год состоит из четырех цифр ("гггг").
- Нажмите кнопку ОК.
Изменение системы дат в Excel
Система дат автоматически изменяется при открытии документа с другой платформы. Например, если вы работаете в Excel и открываете документ, созданный в Excel для Mac, автоматически устанавливается поле проверки системы дат 1904.
Вы можете изменить систему дат, выполнив указанные ниже действия.
- Выберите пункт "Дополнительные параметры> файла".>
- В разделе "При вычислении этой книги" выберите нужную книгу, а затем установите или снимите флажок "Использовать проверку системы дат 1904".
Проблема: у меня проблемы с датами между книгами, в которых используются разные системы дат
Могут возникнуть проблемы при копировании и вставке дат, а также при создании внешних ссылок между книгами на основе двух разных систем дат. Даты могут отображаться на четыре года и один день раньше или позже ожидаемой даты. Эти проблемы могут возникнуть при использовании Excel для Windows, Excel для Mac или обоих.
Например, если скопировать дату 5 июля 2007 г. из книги с системой дат 1900 и вставить ее в книгу с системой дат 1904, будет отображена дата 6 июля 2011 г., то есть на 1462 дня позже. Если же скопировать дату 5 июля 2007 г. из книги с системой дат 1904 и вставить ее в книгу с системой дат 1900, будет отображено как 4 июля 2003 г., то есть на 1462 дня раньше. Дополнительные сведения см. в статье "Системы дат в Excel".
Устранение проблемы копирования и вставки
В пустой ячейке введите значение 1462.
Выделите эту ячейку, а затем на вкладке " Главная " в группе "Буфер обмена " нажмите кнопку "Копировать".
Выделите все ячейки с неправильными датами.
Совет
Чтобы отменить выделение ячеек, щелкните любую ячейку на листе. Дополнительные сведения см. в статье Выделение ячеек, диапазонов, строк и столбцов на листе.
На вкладке " Главная " в группе "Буфер обмена " нажмите кнопку "Вставить", а затем выберите пункт "Специальная вставка".
В диалоговом окне "Специальная вставка " в группе "Вставить" нажмите кнопку "Значения", а затем в группе "Операция" выполните одно из указанных ниже действий.
- Чтобы задать дату через четыре года и на один день позже, нажмите кнопку "Добавить".
- Чтобы задать дату на четыре года и на один день раньше, нажмите кнопку Вычесть.
Исправление проблемы с внешней ссылкой
При использовании внешней ссылки на дату в другой книге с другой системой дат ее можно изменить, выполнив одно из указанных ниже действий.
- Чтобы задать дату через четыре года и на один день, добавьте к ней 1462. Например:
=[Book2]Sheet1!$A$1+1462
- Чтобы установить дату на четыре года и один день раньше, вычтите из нее 1462. Например:
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
См. также
Пользовательское форматирование даты
Отображение чисел в виде значений даты и времени