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

Question

Я в Великобритании, у меня проблема, когда у меня есть список дат, представленных в формате США. Кажется, что Excel обрабатывает те, которые действительны в обоих форматах, как даты в Великобритании (например, 03/01/2012 становится 3 января, а не 1 марта), а также те, которые не являются действительными датами в Великобритании (например, 03/13/2012) в качестве основного текста. Я предполагаю, что этот выбор как-то связан с моими региональными настройками.

Я хочу, чтобы система распознала этот столбец текста в формате даты США и преобразовала его в базовое представление даты для расчетов.

Как мне это сделать?

РЕДАКТИРОВАТЬ: Даты предоставляются в CSV-файл в форме:

3/ 1/2012, 09:01     , 18:58     ,9.4,0.6

где 3/1 1 марта

Answer 1

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

Answer 2

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

Answer 3

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

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

Answer 4

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

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

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

Answer 5

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

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

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

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

Answer 6

КОНВЕРТ ДАТЫ США В ДАТЫ ВЕЛИКОБРИТАНИИ В 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-й 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).

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

Answer 7

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

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

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

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

Answer 8

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

Answer 9

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

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

Answer 10

У меня была эта проблема в течение достаточно долгого времени. Кажется, это часто неправильно понимаемая проблема. В частности, что компьютер не обязательно распознает дату в правильном формате. Если бы вы записали дату в формате ДД / ММ / ГГГГ, 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 для этой формулы.

Answer 11

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

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

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

Answer 12

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