Загрузка...Как преобразовать дату в серийный номер в 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 в соответствующую дату, которую они представляют.
Ниже описаны шаги для этого:
- Выделите ячейки с числом, которое вы хотите преобразовать в дату.
- Перейдите на вкладку "Главная"
- В группе «Число» нажмите раскрывающееся меню «Форматирование чисел».
- В раскрывающемся списке выберите вариант «Длинная дата» или «Краткая дата» (в зависимости от того, в каком формате вы хотите, чтобы эти числа были).
Вышеописанные действия преобразуют числа в выбранный формат даты.
Приведенные выше действия не изменяют значение ячейки, а только способ его отображения.
Обратите внимание, что Excel выбирает форматирование короткой даты в зависимости от региональных настроек вашей системы. Например, если вы находитесь в США, формат даты — MM/DD/YYYY, тогда как в Великобритании формат даты — DD/MM/YYYY.
Хотя этот метод преобразования серийных номеров в даты является быстрым, он имеет некоторые ограничения:
- Существует только два формата даты: короткая дата и длинная дата (и в определенном формате). Поэтому, если вы хотите получить только месяц и год, но не день, вы не сможете сделать это с помощью этих опций.
- Вы не можете отображать дату и время с помощью выпадающих опций форматирования. Вы можете выбрать отображение даты или времени, но не обоих одновременно.
В результате, если вам нужна большая гибкость в отображении дат в Excel, следует использовать опцию «Пользовательское форматирование чисел» (обсуждается далее в этом руководстве).
Иногда при преобразовании числа в дату в Excel вместо даты можно увидеть некоторые символы сетки (что-то вроде #####). Это происходит, когда ширина столбца недостаточна для размещения всей даты. В этом случае просто увеличьте ширину столбца.
Создание настраиваемого формата даты с помощью диалогового окна «Форматирование чисел»
Предположим, у вас есть набор данных, как показано ниже, и вы хотите преобразовать серийные номера в столбце A в даты следующего формата: 01.01.2020.
Обратите внимание, что эта настройка недоступна по умолчанию в методе с лентой, о котором говорилось выше.
Ниже описаны шаги для этого:
- Выделите ячейки с числом, которое вы хотите преобразовать в дату.
- Перейдите на вкладку "Главная"
- В группе "Числа" нажмите значок запуска диалогового окна (это небольшая наклонная стрелка в правом нижнем углу группы).
- В открывшемся диалоговом окне «Формат ячеек» убедитесь, что выбрана вкладка «Число».
- В параметрах «Категория» слева выберите «Дата».
- Выберите желаемое форматирование в поле «Тип».
- Нажмите ОК.
Ниже описаны шаги по форматированию дат по вашему выбору.
Как вы можете видеть, в этом случае существует больше вариантов форматирования даты (по сравнению с короткой и длинной датами, которые мы получаем с помощью ленты).
Когда вы не можете найти то, что ищете, вы можете создать свой собственный формат.
Например, если я хочу отобразить дату в следующем формате — 01/01/2020 (который еще не является опцией в диалоге Формат ячеек).
Вот так я могу это сделать:
- Выделите ячейки с числами, которые вы хотите преобразовать в дату.
- Перейдите на вкладку "Главная"
- В группе "Числа" нажмите значок запуска диалогового окна (это небольшая стрелка наклона в правом нижнем углу группы).
- В открывшемся диалоговом окне «Формат ячеек» убедитесь, что выбрана вкладка «Число».
- В параметрах «Категория» слева выберите «Пользовательский».
- В поле справа введите
Все числа будут преобразованы в указанный числовой формат с помощью вышеописанных действий.
Используя пользовательский формат, вы получаете полный контроль и можете отображать дату в том формате, который вам больше нравится. Вы также можете отображать дату и время в одной ячейке.
В этом же примере, если вы хотите отобразить дату и время, можно использовать следующий формат:
В таблице ниже приведены коды формата даты, которые вы можете использовать:
| Код форматирования даты | Как форматируется дата |
| м | Показывает месяц в виде числа от 1 до 12 |
| мм | Показывать месяцы в виде двузначных чисел от 01 до 12 |
| М-м-м | Показывает месяц как трехбуквенный, как в январе-декабре |
| мммм | Показывает полное название месяцев, как в январе-декабре. |
| ммммм | Показывает название месяца первым алфавитом, как в J-D |
| d | Показывает дни как 1-31 |
| дд | Показывает дни как 01-31 |
| ддд | Показывает дни как вс-сб |
| дддд | Показывает дни как воскресенье-суббота |
| гг | Показывает годы как 00–99 |
| гггг | Показывает годы как 1900-9999 гг. |
Преобразование серийных номеров в даты с помощью формулы ТЕКСТ
Описанные выше методы работают за счет изменения форматирования ячеек с номерами.
Однако в некоторых случаях вы не сможете воспользоваться этими методами.
Например, ниже у меня есть набор данных, в котором числа имеют начальный апостроф, который преобразовывает эти числа в текст.
Встроенных функций, позволяющих изменить форматирование ячеек этого набора данных, не существует. Это связано с тем, что 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 года, в полдень.
Однако данные отображаются в линейном формате. По умолчанию используется раздел Панель управления. Регулируя переменные интерпретации числа или настраивая функцию форматирования, можно настроить формат данных в сценарии. В крайнем случае, можно переформатировать данные на странице свойств объекта листа.
| Строка даты | Серийный номер даты |
|---|---|
| 1997-08-06 | 35648 |
| 09:00 | 0.375 |
| 1997-08-06 09:00 | 35648.375 |
| Серийный номер даты | Указанный формат числа | Строка даты |
|---|---|---|
| 35648 | ‘D/M/YY’ | 6/8/97 |
| 0.375 | ‘hh.mm’ | 09.00 |
Пытаясь интерпретировать даты, время и другие типы данных, QlikView следует установленным правилам. Однако конечный результат будет зависеть от нескольких факторов, как описано выше.
В этих примерах используются следующие настройки по умолчанию
- Десятичная точка: .
- Формат короткой даты: ГГ-ММ-ДД
- Формат времени: ЧЧ:мм
В следующей таблице показаны различные представления, когда данные загружаются в QlikView без специальной функции интерпретации в сценарии:
| Исходные данные | Интерпретация программы QlikView по умолчанию | Формат даты ‘YYYY-MM-DD’ | Формат даты ‘MM/DD/YYYY’ | Формат времени ‘hh:mm’ | Формат числа ‘# ##0.00’ |
|---|---|---|---|---|---|
| 0.375 | 0.375 | 1899-12-30 | 12/30/1899 | 09:00 | 0.38 |
| 33857 | 33857 | 1992-09-10 | 09/10/1992 | 00:00 | 33 857.00 |
| 97-08-06 | 97-08-06 | 1997-08-06 | 08/06/1997 | 00:00 | 35 648.00 |
| 970806 | 970806 | 4557-12-21 | 12/21/4557 | 00:00 | 970 806.00 |
| 8/6/97 | 8/6/97 | 8/6/97 | 8/6/97 | 8/6/97 | 8/6/97 |
В следующей таблице показаны различные представления при чтении данных в программе QlikView с помощью функции интерпретации в сценарии date#( A, ‘M/D/YYYY’):
