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

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

При импорте в 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) :

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

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

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. Вы также можете настроить способ времени в диалоговом окне Формат ячеек.

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

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

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

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

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

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

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

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

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

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

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 Еще. Меньше

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Очень часто можно столкнуться с такой проблемой: когда вы вставляете в ячейки 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 Евгений Довженко