Как остановить Excel, рассматривая даты США как даты Великобритании?

В Excel 2010 вместо открытия файла CSV создайте новую книгу, а затем на вкладке ДАННЫЕ выберите " Получить внешние данные" → " Из текста". Это открывает интерфейс, в котором вы можете указать, как интерпретировать ваши текстовые данные, включая обработку дат.

Измените расширение файла с ".csv" на ".txt", затем откройте в Excel. Excel предоставит вам мастер импорта текста. Выберите "с разделителями" на первой странице, отметьте "запятая" на второй и на третьей вы сможете выбрать тип для каждого столбца данных. Один из типов - это дата и выпадающий список в различных форматах (m/d/y, d/m/y и т. Д. И т. Д.).

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

Таким образом, реальное решение заключается в следующем: 1. Сохранение дат в числовом формате, если это не удалось. 2. Переведите даты в числовой формат самостоятельно, а затем отобразите их в отдельном столбце в любом формате даты.

Номер 2 не требует никакого программирования, просто сохраняя исходный формат столбца как общий текст, используя формулу (лучше будет вычисляемый столбец таблицы), которая превращает его в числовую форму, и другой столбец, который отображает это значение в виде формата даты для вам нравится.

Я столкнулся с той же проблемой, но немного по-другому. Даты, импортируемые из файла CSV, имеют британский формат, но если даты действительны как в США, так и в Великобритании, Excel рассматривает их как американский формат. Мой обходной путь заключается в том, чтобы проанализировать даты как текст (используя NumberFormat = "@") на листе, а затем переформатировать ячейки впоследствии в правильный формат даты (используя NumberFormat = "dd/mm/yyyy").

Кажется (в любом случае) самый простой способ сделать это.

Вероятно, вы можете сделать следующее:

1) Измените настройку даты на панели задач Windows на время в США. Вот руководство о том, как это сделать: http://www.sysprobs.com/change-date-format-windows-7-ddmmyyyy

2) Установите время в США на Excel. Перейти к форматированию ячеек> Дата> Язык> Английский (США)

Это сработало для меня, надеюсь, это поможет.

КОНВЕРТ ДАТЫ США В ДАТЫ ВЕЛИКОБРИТАНИИ В EXCEL / CSV

(вы действительно не хотите менять настройки локали)

Это двухэтапный процесс:

1. Чтобы Excel не пытался автоматически преобразовать некоторые даты в британский формат, а затем испортил преобразование, в Excel выделите столбец и выберите "Данные"> "Текст в столбцы".

   1. Выбрать с разделителями, Далее
   2. Снимите все отметки с разделителей, Далее
   3. Выберите тип данных "Текст", Далее

Не пытайтесь просто установить это через меню форматирования (щелчок правой кнопкой мыши по ячейкам форматирования), оно не будет работать. Не выбирайте "Дата" в "Текст в столбцы", так как это приведет к конвертации дат в США с использованием настроек Великобритании, используйте "Текст"

Также см. Ответ 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-й час - это "/" (то есть дата должна быть 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).

Надеюсь это поможет.

Если вы начинаете с файла CSV, используйте мастер импорта текста , как предлагает ответ dunxd .

Если у вас уже есть данные в Excel, которые интерпретируются неправильно, вы можете использовать функцию «Текст в столбцы» и делать то же самое, что и Мастер импорта текста — сложные формулы не требуются.

Вот пример, где у меня есть 3 даты в формате США, но мое текущее местоположение — Австралия, где мы используем формат Великобритании:

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

Чтобы исправить это:

1. Выберите ячейки, содержащие даты, которые вы хотите исправить.

2. Выберите «Данные» > «Текст в столбцы».

3. Выберите «Фиксированная ширина» , нажмите «Далее» , а затем еще раз «Далее» (вам не нужно создавать дополнительные столбцы).

4. Измените формат данных столбца на «Дата» и «MDY» (или любой другой формат, в котором находятся ваши исходные даты)

   

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

5. Выберите «Готово» . Ваши даты теперь определены.

6. Затем вы можете применить любое форматирование даты, которое вы предпочитаете. Здесь я напечатал название месяца полностью, чтобы вы могли видеть, что исправление было применено правильно:

   

Что делать, если Excel не может распознать формат даты?

Иногда Excel не распознает дату и оставляет ее в виде текста. Вот один пример:

      2022-05-12T10:56:43.682783+00:00

В этом примере мне пришлось использовать функцию «Текст по столбцам» , чтобы разделить текст на букву.Tа затем еще раз на+символ для извлечения даты и времени отдельно. Тогда у вас будет дата и время в отдельных ячейках, где Excel может либо распознать формат, либо вы можете использовать описанный выше метод, чтобы исправить его. Если вам нужно снова объединить дату и время, вы можете просто сложить их вместе (при условии, что Excel распознает их как дату и время).

Недавно у меня возникла аналогичная проблема с Excel 2010. Excel интерпретирует даты, которые неправильно интерпретируются как формат даты в США, т. Е. Любая дата с днем> 12. Для всех дат с днем ​​< 13 он отображает их в формате США мм / дд / гг - все в том же столбце и все в том же формате. Моим первым шагом было проверить, что мои настройки в Excel и Windows были на английском (Австралия) или, по крайней мере, на английском (Великобритания). Ни один из них не был правильным, поэтому я изменил оба и перезапустил Excel, как было рекомендовано. Это не решило проблему. Я переформатировал столбец даты как текст, вставил пару строк между правильными датами и датами США, засыпал, перетащив ручку копирования, затем ввел правильные даты в новые ячейки, перетащил содержимое других ячеек в записи в их ne местоположение и удалил хитрые строки. Когда я переформатировал столбец даты в формат даты, я обнаружил, что, хотя мои даты теперь были правильно ориентированы в формате дд / мм / гггг, Excel по-прежнему распознавал два разных формата - один выровненный по левому краю, другой выровненный по правому краю и не отображавший ни один из форматов. другие варианты могут решить эту проблему.

Как ни странно, когда я вкладывал одну из выровненных по левому краю ячеек в соседнюю, переформатированный в правый выровненный вариант и сохранял правильный формат даты. Я не могу объяснить это, учитывая, что Excel отображал дескриптор "Date" для обоих в разделе "Number" на ленте, но это устранило проблему.

У меня была такая же проблема, но в обратном порядке: от австралийского до американского формата.

В моем случае я запускаю макрос, который открывает файл CSV. Один столбец файла содержит даты. После добавления дополнительного столбца с некоторыми вычисленными данными макрос сохраняет его как файл.XLS. В файле XLS дата, которая действительна в обоих форматах, изменится, то есть 1/11/12 станет 11/1/12. Обратите внимание, что мой макрос никак не касается столбца даты, и CSV всегда имеет правильный формат. Кажется, что это происходит только на компьютере клиента, на котором установлен Office 2010. Я не видел его в Office 2000 или 2003.

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

Я рассмотрел еще одну возможность, которая заключается в том, чтобы иметь макрос оболочки что-то вроде команды grep, чтобы поставить "пробел" перед датами. Это мешает Excel интерпретировать это как дату (кавычки вокруг дат не помогают). Я решил, что импорт был более чистым способом. Поскольку для редактирования нет встроенной утилиты Win, мне придется установить grep на ПК клиента.

Проблема заключается в том, что у вас есть настройки даты в Excel, установленные как (и некоторые даты) Форматы даты, которые начинаются с, и asterik () реагирует на изменения региональных настроек даты и времени, указанных для операционной системы. Вам нужно перейти к панели управления и отрегулировать там формат даты и времени, чтобы избежать перелистывания дат. Иногда, например, 01/11/2013 переходит на 01.11.2013 (где 11 - ноябрь), однако 30/11/2013 не переворачивается, это потому, что ms не распознает 30-й как действительный месяц. формула не требуется

У меня была эта проблема в течение достаточно долгого времени. Кажется, это часто неправильно понимаемая проблема. В частности, что компьютер не обязательно распознает дату в правильном формате. Если бы вы записали дату в формате ДД / ММ / ГГГГ, Excel интерпретирует ее в формате ММ / ДД / ГГГГ. Таким образом, 05/06/2012 будет означать 5 июня 2012 г., однако Excel будет интерпретировать его как 6 мая 2012 г. Точно так же, если вы введете 13/12/2012, где вы имели в виду 13 декабря 2012 г., Excel не будет узнайте дату вообще, так как месяца 13 нет.

Чтобы исправить эту проблему, я разработал формулу, которая перевернет первые две цифры даты. Это может быть использовано для конвертации США в Великобританию или наоборот.

=IF(ISERR(DATEVALUE(F10))=TRUE,DATE(RIGHT(TEXT(F10,"DD/MM/YYYY"),4),LEFT(TEXT(F10,"DD/MM/YYYY"),2),LEFT(RIGHT(TEXT(F10,"DD/MM/YYYY"),7),2)),IF(ISERR(DATEVALUE(TEXT(F10,"mm/DD/YYYY")))=TRUE,DATE(RIGHT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),4),LEFT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),2),LEFT(RIGHT(TEXT(TEXT(F10,"mm/DD/YYYY"),"DD/MM/YYYY"),7),2)),0))

Это сработало для меня именно так, как я хотел, и я считаю, что это решение, которое еще не существует в этой области. Пожалуйста, дайте мне знать, если это работает так же хорошо для вас, как и для меня.

PS. Я использовал Excel 2007 для этой формулы.

Связанная информация и ответ на все проблемы:

Сохраните всю информацию о дате и времени только в одном формате:
http://en.wikipedia.org/wiki/ISO_8601

При необходимости прикрепите поле формата отображения к сохраненным данным.

Щелкните правой кнопкой мыши по столбцу, выберите ячейки формата, выберите первую вкладку "Число", выберите дату категории, а в правой части установите "Язык" на "Английский (США)".