Ошибка в формате даты в ячейке

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

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

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

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

Вариант 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, а также ограничить их автоматическое преобразование в дальнейшем. Выбор варианта остается за вами, после чего следует только повторить инструкцию.

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

Причины и пути решения

Для решения проблемы нужно понимать, почему Эксель не меняет формат даты при любых попытках. К возможным причинам стоит отнести:

1. Конфликт форматов и блокировка стиля ячейки.
2. Несоответствие заданных стилей Excel.
3. Неправильное выполнение работы.
4. Временные сбои в работе программы.
5. Неправильные региональные настройки.

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

Способ №1:

• Выберите столбец даты в Excel.
• Зайдите в раздел «Данные» и выберите «Text to Columns».

• На первом экране поставьте переключатель «delimited».
• Кликните на кнопку «Далее».
• Снимите флажки-разделители и кликните на кнопку «Далее».
• В секции «Формат данных столбца» выберите «Дату».

• Жмите «Готово».

Решение №2:

1. Выделите ячейки, где не меняется дата в Экселе.
2. Жмите на сочетание Ctrl+1.
3. В разделе «Формат ячеек» откройте вкладку «Число».
4. В перечне «Категория» выберите требуемый раздел.
5. Перейдите в «Тип» и укажите подходящий вариант форматирования в Excel. Для предварительного просмотра перейдите в раздел «Образец». Если сделанный вариант не устраивает, можно внести изменения и снова выполнить проверку — меняется отображение или нет.

Метод №3:

1. Жмите нужную ячейку, где формат даты в Эксель не меняется.
2. Кликните сочетание Ctrl+Shift+#.

Способ №4:

1. Используйте формулу: =DATE(MID(A1,FIND(«,»,A1)+1,5),MATCH(LEFT(A1,3),{«Jan»;»Feb»;»Mar»;»Apr»;»May»;»Jun»;»Jul»;»Aug»;»Sep»;»Oct»;»Nov»;»Dec»},0),MID(SUBSTITUTE(A1,»,»,»   «),5,5)) для перевода в REAL.
2. Проверьте, что отображение в Excel меняется с учетом предпочтений.

Такой вариант может сработать в случае неправильных региональных настроек и невозможности изменения форматирования для дня, строки и времени. Представленную форму Excel можно сделать проще, если представить больше сведений о формате ввода. Но даже в таком случае все должно работать корректно. Кроме того, при желании сохранить часть времени можно воспользоваться формулой +RIGHT(A1,11).

Решение №5:

1. Загрузите лист Excel, где не меняется стиль отображения, на Гугл Док.
2. Откройте файл с помощью электронной Гугл-таблицы.
3. Выберите весь столбец с информацией.
4. Установите тип формата для даты. На данном этапе можно выбрать любой подходящий вариант.
5. Закройте электронную таблицу Гугл в формате .xlsx.

Метод №6:

1. Копируйте несколько ячеек, где не меняется форматирование даты в Excel, в блокнот.
2. Жмите на Clear All.
3. Вставьте содержимое ячейки в блокнот обратно в те же ячейки.
4. Установите их в качестве текущего дня в нужном варианте и т. д.

Как поменять формат в Excel

Распространенная причина, почему софт не меняет дату в Экселе — неправильные действия пользователя. Для решения проблемы необходимо правильно сделать эту работу. Здесь существует несколько путей.

Вариант №1:

1. Зайдите во вкладку «Главная».
2. Перейдите в раздел «Число».
3. Жмите на кнопку вызова диалогового окна (находится над строчкой «Число»).
4. В списке «Категория» выберите нужный пункт.
5. Перейдите в раздел «Тип» и установите нужный вариант отображения в Excel.

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

Вариант №2:

1. Зайдите во вкладку XLTools.
2. В группе «Дата и время» откройте выпадающий перечень.
3. Войдите в раздел «Изменить формат даты и времени».
4. Задайте область поиска. Здесь указывается выбранный диапазон, рабочий лист, книга, открытые книги.
5. Выберите подходящий вариант из перечня, принятый в стране и на нужном языке. По желанию задайте пользовательский формат.
6. Кликните на кнопку «Готово».

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

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

Отличного Вам дня!

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

При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема — дробные числа превращаются в даты:

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

При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике — если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату — оно будет конвертировано в дату. Всё, что на дату не похоже — останется текстом.

Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:

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

  

• То же самое случилось и в ячейке А4, где изначально было число 12.6923. Причем 6923-й год Excel тут нисколько не смутил — для него главное, чтобы дата была не раньше 1900 года. Так что добро пожаловать в далекое будущее:

  
• Единственное, что распознаётся корректно — это числа без дробной части (число 1000 в ячейке А6). И на том спасибо

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