Как преобразовать дату в серийный номер в Excel?

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

Excel сохраняет значения даты и времени в виде порядковых номеров на стороне сервера.

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

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

Оба столбца ниже содержат одинаковые числа, но в столбце А показаны цифры, а в столбце Б — дата.

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

Число может отображаться как число, а не как дата, если формат ячейки настроен на отображение чисел, а не дат.

Как же перевести эти серийные номера в даты?

Именно об этом и пойдет речь в этом уроке.

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

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

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

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

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

Сейчас я покажу вам, как это сделать.

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

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

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

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

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

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

Обратите внимание, что Excel выбирает форматирование короткой даты в зависимости от региональных настроек вашей системы. Например, если вы находитесь в США, формат даты — MM/DD/YYYY, тогда как в Великобритании формат даты — DD/MM/YYYY.

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

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

В результате, если вам нужна большая гибкость в отображении дат в Excel, следует использовать опцию «Пользовательское форматирование чисел» (обсуждается далее в этом руководстве).

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

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

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

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

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

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

Ниже описаны шаги по форматированию дат по вашему выбору.

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

Когда вы не можете найти то, что ищете, вы можете создать свой собственный формат.

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

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

1. Выделите ячейки с числами, которые вы хотите преобразовать в дату.
2. Перейдите на вкладку "Главная"
3. В группе "Числа" нажмите значок запуска диалогового окна (это небольшая стрелка наклона в правом нижнем углу группы).
4. В открывшемся диалоговом окне «Формат ячеек» убедитесь, что выбрана вкладка «Число».
5. В параметрах «Категория» слева выберите «Пользовательский».
6. В поле справа введите

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

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

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

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

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

Описанные выше методы работают за счет изменения форматирования ячеек с номерами.

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

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

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

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

Число для форматирования и код формата предоставляются функцией ТЕКСТ.

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

Я привел формулу, которая позволит этого добиться:

Обратите внимание, что я указал формат даты в формуле как MM / DD / YYYYY. Если вам нужно использовать другой формат, вы можете указать свой код в качестве второго аргумента формулы TEXT.

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

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

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

Дата может быть достигнута путем преобразования формул в статические значения при помощи специальной вставки.

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

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

Вот два простых способа преобразовать серийные номера в даты в Microsoft Excel.

Интерпретация даты и времени

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

Номер серии дат — это (фактическое) количество дней с 30 декабря 1899 г., т.е. формат QlikView идентичен системе дат 1900 г., используемой в Microsoft Excel и других приложениях, с 1 марта 1900 г. по 28 февраля 2100 г. Например, 33857 соответствует 10 сентября 1992 года.

Порядковый номер значения времени — это число от 0 до 1. Порядковый номер 0.00000 соответствует 00:00:00:00, а 0.99999 — 23:59:59. Смешанные цифры обозначают дату и время: серийный номер 2.5 означает 1 января 1900 года, в полдень.

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

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

В этих примерах используются следующие настройки по умолчанию

• Десятичная точка: .
• Формат короткой даты: ГГ-ММ-ДД
• Формат времени: ЧЧ:мм

В следующей таблице показаны различные представления, когда данные загружаются в QlikView без специальной функции интерпретации в сценарии:

В следующей таблице показаны различные представления при чтении данных в программе QlikView с помощью функции интерпретации в сценарии date#( A, ‘M/D/YYYY’):