Даты скопированы неправильно (Microsoft Excel)
Джон отмечает, что при вырезании и вставке дат между книгами кажется, что они меняются без причины. Например, он сделал это с книгой со 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, даты будут казаться смещенными на четыре года и один день.
Есть два возможных решения проблемы. Во-первых, вы можете изменить систему базовых дат, используемую в целевой книге. Вы можете сделать это, выполнив следующие действия:
-
Выберите целевую книгу.
-
Откройте диалоговое окно «Параметры Excel». (В Excel 2007 нажмите кнопку «Office», а затем нажмите «Параметры Excel. В Excel 2010 и более поздних версиях откройте вкладку« Файл »на ленте, а затем нажмите« Параметры ».)
-
В левой части диалогового окна нажмите «Дополнительно».
-
Прокрутите вниз, пока не увидите раздел При вычислении этой книги.
(См. Рис. 1.)
-
Убедитесь, что установлен флажок «Использовать систему дат 1904».
-
Щелкните ОК.
Теперь вы сможете копировать даты из источника в целевую книгу без каких-либо проблем, отмеченных изначально. Однако у этого есть недостаток: у других по-прежнему будет та же проблема, если они захотят скопировать что-либо из целевой книги позже.
Это подводит нас ко второму возможному решению: скорректировать даты после их вставки в целевую книгу. Если вы посмотрите на базовые серийные номера, которые 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.