Даты Excel: серийные номера, текст, похожий на дату, и как это исправить

1. Дата – это число, а вот это число

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

=N(A2)                       46027
=TEXT(A2,"yyyy-mm-dd")  2026-01-05
=TEXT(1,"yyyy-mm-dd")   1900-01-01

В ячейке 46 027 человек. Это отсчет дней от начала системы, где день 1 — это 1 января 1900 года. Каждая дата, которую вы когда-либо вводили в Excel, представляет собой такое же число, и из него вытекает все удобное в датах.

Вычитание работает, потому что это вычитание. Первый и последний заказы в таблице — 5 января и 25 мая, и промежуток между ними не является специальным расчетом дат:

=B17-B2      140

140 дней, прямая арифметика с двумя целыми числами. Сортировка работает по той же причине, как и фильтрация «после 1 марта», а также группировка в сводной таблице. Все это — обработка чисел в шляпе в форме даты, поэтому все, что на самом деле не является числом, молча теряет все эти способности.

2. Двухсекундный тест на текст, выдающий себя за свидание

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

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

Бегать =N(A2). Он возвращает числовое значение ячейки или 0, если в ней нет числа. Реальная дата возвращает что-то в районе сорока шести тысяч. Текст возвращает 0. Именно его и следует использовать.

Попробуйте сгруппировать его в сводку. Щелкните правой кнопкой мыши дату в сводной таблице, и если Группа выделен серым цветом, столбец является текстовым. Часто это первый симптом, который замечают люди: сводная таблица, которая не будет группироваться по месяцам почти никогда не возникает проблем с шарниром.

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

=YEAR("19 Jan 2026")     2026
=MONTH("banana")       #VALUE!

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

3. Три способа конвертировать текстовый столбец в реальные даты

В порядке возрастания того, с каким беспорядком они справляются.

DATEVALUE, в новом столбце. Принимает текст и возвращает серийный номер, который затем форматируется как дата.

=DATEVALUE("2026-01-19")     formats to  2026-01-19

Быстро и чисто, и он терпит неудачу с #VALUE! обо всем, что он не может прочитать, что является скорее полезной информацией, чем неудобством. Эти неудачи — это строки, на которые человеку нужно обратить внимание.

Текст в столбцы. Выберите столбец, «Данные», «Текст в столбцы», «Далее», «Далее», затем на третьем шаге выберите Дата и выберите порядок написания текста: DMY, MDY, YMD. Заканчивать. Это тот, который обрабатывает столбец, записанный в порядке страны, отличной от вашей, потому что вы явно указываете ему, что означает текст, вместо того, чтобы позволить Excel догадываться.

Восстановите его из частей. Если текст действительно неправильный, вытащите его части и передайте DATE:

=DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2))

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

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

4. 01.05.2026 и двузначный год

Перед пояснением: приходит файл с 01.05.2026 в графе дата. Скажи, какой это день.

Вы не можете этого знать, и Excel тоже. В большинстве стран мира это 5 января, а в Соединенных Штатах — 1 мая, и в тексте нет информации о том, какая конвенция его написала. Excel решает эту проблему, используя региональные настройки Окна того, кто открывает файл. На машине, на которой была написана эта страница, ответ был:

=DATEVALUE("05/01/2026")     2026-05-01

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

Двузначные годы имеют фиксированную границу, а не региональную, и стоит точно знать, где она находится, поскольку она перемещает значение на сто лет, а не на несколько месяцев.

=DATEVALUE("1/1/29")     2029-01-01
=DATEVALUE("1/1/30")     1930-01-01
=DATEVALUE("1/1/31")     1931-01-01

29 лет и ниже — двадцать первый век; 30 и выше становятся двадцатыми. Дата рождения 01.01.30 в файле клиента — 1930 год, что, вероятно, правильно, а дата контракта 1 января 30 года — 1930 год, что, безусловно, неверно. Ничто вас не предупреждает.

Защита для обеих проблем одинакова и скучна: настаивайте на четырехзначном году и однозначном формате в момент создания файла и предпочитайте yyyy-mm-dd, который правильно сортируется как текст и читается везде одинаково. Если вы не можете контролировать источник, используйте «Текст по столбцам» и объявите порядок самостоятельно.

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

5. Время – это часть после запятой.

Если целое число — это день, то дробь — это часть дня. Половина часа — полдень.

=TEXT(46027.5,  "yyyy-mm-dd hh:mm")     2026-01-05 12:00
=TEXT(46027.75, "yyyy-mm-dd hh:mm")     2026-01-05 18:00

Это объясняет наиболее распространенный сбой фильтра даты. Временная метка 5 января в 09:14 сохраняется как 46027.385, что не равно 46027, поэтому фильтр ровно по 5 января ничего не находит, и при поиске по дате пропускаются все строки. Ценности не ошибочны; это просто не целые дни.

Два исправления, в зависимости от того, что вы хотите. =INT(A2) выбрасывает время и оставляет свидание. Или фильтруйте по диапазону вместо равенства, >= 5 Jan AND < 6 Jan, который следит за временем и при этом показывает весь день.

Это также объясняет, почему продолжительность более 24 часов отображается странно. Ячейка, содержащая 1,5 дня, отформатированная как hh:mm показывает 12:00, потому что весь день перевернулся. Формат [h]:mm, в квадратных скобках, указывает Excel продолжать отсчет после 24 и показывает 36:00.

6. Месяц, конец месяца и группировка.

Если вы знаете, что дата — это число, функции месяца становятся небольшими.

=YEAR(A2)                          2026
=MONTH(A2)                            1
=DAY(A2)                              5
=TEXT(A2,"yyyy-mm")             2026-01
=EOMONTH(A2,0)               2026-01-31    last day of this month
=EOMONTH(A2,-1)+1            2026-01-01    first day of this month
=EDATE(A2,1)                 2026-02-05    same day, next month
=A2+30                       2026-02-04    thirty days later
=TEXT(A2,"dddd")                 Monday
=WEEKDAY(A2,2)                        1    Monday, when 1 means Monday

Два из них заслуживают внимания. EOMONTH(date,-1)+1 это стандартный способ получить первое число месяца, и его стоит запомнить, поскольку в нем нет FOMONTH. И EDATE это не то же самое, что прибавление 30: через месяц после 5 января будет 5 февраля, а через тридцать дней будет 4 февраля, а в конце месяца разница становится больше.

TEXT(A2,"yyyy-mm") — это ключ группировки, к которому я обращаюсь чаще всего, потому что он сортируется правильно, однозначен, а январь 2025 года и январь 2026 года остаются отдельно. Есть одна загвоздка: результатом является текст, поэтому его больше нельзя использовать в арифметике дат. Это нормально для столбца группировки и неправильно для чего-либо еще.

Часто вспомогательный столбец вообще не нужен. Фильтрация по диапазону дат в формуле работает непосредственно с числами:

=SUMIFS(Orders[Revenue],
        Orders[OrderDate], ">="&DATE(2026,3,1),
        Orders[OrderDate], "<="&DATE(2026,3,31))

2585

2585 — это доход за март, и построение границ с DATE вместо того, чтобы печатать ">=1/3/2026" полностью устраняет региональную двусмысленность из четвертого раздела. Если ваш следующий шаг — сравнить этот месяц с предыдущим, арифметика по месяцам одинаково в любом инструменте.

7. 29 февраля, которого не было

Перед объяснением: 1900 год не был високосным, потому что столетние годы являются високосными только тогда, когда они делятся на 400. Скажем, что Excel делает с 29 февраля 1900 года.

=TEXT(59,"yyyy-mm-dd")     1900-02-28
=TEXT(60,"yyyy-mm-dd")     1900-02-29
=TEXT(61,"yyyy-mm-dd")     1900-03-01
=N(DATE(1900,2,29))               60

Сериал 60 — это день, которого не было. Excel принимает его, форматирует и подсчитывает, и вы можете увидеть результат вычитания:

=DATE(1900,3,1) - DATE(1900,2,28)     2
=DATE(2026,3,1) - DATE(2026,2,28)     1

Тот же интервал календарных дней составляет два дня в 1900 году и один день в 2026 году. Это решение совместимости 1980-х годов, которое было сохранено намеренно, чтобы файлы из более ранних программ работы с электронными таблицами продолжали работать, и оно никогда не было исправлено, поскольку его исправление привело бы к перемещению каждой даты в каждой существующей книге.

Насколько вас это должно волновать? Почти не вообще, а ровно один раз. Любые расчеты после 1 марта 1900 года, включая все бизнес-данные, не затрагиваются. Это имеет значение, если вы когда-нибудь вычислите возраст или продолжительность, пересекающую эту дату, или если вы переместите серийные номера между Excel и другой системой, которая правильно определяет 1900 год, и в этом случае даты до марта 1900 года будут отставать на один день. Знание причины занимает десять секунд и экономит час неверия.

8. Настройка, сдвигающая каждую дату в файле на четыре года.

Существует вторая система дат, отсчитывающая с 1904 года вместо 1900 года, и это настройка для каждой книги, а не для каждой ячейки. Его переключение ничего не преобразует; это меняет то, что означают существующие числа. Запустите одну ячейку с серийным номером 46027:

1900 system      2026-01-05
1904 system      2030-01-06
serial 1, under the 1904 system     1904-01-02

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

Дело в том, что весь столбец устарел примерно на четыре года, и сдвиг одинаков в каждой строке. Если вы это видите, выберите «Файл», «Параметры», «Дополнительно» и найдите поле «Использовать систему дат 1904» в разделе книги, в которой вы находитесь.

Крайние случаи, которые незаметно нарушают сроки

Шесть, прошедшие первый проход.

Скрытый компонент времени. Даты, полученные из системы, обычно содержат время, поэтому фильтр по одному конкретному дню ничего не возвращает. Тест с =A2-INT(A2); все, что выше нуля, — это время, которое вы не можете увидеть.

Половина колонны преобразована. После частичного импорта некоторые строки представляют собой реальные даты, а некоторые — текст, и каждый агрегат вычисляется только на основе реальных дат. Сравнивать =COUNT(range) с количеством строк, точно так же, как и для чисел, хранящихся в виде текста.

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

Разворот, который сгруппировал январь дважды. Группировка по месяцам без отметки «Годы» объединяет один и тот же месяц из разных лет в одну строку. В многолетних данных отметьте оба варианта.

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

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