КОНВЕРТ ДАТЫ США В ДАТЫ ВЕЛИКОБРИТАНИИ В EXCEL/CSV
(вы действительно не хотите менять настройки локали)
Это двухэтапный процесс:
Чтобы Excel не пытался автоматически преобразовать некоторые даты в британский формат, а затем испортил преобразование, в Excel выделите столбец и выберите «Данные»> «Текст в столбцы».
- Выбрать с разделителями, Далее
- Снимите все флажки Разделитель, Далее
- Выберите тип данных «Текст», Далее
Не пытайтесь просто установить это через меню форматирования (щелчок правой кнопкой мыши по ячейкам форматирования), оно не будет работать.
Не выбирайте «Дата» в «Текст в столбцы», так как это конвертирует даты в США с использованием настроек Великобритании, используйте «Текст»
[Также смотрите ответ dunxd для импорта csvs. Это также будет работать, если импортировано как TXT.]
2. В запасном столбце рядом скопируйте следующую формулу. Замените ссылки на F2 вашей стартовой ячейкой. Отформатируйте столбец в любом формате даты, который вы выберете.
= ДАТАЗНАЧ (СЦЕПИТЬ ((ЕСЛИ (MID (F2,4,1)= "/", MID (F2,3,1), ЕСЛИ (MID (F2,2,1)= "/", MID (F2,3 , 2), ПЧ (MID (F2,6,1)= "/", MID (F2,4,2), MID (F2,4,1))))), "/", ЕСЛИОШИБКА ((ЕСЛИ ( ПОИСК ( "/", ЛЕВЫЙ (F2,2)), влево (F2,1), ЛЕВАЯ (F2,2))), ЛЕВЫЙ (F2,2)), "/", ЕСЛИ (ISERR (FIND ( "/»ПРАВЫЙ (F2,3), 1)), вправо (F2,4), вправо (F2,2))))
Примечание: это должно работать с датами в следующих форматах:
- 1/1/14
- 01/1/14
- 1/01/14
- 01/01/14
- 1/1/2014
- 01/1/2014
- 1/01/2014
- 01/01/2014
Если ваша дата не разделена символом «/», замените этот разделитель в приведенной выше формуле.
Надеюсь, поможет.
ОБНОВЛЕНИЕ 17-2-2014
По просьбе немного поломка:
Datevalue и Concatenate просто создают окончательную строку в британском формате даты, поэтому интересный момент:
а. Получите День США (среднее значение)
((ЕСЛИ (MID (F2,4,1)= "/", MID (F2,3,1), IF (MID (F2,2,1)= "/", MID (F2,3,2), ЕСЛИ (MID (F2,6,1)= "/", MID (F2,4,2), MID (F2,4,1)))))
Самая сложная часть. Мы не уверены в формате даты. Мы знаем, что где-то будет два "/", но нам нужно найти где. Самая хитрая часть в данных, таких как 1/1/10, второй «/» может быть уже в 4-м часу, где на такой дате, как 10/10/10, первый «/» - 3-й, где Второй 6-й час. Нам нужен способ отличить первое и второе "/" друг от друга. К счастью, формат «1/1/» является единственным способом, которым «/» может быть четвертым, поэтому мы можем использовать это:
- Если "/" - это точный 4-й час (то есть d и m будут одинарными), верните 3-й час в качестве значения дня в США.
- Если нет (это означает, что в день или месяц США должен быть хотя бы один номер 2chr), если 2-й chr - это "/", тогда верните 3-й и 4-й chr в качестве значения дня в США.
- Если нет, то если 6-й chr это "/" (то есть дата должна быть 2 двойными
на день и месяц т.е. '10/10/'), верните 4 и 5-й час в качестве значения дня в США, в противном случае верните только 4-й час в качестве значения дня в США.
б. Получить месяц США (левое значение)
ЕСЛИОШИБКА ((ЕСЛИ (ПОИСК ("/", ЛЕВЫЙ (F2,2)), влево (F2,1), ЛЕВАЯ (F2,2))), ЛЕВЫЙ (F2,2))
Довольно простой,
- Если "/" существует в 2 самых левых часах строки, верните только 1-й час
- если нет, верните первые 2 грн.
- если ошибка, то же самое, что не найти "/", поэтому верните также первые 2 chr
с. Получить год (правильное значение)
ЕСЛИ (ISERR (FIND ("/", ПРАВЫЙ (F2,3), 1)), вправо (F2,4), вправо (F2,2))
Просто,
- если "/" существует в последних 3 часах строки, вернуть последние 2 часа, если нет, вернуть последние 4 часа (год 4 года или год 2).
Надеюсь это поможет.