Excel хранит значения даты и времени в виде серийных номеров в серверной части. Это означает, что, хотя вы можете видеть в ячейке такую ​​дату, как «10 января 2020 года» или «01.10.2020», в серверной части Excel сохраняет это как число. Это полезно, поскольку также позволяет пользователям легко добавлять / вычитать дату и время в Excel.

В Microsoft Excel для Windows «01 января 1900» сохраняется как 1, «02 января 1900» сохраняется как 2 и так далее.

Ниже оба столбца имеют одинаковые числа, но столбец A показывает числа, а столбец B показывает дату, представленную этим числом.

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

Итак, как мы можем преобразовать эти серийные номера в даты ?

Вот о чем этот урок.

В этом уроке я покажу вам два действительно простых способа конвертировать серийные номера в даты в Excel.

Примечание: Excel для Windows использует систему дат 1900 (что означает, что 1 будет представлять 1 января 1900 года, тогда как Excel для Mac использует систему дат 1904 года (у мужчин, что 1 будет представлять 1 января 1904 года в Excel на Mac).

Итак, приступим!

Преобразование серийных номеров в даты с помощью форматирования чисел

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

Вы можете найти некоторые из наиболее часто используемых форматов даты на вкладке «Главная» на ленте.

Позвольте мне показать вам, как это сделать.

Использование встроенных параметров формата даты на ленте

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

Ниже приведены шаги для этого:

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

Это оно!

Вышеупомянутые шаги преобразуют числа в выбранный формат даты.

Вышеупомянутые шаги не изменили значение в ячейке, только способ его отображения.

Обратите внимание, что Excel выбирает форматирование короткой даты на основе региональных настроек вашей системы. Например, если вы находитесь в США, формат даты будет ММ / ДД / ГГГГ, а если вы находитесь в Великобритании, то формат даты будет ДД / ММ / ГГГГ.

Хотя это быстрый способ преобразования серийных номеров в даты, у него есть несколько ограничений:

  • Есть только два формата даты — короткая дата и длинная дата (и это тоже в определенном формате). Итак, если вы хотите получить только месяц и год, а не день, вы не сможете сделать это с помощью этих параметров.
  • Вы не можете отображать дату и время с помощью параметров форматирования в раскрывающемся списке. Вы можете выбрать отображение даты или времени, но не то и другое одновременно.

Итак, если вам нужна большая гибкость в том, как вы хотите отображать даты в Excel, вам нужно использовать параметр настраиваемого форматирования чисел (рассматривается далее в этом руководстве).

Иногда, когда вы конвертируете число в дату в Excel, вместо даты вы можете видеть некоторые знаки решетки (что-то вроде #####). это происходит, когда ширины столбца недостаточно для размещения всей даты. В таком случае просто увеличьте ширину столбца.

Создание настраиваемого формата даты с помощью диалогового окна «Форматирование чисел»

Предположим, у вас есть набор данных, как показано ниже, и вы хотите преобразовать серийные номера в столбце A в даты в следующем формате — 01.01.2020

Обратите внимание, что этот параметр по умолчанию недоступен в методе ленты, который мы рассмотрели ранее.

Ниже приведены шаги для этого:

  • Выделите ячейки с числом, которое вы хотите преобразовать в дату.
  • Перейдите на вкладку «Главная».
  • В группе «Числа» щелкните значок средства запуска диалогового окна (это небольшая наклонная стрелка в правом нижнем углу группы).
  • В открывшемся диалоговом окне «Формат ячеек» убедитесь, что выбрана вкладка «Число».
  • В параметрах «Категория» слева выберите «Дата».
  • Выберите желаемое форматирование в поле «Тип».
  • Нажмите ОК.

Вышеупомянутые шаги преобразуют числа в выбранный формат даты.

Как видите, в этом случае есть больше вариантов форматирования даты (по сравнению с короткой и длинной датой, которую мы получили с лентой).

А если вы не нашли нужный формат, вы также можете создать свой собственный формат даты.

Например, предположим, что я хочу показать дату в следующем формате — 01/01/2020 (что еще не является параметром в диалоговом окне ячеек формата).

Вот как я могу это сделать:

  • Выделите ячейки с числами, которые вы хотите преобразовать в дату.
  • Перейдите на вкладку «Главная»
  • В группе «Числа» щелкните значок средства запуска диалогового окна (это небольшая стрелка наклона в правом нижнем углу группы).
  • В открывшемся диалоговом окне «Формат ячеек» убедитесь, что выбрана вкладка «Число».
  • В параметрах «Категория» слева выберите «Пользовательский».
  • В поле справа введите мм / дд / гггг.
  • Нажмите ОК.

Вышеупомянутые шаги превратят все числа в указанный числовой формат.

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

Например, в том же примере, если вы хотите отображать дату и время, вы можете использовать следующий формат:

мм / дд / гггг чч: мм AM / PM

Ниже приведена таблица, в которой показаны коды формата даты, которые вы можете использовать:

Код форматирования даты Как форматируется дата
m Показывает месяц в виде числа от 1 до 12.
mm Показывать месяцы в виде двузначных чисел от 01 до 12
mmm Месяц отображается в виде трех букв, как в январе – декабре.
mmmm Показывает полное название месяца, как в январе – декабре.
mmmmm Показывает название месяца первым алфавитом, как в JD
d Показывает дни как 1–31
dd Показывает дни как 01–31
ddd Показывает дни как вс – сб
dddd Показывает дни с воскресенья по субботу.
yy Показывает годы как 00–99
yyyy Показывает годы как 1900–9999.

Преобразование серийных номеров в даты с помощью формулы TEXT

Описанные выше методы работают путем изменения форматирования ячеек с числами. Но в некоторых случаях вы не сможете использовать эти методы.

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

Если вы попытаетесь использовать встроенные параметры для изменения форматирования ячеек с этим набором данных, это ничего не даст. Это связано с тем, что Excel не считает их числами, а поскольку даты для чисел, Excel отказывается от вашего желания их форматировать.

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

Функция ТЕКСТ принимает два аргумента — число, которое нужно отформатировать, и код формата.

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

 

Ниже приведена формула, которая могла бы это сделать:

=TEXT(A2,"MM/DD/YYYY")

Обратите внимание, что в формуле я указал форматирование даты в формате ММ / ДД / ГГГГ. Если вам нужно использовать любое другое форматирование, вы можете указать его код в качестве второго аргумента формулы ТЕКСТ.

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

Например, если вы хотите, чтобы окончательный результат был в формате — 01.01.2020 12:00, используйте следующую формулу:

=TEXT(A2,"mm/dd/yyyy hh:mm AM/PM")

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

Если вам нужна только дата (а не основная формула), преобразуйте формулы в статические значения с помощью специальной вставки.

Одним из больших преимуществ использования функции ТЕКСТ для преобразования серийных номеров в даты является то, что вы можете комбинировать результат формулы ТЕКСТ с другими формулами. Например, вы можете объединить полученную дату с текстом и показать что-то вроде — Срок сдачи — 1 января 2020 г. или Крайний срок — 1 января 2020 г.

Примечание: помните, что даты и время хранятся в виде чисел, где каждое целое число будет представлять один полный день, а десятичная часть будет представлять эту большую часть дня. Таким образом, 1 будет означать 1 полный день, а 0,5 — полдня или 12 часов.

Итак, это два простых способа преобразования серийных номеров в даты в Microsoft Excel.

Надеюсь, вы нашли этот урок полезным!

Как преобразовать серийные номера в даты в Excel (2 простых способа)