Джон отмечает, что при вырезании и вставке дат между книгами кажется, что они меняются без причины. Например, он сделал это с книгой со 100 строками, 3 столбцами дат, и все с 2018 годом. Все вставленные данные имеют 2014 год как год, а дни на одну цифру раньше (17-е становится 16-м и т. Д.).

Подсказка в описании Джона заключается в том, что существует четырехлетняя разница между тем, что он копирует, и тем, что вставлено. Проблема связана с двумя разными «базовыми датами», которые Excel может использовать для расчета дат.

Видите ли, внутри Excel даты хранятся как порядковые номера, которые представляют количество дней от заданной базовой даты. Обычно это 1 января 1900 г., поэтому порядковый номер 1 соответствует этой дате (1 января 1900 г.), 2 — следующему дню, 3 — следующему дню и т. Д.

Базовая дата 1900 г. является значением по умолчанию для версий Excel для Windows. Другая базовая дата — 1 января 1904 года. Исторически это базовая дата, используемая в версиях Excel для Mac. (Я говорю «исторически», потому что это не значение по умолчанию в последних версиях Mac. Неясно, когда именно Microsoft изменила значение по умолчанию.) Microsoft использовала эту вторую систему базовых дат, потому что ранние Mac имели врожденную проблему при работе с датами раньше 1 января 1904 года — что-то, связанное с тем, как поступали с високосными годами, если я правильно помню.

В системе базовых дат 1904 года внутренний порядковый номер 0 — это 1 января 1904 года, 1 — следующий день, а 2 — следующий день. Обратите внимание, что с двумя системами базовых дат даты для любого данного серийного номера отклоняются не только на четыре года, а на четыре года и один день.

Таким образом, в ситуации Джона исходная книга имеет базовую дату в системе 1904 года. В этой книге все в порядке, но когда вы копируете значения (помните, что вы копируете числа — серийные номера — из одной системы в другую) и вставляете их в книгу, которая использует систему 1900, даты будут казаться смещенными на четыре года и один день.

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

  1. Выберите целевую книгу.

  2. Откройте диалоговое окно «Параметры Excel». (В Excel 2007 нажмите кнопку «Office», а затем нажмите «Параметры Excel. В Excel 2010 и более поздних версиях откройте вкладку« Файл »на ленте, а затем нажмите« Параметры ».)

  3. В левой части диалогового окна нажмите «Дополнительно».

  4. Прокрутите вниз, пока не увидите раздел При вычислении этой книги.

(См. Рис. 1.)

  1. Убедитесь, что установлен флажок «Использовать систему дат 1904».

  2. Щелкните ОК.

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

Это подводит нас ко второму возможному решению: скорректировать даты после их вставки в целевую книгу. Если вы посмотрите на базовые серийные номера, которые Excel использует в системе, использующей систему базовой даты 1900, вы увидите, что серийный номер на 01.01.1900 равен 1, а серийный номер на 01.01.1904 — 1463. Таким образом, вы можете вставить даты в целевую книгу, вычесть 1462 из того, что вы только что вставили, и вы получите правильно скорректированные даты для целевой книги.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10942) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.