Форматирование чисел в виде значений даты и времени
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Когда вы введите дату или время в ячейку, она отображается в формате даты и времени по умолчанию. Этот формат по умолчанию основан на региональных параметрах даты и времени, заданных на панели управления, и изменяется при их настройке на панели управления. Числа можно отобразить в нескольких других форматах даты и времени, на большинство из которых параметры панели управления не влияют.
В этой статье
- Отображение чисел в качестве даты или времени
- Создание пользовательского формата даты или времени
- Советы для отображения дат и времени
Отображение чисел в качестве даты или времени
Вы можете форматирование даты и времени по мере их ввести. Например, если ввести в ячейку 2/2, Excel будет автоматически интерпретирован как дата и отобразит в ячейке 02.фев. Если это не то, что вам нужно (например, если вы хотите, чтобы в ячейке были 2 февраля 2009 г. или 02.02.09), в диалоговом окне Формат ячеек можно выбрать другой формат даты, как покажем в следующей процедуре. Аналогично, если ввести в ячейку 9:30 a или 9:30 p, Excel интерпретирует это как время и отображает 9:30 или 21:30. Вы также можете настроить способ времени в диалоговом окне Формат ячеек.
- На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число. Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
- В списке Категория выберите дата иливремя.
- В списке Тип выберите нужный формат даты или времени.
Примечание: Форматы даты и времени, которые начинаются со звездочки (*), отвечают на изменения в региональных параметрах даты и времени, заданных на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
Число в активной ячейке выбранного на этом сайте отображается в поле Образец, чтобы можно было просмотреть выбранные параметры форматирования. 
Создание пользовательского формата даты или времени
Для отображения
Используйте код
Месяцев в виде чисел от 1 до 12
Как преобразовать число в дату
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Преобразование в Дату в Excel. Бесплатные примеры и статьи.
Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), — преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ(«11.09.2009») возвращает число 40067, соответствующее 11 …
update Опубликовано: 10 декабря 2013
Функция ЗНАЧЕН() в MS EXCEL
Функция ЗНАЧЕН( ) , английский вариант VALUE(), преобразует строку текста в число (там где это возможно).
update Опубликовано: 28 мая 2014
Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в MS EXCEL)
При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам …
update Опубликовано: 25 апреля 2013
Преобразование в MS EXCEL ТЕКСТовых значений в ДАТУ
Бывает, что при экспорте значений в EXCEL , даты записываются в незнакомом для EXCEL формате, например 20081223 (т.е. 2008г, 23 декабря). Для дальнейшей работы с такими датами выполним преобразование в …
update Опубликовано: 25 апреля 2013
Автоматическое преобразование формата ячейки в MS EXCEL при вводе ТЕКСТовых данных (Часть 2)
Продолжаем бороться в MS EXCEL 2007 с автоматическим преобразованием формата ячейки при вводе данных в ячейку. При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести …
Как преобразовать число в дату
Очень часто можно столкнуться с такой проблемой: когда вы вставляете в ячейки Excel числа, в которых в качестве десятичного разделителя используется точка, то некоторые такие числа Excel преобразовывает в даты, а другие визуально похожи на те же числа, но на самом деле являются текстом, с которым невозможно работать в дальнейшем как с числами.
- Несоответствие регионального формата исходных данных региональным настройкам вашей операционной системы. В исходных данных в качестве десятичного разделителя используется точка (американский формат), а у вас – региональные настройки, в которых десятичным разделителем является запятая, а точка воспринимается как разделитель отдельных составляющих даты – день, месяц, год (например, 17.09.202.
- Особое поведение Excel для ячеек в «Общем» числовом формате (который установлен по умолчанию): если вводить в ячейку что-то похожее на число, то такое значение сохраняется как число, если вводить что-либо, похожее на дату – то такое значение сохраняется как дата. Если вводимое значение не похоже ни на число, ни на дату – то это текст.
В данном примере (выше) поврежденные данные:
Исходные данные сохранились как текст:
- Ячейка D3 – исходное значение (179.3287) мало похоже на дату, так как номер месяца 179 не существует (а вот год 3287 возможен, в далеком будущем).
- Ячейка D4 – исходное значение похоже на дату (июль 1275 года). Однако в Excel точкой отсчета дат является 1 января 1900 года. Поэтому число 1275 (ранее 1900 года) не воспринимается как год.
Исходные данные были преобразованы в даты (и, соответственно, утеряны):
- Ячейка D5 – в исходном значении (7.1972) число до точки похоже на номер месяца 7 (июль), а число после точки – на год 1972 (на самом деле произошло преобразование в дату 01.07.1972)
- Ячейка D6 – в исходном значении (11.2024) число до точки воспринимается как номер месяца (ноябрь), а после точки – год 2024 (произошло преобразование в дату 01.11.2024)
Какие есть способы решения данной проблемы?
Начнем с самых кардинальных и, соответственно, не самых лучших.
Способ-1. Поменять региональные настройки операционной системы (на американские).
Да, но такое глобальное изменение повлияет и на работу других приложений и служб (не только Excel).
Способ-2. Локально в Excel (в параметрах Excel) заблаговременно изменить десятичный разделитель с запятой на точку:
Способ-3. Заблаговременно установить «текстовый» формат ячеек (вместо «общего»), в которые будут вставляться значения.
Тогда мы избавимся от авто преобразования «чисел», похожих на даты, в непосредственно даты, и получим значения в исходном виде, только в формате текста. Далее этот текст легко будет преобразовать в числа с помощью простой формулы:
=SUBSTITUTE(A3;».»;»,»)*1 (в диапазоне C3:C9)
либо еще лучше, формулы:
=NUMBERVALUE(A3;».») (в диапазоне E3:E9)
Русские варианты этих формул:
Способ-4. Формулой (без какой-либо предварительной подготовки):
Можно попробовать восстановить уже испорченные числа формулой
Формула в ячейке G3:
=1*IF(CELL(«format»;D3)=»G»; SUBSTITUTE(D3;».»;»,»); TEXT(D3;»M,YYYY»))
или русский вариант:
=1*ЕСЛИ(ЯЧЕЙКА(«формат»;D3)=»G«; ПОДСТАВИТЬ(D3;».»;»,»); ТЕКСТ(D3;»М,ГГГГ»))
Формула работает следующим образом:
CELL() (ЯЧЕЙКА()) – функция из категории «Информационные». Если первый ее аргумент (тип информации) указан “format”, то результат работы функции — «G» — для текста или чисел и «D3» — для дат.
Если в ячейке находится текст, то функция SUBSTITUTE() (ПОДСТАВИТЬ()) заменяет точку на запятую и умножение на 1 в начале формулы (применение арифметической операции) приводит к преобразованию текста, состоящего из цифр с запятой, — в число.
Если в ячейке не текст (а дата), то функция TEXT() (ТЕКСТ()) преобразует дату в текст в соответствии с кодом формата, указанного в качестве второго ее аргумента. И далее такой текст легко преобразуется в число путем умножения на 1 (в начале формулы).
Используя код формата в функции TEXT, нужно учитывать то, что и даже в англоязычной версии, в зависимости от региональных настроек, вместо “M,YYYY”, возможно, нужно указывать “М,ГГГГ” (где “M” – кириллическое).
ВАЖНО! Формула не является универсальным решением (на все случаи жизни)
Она хорошо работает, если после точки находится от 3-х и более цифр.
А что, если имеем такое:
В первых трех ячейках уже цифры до точки воспринимаются как номер дня (а не номер месяца), а цифры после точки – как номер месяца (а не номер года).
А в последних двух ячейках разные исходные числа привели к получению одной и той же даты.
Если в первом случае можно было бы еще добавить проверку дополнительных условий в формуле, то в последнем – исходные значения безвозвратно утеряны и узнать какие они были – уже не представляется возможным. Очевидно, в таком случае, единственный вариант – это не допустить автоматического преобразования «как бы чисел» в даты, то есть указанный выше способ 3 – чуть ли не единственное решение (не считая кардинальных изменений региональных настроек).
Такие и подобные тонкости работы в Excel мы прорабатываем на курсе «Excel бизнес-анализ и прогнозирование»
За 27 часов изучения Excel у вас не останется никаких вопросов его работе, а если они и возникнут, то наши тренера-поддержки онлайн помогут с ответами.
Автор статьи CEO и тренер DATAbi Евгений Довженко