Что делать если в таблице 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) :

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В ячейке Эксель постоянно появляется месяц года вместо числа.Чем можно помочь мне?

Для изменения формата данной ячейки тебе нужно сделать следующее: выделить ячейку — нажать на нее правой кнопкой мыши, выбрать «формат ячеек», потом выбрать закладку число, потом выбрать из списка слева — «дата», спава увидишь предлагаемые типы дат. Выбери для себя нужный тип даты. Нажми «Ок» и всё.

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

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

Нажать на ячейке правую кнопку мыши, выюрать в контекстном меню «формат ячеек», выбрать вкладку «число» и установить формат на «общий». Если это не поможет. Порыться в настройках приложения.

Excel лепит дату вместо дробного числа. Что делать?

У меня есть в экселевском документе много ячеек, заполненных дробными числами с запятой в качестве разделителя. Например: 2,7
Чтобы работать с ними, мне нужно изменить разделитель целой и дробной частей на точку. Когда я меняю во всём документе при помощи автозамены запятые на точку, то эксель зачем-то на месте 2,7 пишет «2 июля», на месте 3,6 — «3 июня» и т. д. Когда я выделяю ячейки с испорченными таким образом значениями и меняю их формат на «Общий» то вместо даты там появляется какое-то непонятное число (не то, которые было вначале) .
Как с этим бороться?

Голосование за лучший ответ

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

Сначала поменять тип на текст.
потом на число

формат «текстовой» выбирай, потом сотри число и еще раз напиши