Почему не меняется формат даты в Excel? Возможной причиной является конфликт форматов или блокировка стиля ячеек, неправильное выполнение работы или временный сбой в работе приложения. Для устранения попробуйте перезапустить программу или воспользуйтесь одним из приведенных в статье способов. Ниже рассмотрим основные причины и способы решения такой проблемы в Excel.
Причины и пути решения
Для решения проблемы нужно понимать, почему Эксель не меняет формат даты при любых попытках. К возможным причинам стоит отнести:
- Конфликт форматов и блокировка стиля ячейки.
- Несоответствие заданных стилей Excel.
- Неправильное выполнение работы.
- Временные сбои в работе программы.
- Неправильные региональные настройки.
В ситуации, когда не меняется дата в Эксель, можно воспользоваться одним из рассмотренных ниже решений.
Способ №1:
- Выберите столбец даты в Excel.
- Зайдите в раздел «Данные» и выберите «Text to Columns».
- На первом экране поставьте переключатель «delimited».
- Кликните на кнопку «Далее».
- Снимите флажки-разделители и кликните на кнопку «Далее».
- В секции «Формат данных столбца» выберите «Дату».
- Жмите «Готово».
Решение №2:
- Выделите ячейки, где не меняется дата в Экселе.
- Жмите на сочетание Ctrl+1.
- В разделе «Формат ячеек» откройте вкладку «Число».
- В перечне «Категория» выберите требуемый раздел.
- Перейдите в «Тип» и укажите подходящий вариант форматирования в Excel. Для предварительного просмотра перейдите в раздел «Образец». Если сделанный вариант не устраивает, можно внести изменения и снова выполнить проверку — меняется отображение или нет.
Метод №3:
- Жмите нужную ячейку, где формат даты в Эксель не меняется.
- Кликните сочетание Ctrl+Shift+#.
Способ №4:
- Используйте формулу: =DATE(MID(A1,FIND(«,»,A1)+1,5),MATCH(LEFT(A1,3),{«Jan»;»Feb»;»Mar»;»Apr»;»May»;»Jun»;»Jul»;»Aug»;»Sep»;»Oct»;»Nov»;»Dec»},0),MID(SUBSTITUTE(A1,»,»,» «),5,5)) для перевода в REAL.
- Проверьте, что отображение в Excel меняется с учетом предпочтений.
Такой вариант может сработать в случае неправильных региональных настроек и невозможности изменения форматирования для дня, строки и времени. Представленную форму Excel можно сделать проще, если представить больше сведений о формате ввода. Но даже в таком случае все должно работать корректно. Кроме того, при желании сохранить часть времени можно воспользоваться формулой +RIGHT(A1,11).
Решение №5:
- Загрузите лист Excel, где не меняется стиль отображения, на Гугл Док.
- Откройте файл с помощью электронной Гугл-таблицы.
- Выберите весь столбец с информацией.
- Установите тип формата для даты. На данном этапе можно выбрать любой подходящий вариант.
- Закройте электронную таблицу Гугл в формате .xlsx.
Метод №6:
- Копируйте несколько ячеек, где не меняется форматирование даты в Excel, в блокнот.
- Жмите на Clear All.
- Вставьте содержимое ячейки в блокнот обратно в те же ячейки.
- Установите их в качестве текущего дня в нужном варианте и т. д.
Как поменять формат в Excel
Распространенная причина, почему софт не меняет дату в Экселе — неправильные действия пользователя. Для решения проблемы необходимо правильно сделать эту работу. Здесь существует несколько путей.
Вариант №1:
- Зайдите во вкладку «Главная».
- Перейдите в раздел «Число».
- Жмите на кнопку вызова диалогового окна (находится над строчкой «Число»).
- В списке «Категория» выберите нужный пункт.
- Перейдите в раздел «Тип» и установите нужный вариант отображения в Excel.
Тип форматирования, который начинается со звездочки, влияют за изменение параметров дня / времени, установленных на панели управления. В ином случае этого влияния нет. После этого необходимо выбрать нужный язык, который меняется с помощью специального переключателя. Выбор осуществляется из доступного перечня (русский язык предусмотрен).
Вариант №2:
- Зайдите во вкладку XLTools.
- В группе «Дата и время» откройте выпадающий перечень.
- Войдите в раздел «Изменить формат даты и времени».
- Задайте область поиска. Здесь указывается выбранный диапазон, рабочий лист, книга, открытые книги.
- Выберите подходящий вариант из перечня, принятый в стране и на нужном языке. По желанию задайте пользовательский формат.
- Кликните на кнопку «Готово».
Если Excel не меняет формат даты, это не повод беспокоиться. В большинстве случаев проблема связана с неправильным выполнением работы или временными сбоями в работе приложения. Перед тем, как предпринимать какие-либо шаги, попробуйте сохраниться и перезапустить Excel. Если же это не дало результата, проверьте возможность применения одного из рассмотренных выше методов.
В комментариях расскажите, приходилось ли вам сталкиваться с ситуацией, когда не работает дата в Эксель или не меняется ее форматирование. Поделитесь, какой из приведенных выше методов сработал, и какие еще варианты можно использовать.
Отличного Вам дня!
Форматирование чисел в виде значений даты и времени
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, чтобы открыть диалоговое окно Формат ячеек.
-
В списке Категория выберите дата иливремя.
-
В списке Тип выберите нужный формат даты или времени.
Примечание: Форматы даты и времени, которые начинаются со звездочки (*), отвечают на изменения в региональных параметрах даты и времени, заданных на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
-
Чтобы отобразить даты и время в формате других языков, выберите нужный язык в поле Языковой стандарт (расположение).
Число в активной ячейке выбранного на этом сайте отображается в поле Образец, чтобы можно было просмотреть выбранные параметры форматирования.
К началу страницы
Создание пользовательского формата даты или времени
-
На вкладке Главная нажмите кнопку вызова диалогового окна рядом с именем группы Число.
Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
-
В поле Категория выберите дата или время ,а затем выберите числовом формате, наиболее близком по стилю к тому, который вы хотите создать. (При создании пользовательских числных форматов проще начать с существующего, чем с нуля.)
-
В списке Категория выберите пункт (все форматы). В поле Тип вы увидите код формата, совпадающий с форматом даты или времени, выбранным на шаге 3. Встроенный формат даты или времени нельзя изменить или удалить, поэтому не беспокойтесь о переописи.
-
В поле Тип введите необходимые изменения формата. Вы можете использовать любой из кодов в следующих таблицах:
Дни, месяцы и годы
|
Для отображения |
Используйте код |
|---|---|
|
Месяцев в виде чисел от 1 до 12 |
м |
|
Месяцев в виде чисел от 01 до 12 |
мм |
|
Месяцев в виде «янв», …, «дек» |
ммм |
|
Месяцев в виде «январь», …, «декабрь» |
мммм |
|
Месяцев в виде первой буквы месяца |
ммммм |
|
Дней в виде чисел от 1 до 31 |
д |
|
Дней в виде чисел от 01 до 31 |
дд |
|
Дней в виде «Пн», …, «Вс» |
ддд |
|
Дней в виде «понедельник», …, «воскресенье» |
дддд |
|
Лет в виде чисел от 00 до 99 |
гг |
|
Лет в виде чисел от 1900 до 9999 |
гггг |
Если вы используете «м» сразу после кода «ч» или «чч» или непосредственно перед кодом «сс», Excel отображает минуты вместо месяца.
Часы, минуты и секунды
|
Для отображения |
Используйте код |
|---|---|
|
Часы в качестве 0–23 |
ч |
|
Часы в качестве 00–23 |
чч |
|
Минуты в качестве 0–59 |
м |
|
Минуты в качестве 00–59 |
мм |
|
Секунды в качестве 0–59 |
с |
|
Секунды в качестве 00–59 |
ss |
|
Часы с 04:00 до 04:0 |
ч |
|
Время: 16:36 |
ч:мм |
|
Время в 4:36:03 P |
ч:мм:сс |
|
Заслон времени в часах; например, 25,02 |
[ч]:мм |
|
Заслон времени в минутах; например, 63:46 |
[мм]:сс |
|
За считанные секунды |
[сс] |
|
Доля секунды |
ч:мм:сс,00 |
AM и PM Если формат содержит am или PM, часы основаны на 12-часовом формате, где «AM» или «A» указывает время от полуночи до полудня, а «PM» или «P» — время от полудня до полуночи. В противном случае используется 24-часовой цикл. Код «м» или «мм» должен отображаться сразу после кода «ч» или «чч» или непосредственно перед кодом «сс»; в противном Excel отображается месяц, а не минуты.
Создавать пользовательские числовые форматы может быть непросто, если вы этого еще не сделали. Дополнительные сведения о создании пользовательских числных форматов см. в теме Создание и удаление пользовательских числов.
К началу страницы
Советы для отображения дат и времени
-
Чтобы быстро использовать стандартный формат даты или времени, щелкните ячейку с датой или временем и нажмите CTRL+SHIFT+# или CTRL+SHIFT+@.
-
Если после применения к ячейке формата даты или времени в ней отображаются ####, вероятно, ширины ячейки недостаточно для отображения данных. Чтобы увеличить ширину столбца, дважды щелкните правую границу столбца, содержащего ячейки. Ширина столбца будет автоматически изменена таким образом, чтобы вместить содержимое ячеек. Можно также перетащить правую границу столбца до необходимой ширины.
-
При попытке отменить формат даты или времени с помощью выбора в списке Категория общего Excel отображает числовом коде. При повторном вводе даты или времени Excel формат даты или времени по умолчанию. Чтобы ввести определенный формат даты или времени, например январь 2010г., можно отформать его как текст, выбрав текст в списке Категория.
-
Чтобы быстро ввести текущую дату, выйдите из любой пустой ячейки и нажмите CTRL+; (точка с за semicolon) и при необходимости нажмите ввод. Чтобы вставить дату, которая будет обновляться до текущей даты при каждом повторном повторном пересчете или пересчете формулы, введите =СЕГОДНЯ() в пустую ячейку и нажмите ввод.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Нужна дополнительная помощь?
Содержание
- Решение проблемы отображения числа как даты
- Способ 1: контекстное меню
- Способ 2: изменение форматирования на ленте
- Вопросы и ответы
Бывают случаи, когда при работе в программе Excel, после занесения числа в ячейку, оно отображается в виде даты. Особенно такая ситуация раздражает, если нужно произвести ввод данных другого типа, а пользователь не знает как это сделать. Давайте разберемся, почему в Экселе вместо чисел отображается дата, а также определим, как исправить эту ситуацию.
Решение проблемы отображения числа как даты
Единственной причиной, почему данные в ячейке могут отображаться как дата, является то, что в ней установлен соответствующий формат. Таким образом, чтобы наладить отображение данных, как ему нужно, пользователь должен его поменять. Сделать это можно сразу несколькими способами.
Способ 1: контекстное меню
Большинство пользователей для решения данной задачи используют контекстное меню.
- Кликаем правой кнопкой мыши по диапазону, в котором нужно сменить формат. В контекстном меню, которое появится после этих действий, выбираем пункт «Формат ячеек…».
- Открывается окно форматирования. Переходим во вкладку «Число», если оно вдруг было открыто в другой вкладке. Нам нужно переключить параметр «Числовые форматы» со значения «Дата» на нужное пользователю. Чаще всего это значения «Общий», «Числовой», «Денежный», «Текстовый», но могут быть и другие. Тут все зависит от конкретной ситуации и предназначения вводимых данных. После того, как переключение параметра выполнено жмем на кнопку «OK».
После этого данные в выделенных ячейках уже не будут отображаться как дата, а станут показываться в нужном для пользователя формате. То есть, будет достигнута поставленная цель.
Способ 2: изменение форматирования на ленте
Второй способ даже проще первого, хотя почему-то менее популярный среди пользователей.
- Выделяем ячейку или диапазон с форматом даты.
- Находясь во вкладке «Главная» в блоке инструментов «Число» открываем специальное поле форматирования. В нём представлены самые популярные форматы. Выбираем тот, который наиболее подходит для конкретных данных.
- Если среди представленного перечня нужный вариант не был найден, то жмите на пункт «Другие числовые форматы…» в этом же списке.
- Открывается точно такое же окно настроек форматирования, как и в предыдущем способе. В нём расположен более широкий перечень возможного изменения данных в ячейке. Соответственно, и дальнейшие действия тоже будут точно такими же, как и при первом варианте решения проблемы. Выбираем нужный пункт и жмем на кнопку «OK».
После этого, формат в выделенных ячейках будет изменен на тот, который вам нужен. Теперь числа в них не будут отображаться в виде даты, а примут заданную пользователем форму.
Как видим, проблема отображения даты в ячейках вместо числа не является особо сложным вопросом. Решить её довольно просто, достаточно всего нескольких кликов мышкой. Если пользователь знает алгоритм действий, то эта процедура становится элементарной. Выполнить её можно двумя способами, но оба они сводятся к изменению формата ячейки с даты на любой другой.
Еще статьи по данной теме:
Помогла ли Вам статья?
Исправление чисел, превратившихся в даты
При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема — дробные числа превращаются в даты:
Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью — точка), а в Excel у вас российские настройки (где между целой и дробной частью — запятая, а точка используется как разделитель в дате).
При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике — если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату — оно будет конвертировано в дату. Всё, что на дату не похоже — останется текстом.
Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:
- В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
- В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года — самая ранняя дата, с которой может работать Excel — 1 января 1900 г.
- А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:
- То же самое случилось и в ячейке А4, где изначально было число 12.6923. Причем 6923-й год Excel тут нисколько не смутил — для него главное, чтобы дата была не раньше 1900 года. Так что добро пожаловать в далекое будущее:
- Единственное, что распознаётся корректно — это числа без дробной части (число 1000 в ячейке А6). И на том спасибо
Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel — количество дней от 01.01.1900 до текущей даты:
Лечится вся эта история тремя принципиально разными способами.
Способ 1. Заранее в настройках
Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл — Параметры — Дополнительно (File — Options — Advanced):
Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator).
После этого можно смело импортировать данные — проблем не будет.
Способ 2. Формулой
Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:
=—ЕСЛИ(ЯЧЕЙКА(«формат»;A1)=»G»; ПОДСТАВИТЬ(A1;».»;»,»); ТЕКСТ(A1;»М,ГГГГ»))
В английской версии это будет:
=—IF(CELL(«format«;A1)=»G»; SUBSTITUTE(A1;».»;»,»); TEXT(A1;»M,YYYY«))
Логика здесь простая:
- Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата «G» для текста/чисел или «D3» для дат.
- Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE).
- Если в исходной ячейке дата, то выводим её в формате «номер месяца — запятая — номер года» с помощью функции ТЕКСТ (TEXT).
- Чтобы преобразовать получившееся текстовое значение в полноценное число — выполняем бессмысленную математическую операцию — добавляем два знака минус перед формулой, имитируя двойное умножение на -1.
Способ 3. Макросом
Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставляем в нашу книгу новый пустой модуль через меню Insert — Module и копируем туда такой код:
Sub Fix_Numbers_From_Dates()
Dim num As Double, cell As Range
For Each cell In Selection
If Not IsEmpty(cell) Then
If cell.NumberFormat = "General" Then
num = CDbl(Replace(cell, ".", ","))
Else
num = CDbl(Format(cell, "m,yyyy"))
End If
cell.Clear
cell.Value = num
End If
Next cell
End Sub
Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt+F8 или через команду Макросы на вкладке Разработчик (Developer — Macros). Все испорченные числа будут немедленно исправлены.
Ссылки по теме
- Как Excel на самом деле работает с датами и временем
- Замена текста функцией ПОДСТАВИТЬ
- Функция ВПР и числа-как-текст
Skip to content
Это руководство посвящено форматированию даты в Excel и объясняет, как установить вид даты и времени по умолчанию, как изменить их формат и создать собственный.
Помимо чисел, наиболее распространенными типами данных, которые используются в Excel, являются дата и время. Однако работать с ними может быть довольно сложно:
- Одна и та же дата может отображаться различными способами,
- Excel всегда хранит дату в одном и том же виде, независимо от того, как вы оформили её представление.
Более глубокое знание форматов временных показателей поможет вам сэкономить массу времени. И это как раз цель нашего подробного руководства. Мы сосредоточимся на следующих моментах:
- Что такое формат даты
- Формат даты по умолчанию
- Как поменять формат даты
- Как изменить язык даты
- Создание собственного формата отображения даты
- Дата в числовом формате
- Формат времени
- Время в числовом формате
- Создание пользовательского формата времени
- Формат Дата — Время
- Почему не работает? Проблемы и их решение.
Формат даты в Excel
Прежде всего нужно чётко уяснить, как Microsoft Excel хранит дату и время. Часто это – основной источник путаницы. Хотя вы ожидаете, что он запоминает день, месяц и год, но это работает не так …
Excel хранит даты как последовательные числа, и только форматирование ячейки приводит к тому, что число отображается как дата, как время или и то, и другое вместе.
Дата в Excel
Все даты хранятся в виде целых чисел, обозначающих количество дней с 1 января 1900 г. (записывается как 1) до 31 декабря 9999 г. (сохраняется как 2958465).
В этой системе:
- 2 — 2 января 1900 г.
- 44197 — 1 января 2021 г. (потому что это 44 197 дней после 1 января 1900 г.)
Время в Excel
Время хранится в виде десятичных дробей от 0,0 до 0,99999, которые представляют собой долю дня, где 0,0 — 00:00:00, а 0,99999 — 23:59:59.
Например:
- 0.25 — 06:00
- 0.5 — 12:00.
- 0.541655093 это 12:59:59.
Дата и время в Excel
Дата и время хранятся в виде десятичных чисел, состоящих из целого числа, представляющего день, месяц и год, и десятичной части, представляющей время.
Например: 44197.5 — 1 января 2021 г., 12:00.
Формат даты по умолчанию в Excel и как его быстро изменить
Краткий и длинный форматы даты, которые как раз и установлены по умолчанию как основные, извлекаются из региональных настроек Windows. Они отмечены звездочкой (*) в диалоговом окне:
Они изменяются, как только вы меняете настройки даты и времени в панели управления Windows.
Если вы хотите установить другое представление даты и/или времени по умолчанию на своем компьютере, например, изменить их с американского на русское, перейдите в Панель управления и нажмите “Региональные стандарты» > «Изменение форматов даты, времени и чисел» .
На вкладке Форматы выберите регион, а затем установите желаемое отображение, щелкнув стрелку рядом с пунктом, который вы хотите изменить, и выбрав затем наиболее подходящий из раскрывающегося списка:
Если вас не устраивают варианты, предложенные на этой вкладке, нажмите кнопку “Дополнительные параметры» в нижней правой части диалогового окна. Откроется новое окно “Настройка …”, в котором вы переключаетесь на вкладку “Дата” и вводите собственный краткий или длинный формат в соответствующее поле.
Как быстро применить форматирование даты и времени по умолчанию
Как мы уже уяснили, в Microsoft Excel есть два формата даты и времени по умолчанию — короткий и длинный.
Чтобы быстро изменить один из них, сделайте следующее:
- Выберите даты, которые хотите отформатировать.
- На вкладке «Главная» в группе «Число» щелкните маленькую стрелку рядом с полем “Формат числа» и выберите нужный пункт — краткую дату, длинную или же время.
Если вам нужны дополнительные параметры форматирования, либо выберите “Другие числовые форматы» из раскрывающегося списка, либо нажмите кнопку запуска диалогового окна рядом с “Число».
Откроется знакомое диалоговое окно “Формат ячеек”, в котором вы сможете изменить любые нужные вам параметры. Об этом и пойдёт речь далее.
Как изменить формат даты в Excel
Даты могут отображаться разными способами. Когда дело доходит до изменения их вида для данной ячейки или диапазона, самый простой способ — открыть диалоговое окно ”Формат ячеек” и выбрать один из имеющихся там стандартных вариантов.
- Выберите данные, которые вы хотите изменить, или пустые ячейки, в которые вы хотите вставить даты.
- Нажмите
Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Кроме того, вы можете кликнуть выделенные ячейки правой кнопкой мыши и выбрать этот пункт в контекстном меню. - В окне “Формат ячеек» перейдите на вкладку “Число” и выберите “Дата» в списке числовых форматов.
- В разделе “Тип» выберите наиболее подходящий для вас вариант. После этого в поле “Образец» отобразится предварительный просмотр в выбранном варианте оформления.
- Если вас устраивает то, что вы увидели, нажмите кнопку ОК, чтобы сохранить изменение и закрыть окно.
Если, несмотря на ваши усилия, отображение числа, месяца и года в вашей таблице не меняется, скорее всего, ваши данные записаны как текст, и вам сначала нужно преобразовать их в формат даты.
Как сменить язык даты.
Если у вас есть файл, полный иностранных дат, вы, скорее всего, захотите изменить их те, которые используются в вашей стране. Допустим, вы хотите преобразовать американский формат (месяц/день/год) в европейский стиль (день/месяц/год).
Самый простой способ сделать это заключается в следующем:
- Выберите столбец, который вы хотите преобразовать в другой язык.
- Используйте комбинацию
Ctrl + 1, чтобы открыть знакомые нам настройки. - Выберите нужный язык в выпадающем списке «Язык (местоположение)» и нажмите «ОК», чтобы сохранить изменения.
Создание пользовательского формата даты.
Если вам не подходит ни один из стандартных вариантов, вы можете создать свой собственный.
- На листе выделите нужные ячейки.
- Нажмите
Ctrl + 1. - На вкладке Число выберите Все форматы в списке и запишите нужный формат даты в поле Тип. Это показано на скриншоте ниже.
- Щелкните ОК, чтобы сохранить изменения.
При настройке пользовательского формата даты вы можете использовать следующие коды.
| Код | Описание | Пример |
| м | Номер месяца без нуля в начале | 1 |
| мм | Номер месяца с нулем в начале | 01 |
| ммм | Название месяца, краткая форма | Янв |
| мммм | Название месяца, полная форма | Январь |
| ммммм | Первая буква месяца | М (обозначает март и май) |
| д | Номер дня без нуля в начале | 1 |
| дд | Номер дня с нулем в начале | 01 |
| ддд | День недели, краткая форма | Пн |
| дддд | День недели, полная форма | понедельник |
| гг | Год (последние 2 цифры) | 05 |
| гггг | Год (4 цифры) | 2020 |
Можно также использовать дополнительные коды, которые обязательно нужно заключать в квадратные скобки [].
| Код | Пояснение |
| x-sysdate | Системный длинный формат. Месяц в родительном падеже. |
| x-systime | Системное время. |
| x-genlower | Используется родительный падеж в нижнем регистре для любых полных названий месяцев (только для русского языка). Рекомендуется использовать вместе с кодом языка [ru-RU-x-genlower]. |
| x-genupper | Используется родительный падеж в верхнем регистре для любых полных названий месяцев (только на русском языке). Например, [ru-RU-x-genupper]. |
| x-nomlower | Для любых полных названий месяцев применяется именительный падеж в нижнем регистре (только на русском языке): [ru-RU-x-nomlower]. |
Вот как это может выглядеть на примерах:
При создании пользовательского формата в Excel вы можете использовать запятую (,), тире (-), косую черту (/), двоеточие (:) и другие символы.
Как создать собственный формат даты для другого языка
Если вы хотите отображать даты на другом языке, не меняя региональные настройки своего Windows, придётся создать собственный формат и использовать специальный префикс с соответствующим кодом языкового стандарта.
Код языка должен быть заключен в [квадратные скобки] и предваряться знаком доллара ($) и тире (-). Вот несколько примеров:
- [$-419] — Россия
- [$-409] – английский (США)
- [$-422] — Украина
- [$-423] — Беларусь
- [$-407] — Германия
Вы можете найти полный список кодов языков в этом блоге.
Например, вот как это можно настроить для белорусского языка в формате времени -день-месяц-год (день недели) :
Как показать число вместо даты.
Если вы хотите узнать, какое число представляет определенную дату или время, отображаемые в ячейке, вы можете сделать это двумя способами.
1. Диалоговое окно «Форматирование ячеек»
Выделите эту ячейку, нажмите Ctrl + 1, чтобы открыть знакомое нам окно настроек и переключиться на вкладку «Общие».
Если вы просто хотите узнать число, стоящее за датой, ничего не меняя в вашей таблице, то запишите число, которое вы видите в поле «Образец», и нажмите “Отмена», чтобы закрыть окно. Если вы хотите заменить дату числом в текущей ячейке, нажмите ОК.
Вы также можете выбрать формат «Общий» на ленте в разделе «Число». Дата будет тут же заменена соответствующим ей числом.
2. Функции ДАТАЗНАЧ и ВРЕМЗНАЧ
Можно также использовать функцию ДАТАЗНАЧ(), для преобразования даты в соответствующее ей число
=ДАТАЗНАЧ(«20/1/2021»)
Используйте функцию ВРЕМЗНАЧ(), чтобы получить десятичную дробь, представляющую время
=ВРЕМЗНАЧ(«16:30»)
Чтобы узнать дату и время, объедините эти две функции следующим образом:
= ДАТАЗНАЧ(«20/1/2021») & ВРЕМЗНАЧ(«16:30»)
Получится записанное в виде текста число, соответствующее дате-времени.
А если применить операцию сложения –
= ДАТАЗНАЧ(«20/1/2021») + ВРЕМЗНАЧ(«16:30»)
То получим число, которое можно отформатировать в виде даты-времени и с которым можно производить математические операции (найти разность дат и т.п.)
Если вы запишете, скажем, 31.12.1812, то это будет текстовое значение, а не дата. Это означает, что вы не сможете выполнять обычные арифметические операции с ней. Чтобы убедиться в этом, можете ввести формулу =ДАТАЗНАЧ(«31/12/1812») в какую-нибудь ячейку, и вы получите ожидаемый результат — ошибку #ЗНАЧ!.
Формат времени в Excel
Если помните, выше мы уже говорили, что Excel обрабатывает время как часть дня, и время сохраняется как десятичная часть.
Например:
- 00:00:00 сохраняется как 0,0
- 23:59:59 сохраняется как 0,99999
- 06:00 — 0,25
- 12:00 — 0,5
Когда в ячейку вводятся и дата, и время, они сохраняются как десятичное число, состоящее из целой части, представляющего дату, и десятичной части, представляющей время.
Формат времени по умолчанию.
При изменении формата времени в диалоговом окне ”Формат ячеек” вы могли заметить, что один из пунктов начинается со звездочки (*). Это формат времени по умолчанию в вашем Excel. Как и в случае с датой, он определяется региональными настройками Windows.
Чтобы быстро применить формат времени по умолчанию к выбранной ячейке или диапазону ячеек, щелкните стрелку раскрывающегося списка в группе Число на вкладке Главная и выберите Время.
Изменить формат времени по умолчанию, перейдите в Панель управления и перейдите “Региональные стандарты» > «Изменение форматов даты, времени и чисел». Подробно мы этот процесс описали выше, когда рассматривали установки параметров даты по умолчанию.
Десятичное представление времени.
Быстрый способ выбрать десятичное число, представляющее определенное время, — использовать диалоговое окно ”Формат ячеек”.
Просто выберите ячейку, содержащую время, и нажмите Ctrl + 1, чтобы открыть окно настроек. На вкладке “Число” выберите “Общие» в разделе “Категория», и вы увидите десятичную дробь в поле “Образец».
Теперь вы можете записать это число и нажать “Отмена», чтобы закрыть окно. Или же нажмите OK и замените время соответствующим десятичным числом в ячейке.
Фактически это самый быстрый, простой и не требующий формул способ преобразования времени в десятичное число.
Как применить или изменить формат времени.
Microsoft Excel достаточно умен, чтобы распознавать время при вводе и соответствующем форматировании ячейки. Например, если вы наберете 10:30 или 18:40, программа будет воспринимать и отображать это как время в зависимости от установленного по умолчанию формата времени.
Если вы хотите отформатировать некоторые числа как время или применить другой формат времени к существующим значениям, вы можете сделать это с помощью диалогового окна ”Формат ячеек”, как описано ниже.
- На листе Excel выберите ячейки, в которых вы хотите применить или изменить формат времени.
2. Откройте диалоговое окно Формат ячеек , нажав Ctrl + 1или щелкнув значок “Панель запуска диалогового окна» рядом с полем ”Число» на вкладке “Главная”.
3. На вкладке “Число” выберите “Время» в списке и укажите подходящий образец в окне “Тип» .
4. Нажмите OK, чтобы применить выбранное.
Создание пользовательского формата времени.
Хотя Microsoft Excel предоставляет несколько различных форматов времени, вы можете создать свой собственный, который лучше всего подходит для конкретной задачи. Для этого откройте знакомое нам окно настроек, выберите “Все форматы» и введите подходящее время в поле “Тип» .
Созданный вами пользовательский формат времени останется в списке Тип в следующий раз, когда он вам понадобится.
Совет. Самый простой способ создать собственный формат времени – использовать один из существующих в качестве отправной точки. Для этого щелкните “Время» в списке «Категория» и выберите один из предустановленных форматов. После этого внесите в него изменения.
При этом вы можете использовать следующие коды.
| Код | Описание | Отображается как |
| ч | Часы без нуля в начале | 0:35:00 |
| чч | Часы с нулем в начале | 03:35:00 |
| м | Минуты без нуля в начале | 0:0:59 |
| мм | Минуты с нулем в начале | 00:00:59 |
| c | Секунды без нуля в начале | 00:00:9 |
| сс | Секунды с нулем в начале | 00:00:09 |
Когда вы рассчитываете, к примеру, табель рабочего времени, то сумма может превысить 24 часа. Чтобы Microsoft Excel правильно отображал время, выходящее за пределы суток, примените один из следующих настраиваемых форматов времени. На скриншоте ниже – отображение одного и того же времени разными способами.
Пользовательские форматы для отрицательных значений времени
Пользовательские форматы времени, описанные выше, работают только для положительных значений. Если результат ваших вычислений представляет собой отрицательное число, отформатированное как время (например, когда вы вычитаете большее количество времени из меньшего), результат будет отображаться как #####. И увеличение ширины столбца не поможет избавиться от этих решёток.
Если вы хотите обозначить отрицательные значения времени, вам доступны следующие параметры:
- Отобразите пустую ячейку для отрицательных значений времени. Для этого введите точку с запятой в конце формата времени, например [ч]: мм;
- Вывести сообщение об ошибке. Введите точку с запятой в конце формата времени, а затем напишите сообщение в кавычках, например
[ч]: мм; «Отрицательное время»
Если вы хотите отображать отрицательные значения времени именно как отрицательные значения, например -11:15, самый простой способ — изменить систему дат Excel на систему 1904 года. Для этого щелкните Файл> Параметры> Дополнительно, прокрутите вниз до раздела Вычисления и установите флажок Использовать систему дат 1904.
Формат «Дата – Время».
Если нужно показать и дату, и время, вы можете просто объединить в единое целое те форматы, о которых мы говорили выше.
На скриншоте ниже вы видите несколько вариантов, как могут выглядеть ваши значения:
Ничего сложного: сначала описываем дату, затем – время.
Формат даты Excel не работает – как исправить?
Обычно Microsoft Excel очень хорошо понимает даты, и вы вряд ли столкнетесь с какими-либо серьёзными проблемами при работе с ними.
Но если всё же у вас возникла проблема с отображением дня, месяца и года, ознакомьтесь со следующими советами по устранению неполадок.
Ячейка недостаточно широка, чтобы вместить всю информацию.
Если вы видите на листе несколько знаков решетки (#####) вместо даты, то скорее всего, ваши ячейки недостаточно широки, чтобы вместить её целиком.
Решение. Дважды кликните по правой границе столбца, чтобы изменить его размер в соответствии с содержимым. Кроме того, вы можете просто перетащить мышкой правую границу, чтобы установить нужную ширину столбца.
Отрицательные числа форматируются как даты
Во всех современных версиях Excel 2013, 2010 и 2007 решетка (#####) также отображается, когда ячейка, отформатированная как дата или время, содержит отрицательное значение. Обычно это результат, возвращаемый какой-либо формулой. Но это также может произойти, когда вы вводите отрицательное значение в ячейку, а затем представляете эту ячейку как дату.
Если вы хотите отображать отрицательные числа как отрицательные даты, вам доступны два варианта:
Решение 1. Переключитесь на систему 1904.
Перейдите в Файл > Параметры > Дополнительно , прокрутите вниз до раздела При вычислении этой книги , установите флажок Использовать систему дат 1904 и нажмите ОК .
В этой системе 0 – это 1 января 1904 года; 1 – 2 января 1904 г .; а -1 отображается как: -2-янв-1904.
Конечно, такое представление очень необычно и требуется время, чтобы к нему привыкнуть.
Решение 2. Используйте функцию ТЕКСТ.
Другой возможный способ отображения отрицательных дат в Excel – использование функции ТЕКСТ. Например, если вы вычитаете C1 из B1, а значение в C1 больше, чем в B1, вы можете использовать следующую формулу для вывода результата в нужном вам виде:
=ТЕКСТ(ABS(B1-C1);»-ДД ММ ГГГГ»)
Получим результат «-01 01 1900».
Вы можете в формуле ТЕКСТ использовать любые другие настраиваемые форматы даты.
Замечание. В отличие от предыдущего решения, функция ТЕКСТ возвращает текстовое значение, поэтому вы не сможете использовать результат в других вычислениях.
Даты импортированы в Excel как текст
Когда вы импортируете данные из файла .csv или какой-либо другой внешней базы данных, даты часто импортируются как текстовые значения. Они могут выглядеть для вас как обычно, но Excel воспринимает их как текст и обрабатывает соответственно.
Решение. Вы можете преобразовать «текстовые даты» в надлежащий для них вид с помощью функции ДАТАЗНАЧ или функции Текст по столбцам. Подробную информацию см. в следующей статье: Как преобразовать текст в дату.
Мы рассмотрели возможные способы представления даты и времени в Excel. Спасибо за чтение!
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью… 
Как сделать пользовательский числовой формат в Excel — В этом руководстве объясняются основы форматирования чисел в Excel и предоставляется подробное руководство по созданию настраиваемого пользователем формата. Вы узнаете, как отображать нужное количество десятичных знаков, изменять выравнивание или цвет шрифта,… 
7 способов поменять формат ячеек в Excel — Мы рассмотрим, какие форматы данных используются в Excel. Кроме того, расскажем, как можно быстро изменять внешний вид ячеек самыми различными способами. Когда дело доходит до форматирования ячеек в Excel, большинство… 
Как удалить формат ячеек в Excel — В этом коротком руководстве показано несколько быстрых способов очистки форматирования в Excel и объясняется, как удалить форматы в выбранных ячейках. Самый очевидный способ сделать часть информации более заметной — это… 
8 способов разделить ячейку Excel на две или несколько — Как разделить ячейку в Excel? С помощью функции «Текст по столбцам», мгновенного заполнения, формул или вставив в нее фигуру. В этом руководстве описаны все варианты, которые помогут вам выбрать технику, наиболее подходящую…