Как убрать 1900 год в экселе

Обновлено: 04.07.2024

Задача: Excel хранит дату как число дней, прошедших с 1 января 1900 года. Это означает, что все функции даты не работают для дат XVIII в. и более ранних. Это проблема для историков и генеалогов (людей, занимающихся генеалогией).

Решение: предлагается формула, которая работает с любыми датами с 1 января 1000 г. Она основана на добавлении 1000 лет ко всем датам. Таким образом, допустимая дата, например, 23 января 2009, станет 23 января, 3009. При этом недопустимая дата, например, 17 февраля 1865, станет допустимой – 17 февраля 2865. Поскольку Excel может работать с датами вплоть до 9999 года, то с этой стороны проблем не предвидится.

Введите начальную и конечную даты в ячейки А4 в В4 (рис. 1). Используйте, например, формат 17/02/1865. Если ваша дата после 1900, Excel автоматически преобразует дату в порядковый номер. Если дата до 1900, Excel сохранит ее в виде текста. Я использовал условное форматирование для области А4:В14 на основе формулы =НЕ(ЕТЕКСТ(A4)), которое подкрасило ячейки с датами.

Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете спецформулу

Если ячейка содержит допустимую дату, вы добавляете 1000 лет. Простой способ сделать это – использовать функцию ДАТАМЕС(А4;12000). Эта функция возвращает дату, которая на 12 000 месяцев (1000 лет) отстоит от начальной даты, указанной в первом аргументе функции ДАТАМЕС. Обратите внимание, что эта функция появилась в Excel 2007. В более ранних версиях эта функция доступна после установки надстройки Пакет анализа. Вместо этого можете использовать формулу: =ДАТА(ГОД(А4)+1000;МЕСЯЦ(А4);ДЕНЬ(А4)).

Если ячейка содержит недопустимую дату (до 1900 г.), вам нужно разбить дату в текстовом виде на элементы, добавить 1000 лет к годам, после чего обратно склеить элементы:

Получите левую часть даты (дни, месяцы и разделитель) =ЛЕВСИМВ(A4;ДЛСТР(A4)-4)
Получите год =ПРАВСИМВ(A4;4)
Добавьте 1000 лет к году =ПРАВСИМВ(A4;4)+1000
Сцепите дни, месяцы и год =ЛЕВСИМВ(A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000
Преобразуйте текст, полученный на предыдущем шаге в дату =ДАТАЗНАЧ(ЛЕВСИМВ(A4;ДЛСТР(A4)-4)&ПРАВСИМВ(A4;4)+1000)

Не забудьте, что в Англии и колониях переход с юлианского на григорианский календарь привел к тому, что вслед за 2 сентября 1752 года наступило сразу 14 сентября. В России вслед за 31 января 1918 года наступило 14 февраля (подробнее см. Григорианский календарь). Предложенная формула не обрабатывает эти аномалии.

Резюме: несмотря на то, что Excel не умеет напрямую работать с датами до 1900 года, вы можете добавить ко всем датам некое фиксированное число лет (например, 1000), и сделать доступным обработку формулами дат.

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

Удалить год с даты с помощью формул

Есть несколько формул для удаления года из даты, например Текст Функция, Объединить функции, вы можете применить формулы следующим образом:

1. В соседней пустой ячейке введите эту формулу: = ТЕКСТ (B2; «мм / дд») , см. снимок экрана:

2, Затем нажмите Enter и выберите ячейку C2, затем перетащите маркер заполнения по диапазону ячеек, который вы хотите содержать эту формулу. И год был удален из даты, см. Снимок экрана:

Чаевые: Кроме вышеуказанного Текст формула, можно использовать и другие формулы, например = СЦЕПИТЬ (МЕСЯЦ (B2); «-»; ДЕНЬ (B2)) и = МЕСЯЦ (B2) & "-" & ДЕНЬ (B2) . Если применить эти формулы, получится следующий результат:

Внимание: Поскольку это формулы, вам необходимо вставить их как значения при их копировании.

Удалить год с даты с помощью Kutools for Excel

Если вы не хотите запоминать формулу, Kutools for Excel's Применить форматирование даты утилита может помочь вам решить эту проблему.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите диапазон дат, в котором вы хотите удалить год, затем нажмите Kutools > Формат > Применить форматирование даты, см. снимок экрана:

2. В Применить форматирование даты в диалоговом окне выберите один тип формата даты, который содержит только день и месяц, например 3/14 или 14/3, а затем щелкните значок OK кнопка. Смотрите скриншот:

Вы можете видеть, что формат даты был изменен только на месяц и день.

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

Есть несколько способов удалить год из даты, указанной в Microsoft Excel. Вы можете использовать настраиваемое форматирование ячейки, чтобы скрыть ее, или использовать различные функции, такие как СЦЕПИТЬ, чтобы полностью удалить ее. Вот как.

Пользовательское форматирование чисел

Хотя значение даты в ячейке Excel технически является числом, Microsoft Excel использует определенный тип форматирования для его отображения. То же самое верно и для денежных значений, где символы валюты добавляются к значениям, чтобы показать, что они относятся к деньгам. Когда вы добавляете дату в Excel в формате, который он распознает, Excel автоматически меняет формат номера ячейки на тип «Дата».

Вы можете настроить этот тип для отображения значения даты по-разному. Например, вы можете переключаться между «11/02/2021» (в формате ДД / ММ / ГГГГ, который обычно используется в Великобритании) и «2021-02-11» для 11 февраля 2021 года.

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

Для этого выделите ячейки, содержащие исходные значения даты, затем выберите вкладку «Главная» на панели ленты. Оттуда щелкните стрелку рядом с раскрывающимся меню числового формата и выберите параметр «Дополнительные числовые форматы».

В меню «Формат ячеек» выберите нужный стиль форматирования из имеющихся вариантов «Дата». Как только это будет выбрано, выберите вариант «Пользовательский».

В параметре «Пользовательский» вы увидите стиль форматирования числа даты, отображаемый в виде текста в поле «Тип». Например, «11.02.2021» будет отображаться в этом поле как «дд / мм / гггг».

Чтобы удалить год, обязательно удалите все ссылки на «гггг» или «гг» в поле «Тип», а также любые лишние ограничители, такие как тире или косая черта. Например, «11.02.2021» потребует от вас удалить «/ yyyy» из поля типа «dd / mm / yyyy», оставив «dd / mm» на месте.

После внесения изменений нажмите кнопку «ОК».

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

СЦЕПИТЬ, функция

Если вы хотите удалить год из даты в Excel, вы можете объединить вывод двух функций (например, ДЕНЬ или МЕСЯЦ) вместе с помощью СЦЕПИТЬ. Вместо того, чтобы скрывать год от просмотра с помощью пользовательского форматирования чисел, CONCATENATE (с ДЕНЬМ и МЕСЯЦЕМ) позволяет вам создать отдельное значение, которое вообще не включает год.

В этом примере ячейка A2 содержит значение даты (12.11.2021) в формате ДД / ММ / ГГГГ. Используя CONCATENATE с DAY и MONTH, день (11) и месяц (12) из значения даты в A2 помещаются в другую ячейку, разделенную разделителем, который в данном случае является косой чертой.

Вы можете настроить эту формулу, чтобы изменить порядок значений дня и месяца (например, ММ / ДД) или использовать другой символ-разделитель. Вы также можете использовать дескриптор заполнения, чтобы скопировать эту формулу в другие ячейки столбца, чтобы удалить год из нескольких значений даты.

В статьях [1–2] указывалось, что не всякий год, порядковый номер которого кратен четырем, является високосным — годы, номера которых кратны 100, являются високосными только при условии, что их значения кратны 400. Поэтому 2000 год являлся високосным, а 1900 год — нет, и в нем было 365 дней, как у “обычного” года, а не 366 (в феврале — 28 дней, а не 29). Однако программа Microsoft Excel воспринимает 1900-й как високосный! Если ввести в ячейку дату 29.02.1900 , то Excel распознает такое значение как дату и, как обычно, выровняет его в ячейке по правому краю. Если же ввести другую недопустимую дату, например, 29.02.1901 , то Excel воспримет введенное значение как текстовую строку (выравнивание текста в ячейке по умолчанию происходит по левому краю).

Возникает вопрос: как программный продукт, который ежедневно используют миллионы людей во всем мире, может содержать такую ошибку? Ответ кроется в истории его создания. Исходная версия электронной таблицы Lotus 1-2-3, на которой базировалась программа Excel, содержала дефект, по причине которого 1900 год рассматривался как високосный. Разработчики Excel знали об этой ошибке и позднее, при выпуске приложения, специально перенесли этот дефект в новую программу, чтобы обеспечить его совместимость с рабочими листами файлов Lotus.

Но почему же эта ошибка существует в более поздних версиях Excel? Компания Microsoft утверждает, что недостатки, которые могут возникнуть при исправлении этой ошибки, значительно перевешивают преимущества. Если бы существующая ошибка была устранена, это привело бы в беспорядок сотни тысяч существующих рабочих книг. Кроме того, исправление этой проблемы сказалось бы на совместимости самого приложения Excel с другими программами, использующими данные типа “дата”. Именно поэтому в действительности эта ошибка влечет не так уж много проблем, как ее исправление. К тому же большинство пользователей не использует даты ранее 1 марта 1900 года.

1. Високосные годы. / “В мир информатики” № 17 (“Информатика” № 5/2004).

2. Как узнать номер дня недели? / “В мир информатики” № 114 (“Информатика” № 20/2008).

Читайте также: