21/03/2004 не регистрируется как дата, а 7/3/2004 регистрируется как дата. Кто-нибудь знает, как мне удалить все нули в значениях дат со значениями месяцев, начинающимися с 0?
Поэтому столбцы, приклеенные к левому полю, почему-то не конвертируются автоматически. Со столбцами, приклеенными к правому полю, все в порядке.
решение1
Обычно это можно решить, используя функцию ДАТАЗНАЧ для преобразования даты, которая является текстом, в значение даты Excel (т. е. число, которое Excel распознает как дату). Использование будет ДАТАЗНАЧ(строка_даты или адрес_ячейки).
Однако здесь это не сработает, поскольку 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)))
)
Эта очень сложная формула массива (надеюсь, кто-нибудь сможет предложить более простую) использует функцию ДАТА для построения значения даты Excel. (У меня случайно оказалась исходная строка даты в ячейке F8, когда я строил дату.)
Строка даты, которую необходимо преобразовать, не может использоваться в необработанном виде, поскольку иногда она включает время. Вместо этого используется RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8). Эта формула ищет пробел в строке даты. Если она находит пробел, она использует функцию LEFT, чтобы получить все символы слева от пробела; если она не находит пробел, она просто возвращает исходную строку.
Самая внешняя часть формулы — это функция ДАТА: при указании года, месяца и дня она выдает дату Excel. Я разбил расчет этих трех частей в коде выше.
Расчет года следующий:
- Переверните строку даты (обрезав время, как описано выше).
- Найдите позицию первого символа «/» в этой перевернутой строке с помощью функции ПОИСК (Эта косая черта на самом деле является первой косой чертой в неперевернутой строке даты).
- Возьмите указанное количество символов справа от строки даты с помощью функции ПРАВСИМВ.
Расчет месяца состоит из следующих шагов:
- Найдите положение первой косой черты внеобратимыйстрока даты.
- Определите количество символов в месяце (которое может быть 1 или 2), вычислив разницу между этой позицией и позицией второго «/», определенной при расчете года.
- Используйте функцию MID для возврата символов между первым и вторым слешем.
Расчет дня самый простой.
- Рассчитайте положение первого символа «/», используя тот же метод, что и при расчете месяца.
- Возьмите указанное количество символов (минус 1) из левой части строки, используя функцию ЛЕВСИМВ.
Существуют менее сложные способы получения результата, самым простым из которых, несомненно, будет использование команды «Текст по столбцам» на вкладке «Данные» ленты, как предложил @Barry.
Другой способ — использовать функцию сопоставления шаблонов REGEX. Эта функция не встроена в Excel, но может быть создана и доступна с помощью кода VBA. Хотя код немного сложен, в сети доступно несколько его версий (например,этот пример). Однако использование функции было бы очень простым. Например, выражение соответствия для получения дня может быть чем-то вроде REGEXP("./",F8), которое затем можно использовать в функции ДАТА для получения значения даты 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))))
решение2
Значение, которое приклеивается к левой стороне, обычно означает, что это текстовое значение, тогда как числовое/дата значение выравнивается по правому краю.
Возможно, вам также стоит проверить эти вещи, прежде чем выполнять возможное преобразование даты и времени в Excel:
Проверьте настройки языка/региона вашей системы, особенно формат даты и времени. Excel использует настройки даты и времени вашей системы по умолчанию, и в результате преобразование даты и времени будет отличаться для разных регионов.
Убедитесь, что к значению ячейки не прикреплены лишние пробелы или скрытые символы переноса строки. Допустимое значение даты и времени с пробелом будет рассматриваться как текстовое значение, а не как дата и время, поэтому Excel не выполнит его автоматическое преобразование.
Используйте опцию пользовательского формата ячеек Excel, где вы можете указать собственные правила преобразования, например, ДД/ММ/ГГГГ.
решение3
Предположительно исходный формат — дд/мм/гггг. Попробуйте использовать функцию «Текст в столбцы» для преобразования.
Выберите столбец дат
Данные > Текст по столбцам > Далее > Далее > в разделе «Формат данных столбца» выберите «Дата» и выберите исходный формат из раскрывающегося списка - «ДМГ» > ОК
решение4
Формат данных по умолчанию в Excel: :dd/m/yyyy. Левое поле не преобразуется автоматически, потому что вы выбрали: m/dd/yyyy. Просто выберите правильный формат, и ваша проблема будет решена.