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

Проблема отображения числа в формате даты в Excel

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

Решение проблемы отображения числа как даты

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

Способ 1: контекстное меню

Большинство пользователей для решения данной задачи используют контекстное меню.

Кликаем правой кнопкой мыши по диапазону, в котором нужно сменить формат. В контекстном меню, которое появится после этих действий, выбираем пункт «Формат ячеек…».

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

Способ 2: изменение форматирования на ленте

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

1. Выделяем ячейку или диапазон с форматом даты.
2. Находясь во вкладке «Главная» в блоке инструментов «Число» открываем специальное поле форматирования. В нём представлены самые популярные форматы. Выбираем тот, который наиболее подходит для конкретных данных.
3. Если среди представленного перечня нужный вариант не был найден, то жмите на пункт «Другие числовые форматы…» в этом же списке.

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

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

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

При импорте в 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 и копируем туда такой код:

Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer — Macros) . Все испорченные числа будут немедленно исправлены.

Дата вместо числа в Excel: исправляем разными способами

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

• Возвращаем отображение чисел вместо дат
  • Метод 1: используем контекстное меню
  • Метод 2: пользуемся инструментами на ленте
  • Метод 3: жмем горячие клавиши

  Возвращаем отображение чисел вместо дат

  На самом деле все достаточно просто – вместо числа в ячейке появляется дата по причине того, что для нее задан такой формат (“Дата”). Следовательно, чтобы мы видели именно числовое значение, требуется всего лишь сменить формат на тот, который нам нужен. Справиться с этим можно по-разному.

  Метод 1: используем контекстное меню

  Данное меню пользуется наибольшей популярностью среди пользователей Excel для решения множества задач. В нашем случае нам оно тоже может помочь.

  1. Правой кнопкой мыши кликаем по отдельной ячейке или заранее выделенному диапазону ячеек (например, с помощью зажатой левой кнопки мыши). В появившемся меню выбираем команду “Формат ячеек”.
  2. Мы должны оказаться в окне форматирования во вкладке “Число”. В поле “Числовые форматы” меняем формат “Дата” на “Числовой” (в некоторых случаях, если требуется, можно выбрать “Общий”, “Денежный” или “Текстовый”). Обращаем вниманием на область по надписью “Образец” – именно так будет отображаться содержимое ячейки после применения настроек. По готовности нажимаем OK.Примечание: некоторые выбранные форматы можно настраивать, например, для числового можно задать количество десятичных знаков, а также включить/отключить разделитель групп разрядов.
  3. Таким образом, нам удалось получить в выбранной ячейке (диапазоне ячеек) конкретное число вместо даты.

  Метод 2: пользуемся инструментами на ленте

  Этот способ используется реже, чем первый, несмотря на то, что является более простым и реализуется буквально в один клик.

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

  Метод 3: жмем горячие клавиши

  Этот способ используется редко, так как для его реализации нужно запомнить специальную комбинацию клавиш, и по сути, он является альтернативой использованию контекстного меню, позволяя обойтись без него.

  Все, что нам нужно сделать – выбрать ячейку (или выделить диапазон), после чего нажать на клавиатуре сочетание Ctrl+1, после чего откроется уже знакомое окно форматирования.

  

  Заключение

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

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

  В этом руководстве я расскажу о нескольких методах решения различных случаев преобразования даты в числовой или текстовый формат в Excel.
  

  • 1. Преобразование даты в текст
    1.1 Преобразование даты в текст в формате мм / дд / гггг
    1.2 Преобразование даты в текст в формате mmddyyyy
    1.3 Преобразование даты в текст в других форматах
  • 2. Преобразование даты в число
    2.1 Преобразование даты в число в 5-значном формате
    2.2 Преобразование даты в число в формате mmddyyyy
  • 3. Преобразование даты в месяц / день / год или другие форматы даты с помощью щелчков мышью.

  1. Преобразование даты в текст

  В этом разделе представлены методы преобразования даты в текст в Excel.

  Чтобы преобразовать дату в текст, вам нужна всего одна формула.

  Формула: = ТЕКСТ (дата, «формат_даты»)

  Ссылка: дата: ячейка с датой, которую вы хотите преобразовать в текст
  Формат даты: формат, который вы хотите отобразить после преобразования даты в текст

  В следующих случаях я расскажу вам, как использовать эту формулу.

  1.1 Преобразование даты в текст в формате мм / дд / гггг

  Например, для преобразования даты в ячейке A3 в мм / дд / гггг используйте формулу
  = ТЕКСТ (A3; «мм / дд / гггг»)
  нажмите Enter ключ
  

  Если вы хотите преобразовать дату в ячейке A8 в дд / мм / гггг, используйте эту формулу
  = ТЕКСТ (A8; «дд / мм / гггг»)
  нажмите Enter ключ
  

  1.2 Преобразование даты в текст в формате mmddyyyy или ddmmyyyy

  Если вы хотите преобразовать дату в ячейке A13 в текст, но в формате mmddyyyy, введите эту формулу
  = ТЕКСТ (A13, «ммддйгг»)
  нажмите Enter ключ
  

  Если вы хотите преобразовать дату в ячейке A18 в текст в формате ddmmyyyy, введите эту формулу
  = ТЕКСТ (A18, «ддммйгг»)
  нажмите Enter ключ
  

  1.3 Преобразование даты в текст в других форматах

  Фактически, в любой текстовый формат, в который вы хотите преобразовать дату, просто введите нужный формат в кавычки в формуле.

  Дата (A3)

  12/23/2019

  Формула	= ТЕКСТ (A3; «мм-дд-гггг»)	= ТЕКСТ (A3; «мм / дд»)	= ТЕКСТ (A3; «дд»)	= ТЕКСТ (A3; «д / м / г»)
  Результат	23-12-2019	23/12	23	23/12/19

  16 новых функций и возможностей дат решают 90% задач по обработке дат в Excel за вас.

  ◆ Добавьте или вычтите год / месяцы / дни / недели до даты, нажми для деталей.

  ◆ Добавить или вычесть секунды / минуты / часы к дате, подробнее.

  ◆ Считайте выходные / будние дни между двумя датами. подробнее.

  ◆ Преобразование времени в десятичные секунды / муниты / часы. нажми для деталей.

  ◆ Преобразование нескольких форматов даты в стандартный формат даты США. нажми для деталей.

  2. Преобразование даты в число

  В этом разделе я предлагаю методы преобразования даты в число в 5-значном формате mmddyyyy.

  2.1 Преобразование даты в число в 5-значном формате

  Если вы хотите преобразовать дату в число в 5-значном формате, выполните следующие действия:

  1. Щелкните правой кнопкой мыши ячейку, содержащую дату, которую вы хотите преобразовать в число, и в контекстном меню выберите Формат ячеек общий.
  

  2. в Формат ячеек диалог ， под Число вкладка ， выберите Общие из панели Категория.
  

  3. Нажмите OK. Дата в выбранной ячейке преобразована в числовую строку в формате ммддыггг.
  

  2.2 Преобразование даты в число в формате mmddyyyy или ddmmyyyy

  Если вы хотите преобразовать дату в числовую строку в формате mmddyyyy или ddmmyyyy, вы также можете применить функцию Format Cells.

  1. Щелкните правой кнопкой мыши ячейку, содержащую дату, которую вы хотите преобразовать в число, и в контекстном меню выберите Формат ячеек общий.

  2. в Формат ячеек диалога под Число вкладка, выберите На заказ из Категория панель, затем перейдите в правый раздел, введите ММДДГГГГ в Тип текстовое окно.
  

  3. Нажмите OK. Дата в выбранной ячейке преобразована в числовую строку в формате ммддйгг
  

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

  Время

  2/23/2019

  Форматировать ячейку как настраиваемую	ддммгггг	ддммгг	ммгггг	ггггммдд
  Монитор	23022019	230219	022019	20190223

  3. Преобразование даты в месяц / день / год или другие форматы даты с помощью щелчков мышью.

  Если вы хотите быстро преобразовать дату в месяц, день, год или другие форматы даты, Применить форматирование даты полезности Kutools for Excel будет хорошим выбором для использования.

  Попробуйте! Попрощайтесь с мышиной рукой и шейным спондилезом

  300 передовых инструментов Kutools for Excel решают 80% Задачи Excel за считанные секунды, избавят вас от тысяч щелчков мышью.

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

  Ежедневно повышайте производительность на 80% для более чем 110000 высокоэффективных людей, включая вас.

  Больше не мучиться мучительными формулами и VBA, дайте своему мозгу отдых и радостное рабочее настроение.

  30 — дневная бесплатная пробная версия со всеми функциями, 30 день назад деньги без причины.

  Лучшее тело создает лучшую жизнь.

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

  2. Нажмите Кутулс > Формат > Применить форматирование даты.
  

  3. в Применить форматирование даты диалоговом окне выберите формат даты, который вы хотите использовать в Форматирование даты панель, тем временем преобразованный результат будет предварительно показан справа предварительный просмотр панель.
  

  4. Нажмите Ok, то выбранные вами даты будут преобразованы в выбранный вами формат даты.
  

  Преобразование даты в несколько форматов даты Индивидуальность с помощью щелчков

  Скачать образец файла

  

  How To Make You Stand Out From Crowd, Easily Work And Leisurely Enjoy Life？

  Kutools for Excel will averagely improve your efficiency every day, make you have much time to accompany with family and have fun in life.

  advanced tools solve Excel problems, you no longer struggle to search solutions, more easily complete work.

  Easily handle Excel scenarios, save at least 1 hour for you every day.

  Become an Excel expert in 5 minutes, gain peoples’ recognition and opportunity for promotion.

  One-click utilities handle batch processing, let you avoid thousands of mouse-clicks, say good-bye to mouse hand.

  Get rid of rote memorizing formulas and complex VBA, give a rest to your brain.

  110000+ high efficiency officers and 300+ world renowned companies’ choice.

  Only take $39 but much worth than $4000 training of other people

  days free trial without any limitation, full money back in -day without any reasons.

  Читать:

  Как найти массу электрона