Формат ячейки меняет отображение числа, но не превращает строку в дату. Если значение импортировано как текст, сортировка идёт лексикографически, разность дат даёт ошибку, а сводная таблица не группирует поле по месяцам. Сначала определите тип и шаблон исходных строк.
Не запускайте замену разделителей сразу на всём столбце. Сделайте копию листа или исходного столбца и возьмите выборку: начало, середину, конец, пустые значения и даты, где число дня больше 12. Они показывают реальный порядок частей лучше, чем неоднозначное 03/04/2026.
Внешний вид не доказывает тип
| Значение в ячейке | Что это может быть | Контроль |
|---|---|---|
| 31.12.2026 | Текст или отформатированное число | ISNUMBER и ISTEXT |
| 46022 | Серийное число или обычный код | Назначение столбца и формат |
| '01.02.2026 | Текст с апострофом ввода | Строка формул |
| 2026-02-01 | ISO-подобная строка | Явный шаблон импорта |
| 03/04/2026 | DMY или MDY | Источник и локаль, не догадка |
| 31.02.2026 | Ошибочная календарная дата | Оставить ошибкой, не исправлять молча |
Выравнивание по левому краю и зелёный треугольник полезны как подсказки, но не заменяют проверку. Пользователь мог изменить alignment вручную, а фоновые правила ошибок — быть выключены. Формулы =ISNUMBER(A2) и =ISTEXT(A2) дают прямой ответ о текущем типе.
DATEVALUE подходит только после проверки локали
=ISNUMBER(A2) // уже числовая дата?
=ISTEXT(A2) // действительно строка?
=DATEVALUE(A2) // разбор по настройкам системы
// Для строго фиксированного текста dd.mm.yyyy:
=DATE(--RIGHT(A2,4),--MID(A2,4,2),--LEFT(A2,2))
// Power Query M с явными правилами:
Date.FromText([date_text], [Format="dd.MM.yyyy", Culture="ru-RU"])Имена функций и разделители аргументов могут отображаться иначе в локализованной версии Excel, но принцип тот же. Microsoft указывает, что DATEVALUE использует системные настройки даты; поэтому один и тот же неоднозначный текст на двух компьютерах способен интерпретироваться по-разному.
Выбор метода зависит от происхождения данных
- Зелёный индикатор подходит для небольшого диапазона с очевидным форматом.
- DATEVALUE удобен для строк, уже понятных текущей локали.
- Явная формула DATE из частей подходит для фиксированного dd.mm.yyyy.
- Text Import Wizard позволяет задать DMY, MDY или YMD при импорте файла.
- Power Query лучше для повторяемой загрузки и фиксирует Format и Culture в шаге.
Замена точки на слэш не решает порядок дня и месяца. Удаление всех пробелов тоже опасно: неразрывный пробел, время или пояс могут быть частью контракта источника. Нормализация должна следовать описанному формату, а неожиданные строки уходят в отдельный список ошибок.
Неоднозначные даты останавливают массовую конверсию
Строка 03/04/2026 корректна и как 3 апреля, и как 4 марта. Определить смысл по ней невозможно. Возьмите метаданные выгрузки, документацию источника или строки вроде 13/04/2026. Если подтверждения нет, запросите формат у владельца данных и не публикуйте отчёт с угаданными месяцами.
Результат проверяется числами, а не цветом
- Посчитать количество исходных непустых строк.
- Посчитать успешно полученные числовые даты и отдельные ошибки.
- Сверить минимум и максимум с ожидаемым периодом отчёта.
- Проверить вручную однозначные 13-е и 31-е числа.
- Отсортировать по новой дате и найти неожиданные годы.
- Сравнить несколько строк с исходной системой или файлом.
Числовой результат затем получает обычный формат даты. Не заменяйте исходный столбец, пока контрольные суммы не сошлись: сохраните raw_date и parsed_date рядом. Это позволяет исправить правило и повторить преобразование без повторного запроса исходных данных.
Время и пустые строки разбираются отдельно
DATEVALUE игнорирует время в текстовом аргументе, поэтому для timestamp нужен другой явно выбранный тип и правило. Пустая строка, пробел, текст NULL и реальное пустое значение также не равны друг другу. В Power Query задайте политику null до преобразования, чтобы ошибка источника не превратилась в фиктивную дату.
Повторяемый импорт фиксирует культуру
Для еженедельного файла ручная кнопка становится источником расхождений. Сохраните Power Query с явным Format и Culture, добавьте столбец ошибок и тестовую строку с днём больше 12. Для разбора достаточно нескольких обезличенных исходных значений, версии Excel, региональных настроек и описания источника; весь рабочий файл с персональными данными передавать не нужно.