Excel хранит значения даты и времени в виде серийных номеров в серверной части.
Это означает, что, хотя вы можете видеть дату, например «10 января2021-2022» или «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
Ниже приведена таблица, в которой показаны коды формата даты, которые вы можете использовать:
Код форматирования даты | Как форматируется дата |
м | Показывает месяц в виде числа от 1 до 12 |
мм | Показывать месяцы в виде двузначных чисел от 01 до 12 |
М-м-м | Показывает месяц как трехбуквенный, как в январе-декабре |
мммм | Показывает полное название месяцев, как в январе-декабре. |
ммммм | Показывает название месяца первым алфавитом, как в J-D |
d | Показывает дни как 1-31 |
дд | Показывает дни как 01-31 |
ддд | Показывает дни как вс-сб |
дддд | Показывает дни как воскресенье-суббота |
гг | Показывает годы как 00–99 |
гггг | Показывает годы как 1900-9999 гг. |
Преобразование серийных номеров в даты с помощью формулы ТЕКСТ
Описанные выше методы работают путем изменения форматирования ячеек с числами.
Но в некоторых случаях вы не сможете использовать эти методы.
Например, ниже у меня есть набор данных, в котором числа имеют начальный апостроф, который преобразует эти числа в текст.
Если вы попытаетесь использовать встроенные параметры для изменения форматирования ячеек с этим набором данных, это ничего не даст. Это связано с тем, что Excel не считает их числами, а поскольку даты для чисел, Excel отказывается от вашего желания их форматировать.
В таком случае вы можете либо преобразовать этот текст в числа, а затем использовать описанные выше методы форматирования, либо использовать другой метод для преобразования серийных номеров в даты - используя функцию ТЕКСТ.
Функция ТЕКСТ принимает два аргумента - число, которое нужно отформатировать, и код формата.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите преобразовать все числа и столбец A в даты.
Ниже приведена формула, которая могла бы это сделать:
= ТЕКСТ (A2; «ММ / ДД / ГГГГ»)
Обратите внимание, что в формуле я указал форматирование даты в формате ММ / ДД / ГГГГ. Если вам нужно использовать любое другое форматирование, вы можете указать его код в качестве второго аргумента формулы ТЕКСТ.
Вы также можете использовать эту формулу для отображения даты и времени.
Например, если вы хотите, чтобы окончательный результат был в формате - 01.01.2020 12:00, используйте следующую формулу:
= ТЕКСТ (A2; «мм / дд / гггг чч: мм AM / PM»)
В приведенной выше формуле я также добавил формат времени, поэтому, если в числах есть десятичные части, оно будет отображаться как время в часах и минутах.
Если вам нужна только дата (а не основная формула), преобразуйте формулы в статические значения с помощью специальной вставки.
Одним из больших преимуществ использования функции ТЕКСТ для преобразования серийных номеров в даты является то, что вы можете комбинировать результат формулы ТЕКСТ с другими формулами. Например, вы можете объединить полученную дату с текстом и показать что-то вроде - Срок сдачи - 1 января 2020 г. или Крайний срок - 1 января 2020 г.
Примечание: помните, что даты и время хранятся в виде чисел, где каждое целое число будет представлять один полный день, а десятичная часть будет представлять эту большую часть дня. Таким образом, 1 будет означать 1 полный день, а 0,5 - полдня или 12 часов.
Итак, это два простых способа преобразования серийных номеров в даты в Microsoft Excel.
Надеюсь, вы нашли этот урок полезным!