Как заменить дату на число в excel
Перейти к содержимому

Как заменить дату на число в excel

  • автор:

Как заменить дату на число в excel

Excel преобразовывает числа в даты. Причины и способы решения.

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

  1. Несоответствие регионального формата исходных данных региональным настройкам вашей операционной системы. В исходных данных в качестве десятичного разделителя используется точка (американский формат), а у вас – региональные настройки, в которых десятичным разделителем является запятая, а точка воспринимается как разделитель отдельных составляющих даты – день, месяц, год (например, 17.09.202.
  2. Особое поведение 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 Евгений Довженко

Пользовательское форматирование даты

При вводе текста в ячейку, например «2/2″, Excel предполагает, что это дата, и форматирует ее в соответствии с параметром даты по умолчанию в панель управления. Excel может отформатировать его как «2 февраля». Если изменить параметр даты в панель управления, формат даты по умолчанию в Excel изменится соответствующим образом. Если вам не нравится формат даты по умолчанию, вы можете выбрать другой формат даты в Excel, например «2 февраля 2012 г.» или «02.02.12″. Вы также можете создать собственный пользовательский формат в классическом приложении Excel.

Выбор из списка форматов даты

Выбор даты в списке

  1. Выделите ячейки, которые нужно отформатировать.
  2. Нажмите сочетание клавиш CTRL+1.
  3. В диалоговом окне Формат ячеек откройте вкладку Число.
  4. В списке Категория щелкните Дата.

Примечание: Форматы даты, начинающиеся со звездочки (*), будут изменены при изменении формата отображения даты и времени на панели управления. Форматы без звездочки останутся без изменений.

Совет: В ваших ячейках есть числа, отображающиеся как #####? Скорее всего, ячейка недостаточно широка, чтобы отобразить все число. Дважды щелкните правую границу столбца, содержащего ячейки с символами #####. Размер столбца изменится в соответствии с длиной чисел. Вы можете также перетащить правую границу столбца до необходимой ширины.

Создание пользовательского формата даты

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

  1. Выделите ячейки, которые нужно отформатировать.
  2. Нажмите сочетание клавиш CTRL+1.
  3. В диалоговом окне Формат ячеек откройте вкладку Число.
  4. В списке Категория щелкните Дата и выберите нужный формат даты в поле Тип. Этот формат можно изменить на последнем шаге ниже.

Выбор даты в списке

Для отображения

Используйте код

Месяцев в виде чисел от 1 до 12

Месяцев в виде чисел от 01 до 12

Месяцев в виде «янв», . «дек»

Месяцев в виде «январь», . «декабрь»

Месяцев в виде первой буквы месяца

Дней в виде чисел от 1 до 31

Дней в виде чисел от 01 до 31

Дней в виде «Пн», . «Вс»

Дней в виде «понедельник», . «воскресенье»

Лет в виде чисел от 00 до 99

Лет в виде чисел от 1900 до 9999

Если вы изменяете формат, включающий значения времени, и используете «m» сразу после кода «h» или «hh» или непосредственно перед кодом «ss», Excel отображает минуты вместо месяца.

Советы по отображению дат

  • Чтобы быстро применить формат даты по умолчанию, щелкните ячейку с датой и нажмите сочетание клавиш CTRL+SHIFT+#.
  • Если после применения к ячейке формата даты в ней отображаются символы #####, вероятно, ширина ячейки недостаточна для отображения числа полностью. Дважды щелкните правую границу столбца, содержащего ячейки с символами #####. Размер столбца изменится в соответствии с длиной чисел. Вы можете также перетащить правую границу столбца до необходимой ширины.
  • Чтобы быстро ввести текущую дату, выберите любую пустую ячейку на листе, нажмите сочетание клавиш CTRL+; (точка с запятой), а затем, если необходимо, нажмите клавишу ВВОД.
  • Чтобы ввести дату, которая будет обновляться до текущей даты при каждом открытии листа или перерасчете формулы, введите в пустую ячейку =СЕГОДНЯ() и нажмите клавишу ВВОД.

При вводе текста в ячейку, например «2/2″, Excel предполагает, что это дата, и форматирует ее в соответствии с параметром даты по умолчанию в панель управления. Excel может отформатировать его как «2 февраля». Если изменить параметр даты в панель управления, формат даты по умолчанию в Excel изменится соответствующим образом. Если вам не нравится формат даты по умолчанию, вы можете выбрать другой формат даты в Excel, например «2 февраля 2012 г.» или «02.02.12″. Вы также можете создать собственный пользовательский формат в классическом приложении Excel.

Выбор из списка форматов даты

Выбор даты в списке

  1. Выделите ячейки, которые нужно отформатировать.
  2. Нажмите клавиши CONTROL+1 или COMMAND+1.
  3. В диалоговом окне Формат ячеек откройте вкладку Число.
  4. В списке Категория щелкните Дата.

Примечание: Форматы даты, начинающиеся со звездочки (*), будут изменены при изменении формата отображения даты и времени на панели управления. Форматы без звездочки останутся без изменений.

Совет: В ваших ячейках есть числа, отображающиеся как #####? Скорее всего, ячейка недостаточно широка, чтобы отобразить все число. Дважды щелкните правую границу столбца, содержащего ячейки с символами #####. Размер столбца изменится в соответствии с длиной чисел. Вы можете также перетащить правую границу столбца до необходимой ширины.

Создание пользовательского формата даты

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

  1. Выделите ячейки, которые нужно отформатировать.
  2. Нажмите клавиши CONTROL+1 или COMMAND+1.
  3. В диалоговом окне Формат ячеек откройте вкладку Число.
  4. В списке Категория щелкните Дата и выберите нужный формат даты в поле Тип. Этот формат можно изменить на последнем шаге ниже.

Выбор даты в списке

Для отображения

Используйте код

Месяцев в виде чисел от 1 до 12

Месяцев в виде чисел от 01 до 12

Месяцев в виде «янв», . «дек»

Месяцев в виде «январь», . «декабрь»

Месяцев в виде первой буквы месяца

Дней в виде чисел от 1 до 31

Дней в виде чисел от 01 до 31

Дней в виде «Пн», . «Вс»

Дней в виде «понедельник», . «воскресенье»

Лет в виде чисел от 00 до 99

Лет в виде чисел от 1900 до 9999

Если вы изменяете формат, включающий значения времени, и используете «m» сразу после кода «h» или «hh» или непосредственно перед кодом «ss», Excel отображает минуты вместо месяца.

Советы по отображению дат

  • Чтобы быстро применить формат даты по умолчанию, щелкните ячейку с датой и нажмите сочетание клавиш CTRL+SHIFT+#.
  • Если после применения к ячейке формата даты в ней отображаются символы #####, вероятно, ширина ячейки недостаточна для отображения числа полностью. Дважды щелкните правую границу столбца, содержащего ячейки с символами #####. Размер столбца изменится в соответствии с длиной чисел. Вы можете также перетащить правую границу столбца до необходимой ширины.
  • Чтобы быстро ввести текущую дату, выберите любую пустую ячейку на листе, нажмите сочетание клавиш CTRL+; (точка с запятой), а затем, если необходимо, нажмите клавишу ВВОД.
  • Чтобы ввести дату, которая будет обновляться до текущей даты при каждом открытии листа или перерасчете формулы, введите в пустую ячейку =СЕГОДНЯ() и нажмите клавишу ВВОД.

Когда вы вводите в ячейку что-то вроде 2/2 , Excel в Интернете думает, что вы вводите дату и отображает ее как 2 февраля. Вы можете изменить дату на более длинную или короткую.

Чтобы дата выводилась в кратком формате (например, «02.02.2013»), выберите ячейку и щелкните Главная > Числовой формат > Краткий формат даты. Чтобы использовать более длинный формат, например «суббота, 2 февраля 2013 г.», выберите значение Длинный формат даты.

Советы по отображению дат

  • Если после применения к ячейке формата даты в ней выводятся символы #####, вероятно, ее ширины недостаточно для отображения числа полностью. Попробуйте перетащить границу столбца, содержащего ячейки с #####. Размер столбца изменится в соответствии с длиной чисел.
  • Чтобы ввести дату, которая будет обновляться до текущей даты при каждом открытии листа или перерасчете формулы, введите в пустую ячейку =СЕГОДНЯ() и нажмите клавишу ВВОД.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Форматирование чисел в виде значений даты и времени

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

В этой статье

  • Отображение чисел в виде дат или времени
  • Создание пользовательского формата даты или времени
  • Советы по отображению дат или времени

Отображение чисел в виде дат или времени

Вы можете форматировать даты и время по мере ввода. Например, если ввести 2/2 в ячейке, Excel автоматически интерпретирует это как дату и отобразит в ячейке 2 февраля . Если это не то, что вы хотите( например, если вы предпочитаете показывать 2 февраля 2009 г. или 02.02.09 в ячейке), вы можете выбрать другой формат даты в диалоговом окне Формат ячеек , как описано в следующей процедуре. Аналогичным образом, если ввести 9:30 a или 9:30 p в ячейке, Excel будет интерпретировать это как время и отображать 9:30 или 21:30. Опять же, можно настроить способ отображения времени в диалоговом окне Формат ячеек .

Кнопка вызова диалогового окна в группе

    На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число.

Format Cells dialog box

Вы также можете нажать клавиши CTRL+1 , чтобы открыть диалоговое окно Формат ячеек .
В списке Категория щелкните Дата или Время.

Примечание: Форматы даты и времени, начинающиеся со звездочки (*), реагируют на изменения региональных параметров даты и времени, указанные в панель управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.

В диалоговом окне

Чтобы отобразить даты и время в формате других языков, выберите нужный параметр языка в поле Языковой стандарт (расположение).

В диалоговом окне

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

Создание пользовательского формата даты или времени

Кнопка вызова диалогового окна в группе

  1. На вкладке Главная нажмите кнопку вызова диалогового окна рядом с именем группы Число.

Для отображения

Используйте код

Месяцев в виде чисел от 1 до 12

Месяцев в виде чисел от 01 до 12

Исправление чисел, превратившихся в даты

Испорченные данные

При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема — дробные числа превращаются в даты:
Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью — точка), а в Excel у вас российские настройки (где между целой и дробной частью — запятая, а точка используется как разделитель в дате).
При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике — если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату — оно будет конвертировано в дату. Всё, что на дату не похоже — останется текстом. Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:

  • В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
  • В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года — самая ранняя дата, с которой может работать Excel — 1 января 1900 г.
  • А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:

Неправильная дата

Еще одна неправильная дата

Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel — количество дней от 01.01.1900 до текущей даты:

Неправильное число после изменения формата

Лечится вся эта история тремя принципиально разными способами.

Способ 1. Заранее в настройках

Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл — Параметры — Дополнительно (File — Options — Advanced) :

Настройка разделителей в окне параметров Excel

Снимаем флажок Использовать системные разделители (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 на самом деле работает с датами и временем
  • Замена текста функцией ПОДСТАВИТЬ
  • Функция ВПР и числа-как-текст

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *