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

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

  • автор:

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

При вводе текста в ячейку, например «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 или получить поддержку в сообществах.

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

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 Евгений Довженко

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

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

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

Описание сути проблемы

Посмотрите на следующий скриншот. Я специально несколько чисел записал с одинаковым разделителем дробной части от целой, чтобы показать, как Эксель автоматически форматирует их в даты. Такое конвертирование происходит только в том случае, если само число может являться датой. Например, это будет 6.1995 – такое число отобразится как июн.95 и вернуть ему прежний формат уже проблематично.

Пример превратившихся в даты чисел в Microsoft Excel

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

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

Вариант 1: Предварительное изменение параметров программы

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

  1. Если какая-либо книга уже открыта, перейдите на вкладку «‎Файл», выбрав ее на панели слева. Открытие вкладки Файл для перехода в настройки Microsoft Excel
  2. ‎В главном окне Microsoft Excel на панели слева щелкните по пункту «‎Параметры». Переход в настройки Microsoft Excel для исправления чисел, превратившихся в даты
  3. Выберите раздел с настройками «‎Дополнительно» и снимите галочку с пункта «‎Использовать системные разделители». Поиск нужной настройки для изменения разделителя в Microsoft Excel
  4. В качестве разделителя целой и дробной части ‎‎‎укажите точку или любой другой знак, который в вашем случае является проблемным. Примените изменения и переходите к импорту чисел, проверяя их на отображение. Изменение разделителя для чисел через настройки Microsoft Excel

Вариант 2: Использование формулы

Далее поговорим о том, как исправить уже вставленные числа, которые подверглись автоматическому конвертированию. Для этого понадобятся вспомогательные ячейки, а также использование простой формулы с несколькими видами вычислений. Вот она:

=--ЕСЛИ(ЯЧЕЙКА("формат";A1)="G"; ПОДСТАВИТЬ(A1;".";","); ТЕКСТ(A1;"М,ГГГГ"))

Вставка формулы для исправления превратившихся в даты чисел в Microsoft Excel

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

Пример использования формулы для исправления превратившихся в даты чиселв Microsoft Excel

Полученные данные можно скопировать и заменить оригиналы, а также формула, как и любые другие, доступна для растягивания. Если значения в ячейке нет, вы получите соответствующее уведомление. В случае, когда число не требует преобразования обратно, оно будет показано в оригинальном виде.

Растягивание формулы исправления чисел в Microsoft Excel

Вариант 3: Создание и активация макроса

В завершение хотелось бы обратиться к среде разработки, встроенной в Microsoft Excel, которая называется Visual Basic. С ее помощью будет создан скрипт, проверяющий данные внутри выбранной ячейки и заменяющий разделитесь на нужный с конвертированием в подходящий формат ячейки. Сам код выглядит следующим образом:

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

Для создания макроса с его использованием и дальнейшего применения код нужно будет вставить в модуль и сохранить его для данной книги. Если вы еще не знаете, как осуществляется взаимодействие с Visual Basic, следуйте предложенной инструкции:

  1. На верхней панели выберите вкладку «‎Разработчик» и запустите инструмент «‎Visual Basic». Переход к созданию макроса для исправления чисел в Microsoft Excel
  2. Откройте меню «‎Insert» и выберите из него пункт «‎Module». Создание макроса для исправления чисел в Microsoft Excel
  3. В окно редактирования модуля вставьте указанный выше код и закройте его. Ввод кода для макроса исправления чисел в Microsoft Excel
  4. Перед выходом из Visual Basic обязательно сохраните изменения, кликнув по значку с изображением шестеренки. Сохранение макроса исправления чисел в Microsoft Excel
  5. Остается выделить ячейку для конвертирования, а на той же вкладке «‎Разработчик» выбрать инструмент управления кодом «‎Макрос». Переход к запуску макроса исправления чисел в Microsoft Excel
  6. В списке макросов найдите только что созданный и дважды нажмите по нему левой кнопкой мыши, чтобы применить. Запуск макроса исправления чисел в Microsoft Excel
  7. Возвратитесь к таблице и убедитесь в том, что ‎‎‎‎‎‎число в ячейке было успешно исправлено на нужный формат. Повторите то же самое с остальными, а для быстрого применения макроса можете использовать сочетание клавиш Alt + F8. Проверка действия макроса исправления превратившихся в даты чисел в Microsoft Excel

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

Читайте также в Комьюнити:

  • Как использовать функцию ДЛСТР в Microsoft Excel
  • Открытие Microsoft Excel в разных окнах
  • Как отобразить день недели по дате в Microsoft Excel

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

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

При импорте в 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 не будет опубликован. Обязательные поля помечены *