21/03/2004 не регистрируется как дата, в то время как 03.07.2004 регистрируется как дата. Кто-нибудь знает, как я мог удалить все 0 в значениях дат со значениями месяца, начиная с 0?
Таким образом, столбцы, которые приклеены к левому полю, каким-то образом не преобразуются автоматически. колонки, склеенные вправо, в порядке.
Обычно это можно решить с помощью функции DATEVALUE для преобразования даты, которая является текстовой, в значение даты Excel (т. Е. Число, которое Excel распознает как дату). Использование будет DATEVALUE (date_string или cell_address).
Однако это не сработает, потому что 1) день предшествует месяцу в строках вашего примера, а DATEVALUE ожидает, что за месяцем следует день, и 2) для некоторых строк даты также включается время.
Вот альтернатива формулы массива, которая работает с примерами, которые вы предоставили. Он должен быть введен с помощью комбинации клавиш Control - Shift - Enter .
=DATE(
RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)),
LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-1),
LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)-LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1)))
)
Эта очень сложная формула массива (я надеюсь, что кто-то может предложить более простую) использует функцию DATE для построения значения даты в Excel. (При создании даты у меня была оригинальная строка даты в ячейке F8.)
Строка даты, которую необходимо преобразовать, не может использоваться в необработанном виде, поскольку иногда она содержит время. Вместо этого используется RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8) . Эта формула ищет пробел в строке даты. Если он находит пробел, он использует функцию LEFT, чтобы получить все символы слева от пробела; если он не находит пробел, он просто возвращает исходную строку.
Самая внешняя часть формулы - это функция DATE: если заданы год, месяц и день, она возвращает дату в формате Excel. Я разбил расчет этих трех частей в коде выше.
Год рассчитывается следующим образом:
Расчет месяца состоит из следующих этапов:
Расчет дня самый простой.
Есть менее сложные способы получения результата, самым простым из которых, несомненно, будет использование команды «Текст в столбцы» на вкладке «Данные» на ленте, как предлагает @Barry.
Другой способ продолжить - использовать функцию сопоставления с шаблоном REGEX. Эта функция не встроена в Excel, но может быть создана и доступна с помощью кода VBA. Хотя код немного сложнее, в сети доступно несколько версий (например, этот пример). Однако использовать функцию было бы очень просто. Например, выражение соответствия для получения Дня может быть чем-то вроде REGEXP("./",F8) , которое затем можно использовать в функции DATE для получения значения даты в Excel.
Для удобства приведу формулу в ее полной форме.
=DATE(RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)),LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-1),MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1)),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)-LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1))))
Значение, которое приклеивается к левой стороне, обычно означает, что это текстовое значение, тогда как числовое значение / значение даты выровнены по правому краю.
Возможно, вы также можете проверить эти вещи, прежде чем делать возможное преобразование даты и времени в Excel:
Проверьте системные настройки языка / региона, особенно формат даты и времени. В Excel используются стандартные системные настройки времени и даты, поэтому в результате преобразование даты и времени будет отличаться для разных регионов.
Убедитесь, что к значению вашей ячейки не добавлены лишние пробелы или скрытые символы разрыва строки, допустимое значение даты-времени с пробелом будет рассматриваться как текстовое значение, а не как время даты, поэтому Excel не будет автоматически преобразовывать его ,
Используйте параметр формата ячейки Excel, в котором вы можете указать свои собственные правила преобразования, такие как ДД / ММ / ГГГГ.
Предположительно исходный формат - дд / мм / гггг. Попробуйте использовать функцию "Текст в столбцы" для преобразования.
Выберите столбец дат
Данные> Текст в столбцы> Далее> Далее> в разделе "Формат данных столбца" выберите "Дата" и выберите исходный формат из выпадающего списка - "DMY"> ОК
Я видел, что вы только что нашли решение, но если это работает, это должен быть более простой обходной путь.
Выберите ячейки, которые не отформатированы как даты. Щелкните правой кнопкой мыши, выберите формат ячеек, выберите Numbers и категорию Custom. В поле типа задайте формат, который является форматом ввода, вы вводите его вручную, потому что не будет подходящего вам формата. В случае ввода строки дд / мм / гггг, нажмите ОК. Теперь Excel знает, что это дата (хотя с форматом день месяц год), вы можете скопировать и вставить эту ячейку в новую ячейку, которая отформатирована как дата, как вы хотите.
Формат данных по умолчанию в Excel: дд / м / гггг. Левое поле не конвертируется автоматически, потому что вы выбираете: m / dd / yyyy. Просто выберите правильный формат, который решит вашу проблему.