Вот список дат:
04-22-11
12-19-11
11-04-11
12-08-11
09-27-11
09-27-11
04-01-11
При копировании этого списка в Excel некоторые из них распознаются как даты, а другие нет, следующим образом:
04-22-11
12-19-11
11-04-11 (дата)
12-08-11 (date)
09-27-11
09-27-11
04-01-11 (дата)
Кто-нибудь знает, почему? И как заставить Excel распознавать все элементы списка как даты?
Большое спасибо!
Это не так сложно...
Отметьте этот пост в форуме:
http://www.pcreview.co.uk/forums/excel-not-recognizing-dates-dates-t3139469.html
Короче говоря:
Это вызвано региональными настройками вашего компьютера.
Когда вы вставляете данные в excel, это всего лишь цепочка строк (а не даты).
В Excel есть своя логика, чтобы распознавать ваши текущие форматы данных, а также несколько похожих форматов дат или очевидные форматы даты, где он может считать, что это дата. Когда он сможет сопоставить ваши данные с данными в действительную дату, он будет отформатировать его как дату в ячейке, в которой он находится.
Ваш конкретный пример связан с тем, что ваш список дат форматируется как "m/d/yy", который является форматом США. он правильно вставляется в мое excel, потому что у меня есть региональный набор настроек на "американский английский" (хотя я и канадский:))
Если система настроена на канадский английский/французский формат, она будет ожидать формат "d/m/yy" и не будет распознавать дату, где месяц составляет > 13.
Лучший способ импортировать данные, содержащие даты, в excel - скопировать его в этом формате.
2011-04-22
2011-12-19
2011-11-04
2011-12-08
2011-09-27
2011-09-27
2011-04-01
Что такое "yyyy-MM-dd", этот формат распознается одинаково на каждом компьютере, который я когда-либо видел (часто упоминается как формат ODBC или стандартный формат), где единицы всегда от максимального до наименьшего веса ( "yyyy-MM-dd HH: mm: ss.fff" ) другой побочный эффект: он будет корректно сортироваться как строка.
Чтобы избежать изменения региональных настроек взад и вперед, вы можете рассмотреть возможность наложения макроса в excel для вставки данных. Простой формат всплывающего окна и некоторая базовая логика для переформатирования дат не будут слишком сложными.
В вашем случае это, вероятно, принимает их в формате DD-MM-YY, а не MM-DD-YY.
Самый быстрый и простой способ исправить это - найти и заменить на своем разделителе даты с тем же разделителем. Например, в этом случае найдите "-" и "Замените" на "-", не знаете, почему это работает, но вы найдете, что все даты выровнены по правому краю, как и должно быть после этого.
Вот то, что сработало для меня в формате mm/dd/yyyy:
=DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,2)),VALUE(MID(A1,4,2)))
Преобразуйте ячейку с формулой в формат даты и перетащите формулу вниз.
Щелкните правой кнопкой мыши заголовок столбца и выберите "Форматировать ячейки", выберите "Дата" и выберите нужный формат даты. Те, которые не распознаются, являются неоднозначными и, как таковые, не интерпретируются как ничего, кроме тех, которые разрешены после применения форматирования к столбцу. Обратите внимание, что для меня в Excel 2002 SP3 даты, указанные выше, автоматически и правильно интерпретируются как даты при вставке.
Обходной путь для этой проблемы заключается в временном изменении ваших региональных настроек, поэтому формат даты импортированного файла CSV "соответствует" региональным настройкам.
Open Office, похоже, работает аналогичным образом для этой проблемы: http://www.oooforum.org/forum/viewtopic.phtml?t=85898
Вот что сработало для меня. Я выделил столбец со всеми моими датами. На вкладке "Данные" я выбрал "текст для столбцов" и выбрал поле "Разграничено", я нажму рядом и закончу. Хотя это не похоже на что-то изменившееся, Excel теперь читает столбец как даты, и мне удалось сортировать по датам.
Самое простое решение - положить yy, mm, dd в формулу date(), предварительно извлекая их с помощью left(), mid() и right(). В этом случае, если ваша дата ввода находится в A1:
=date(right(A1,2)+100,left(A1,2),mid(A1,4,2))
Объяснение выше:
=right(A1,2) получает последние две цифры в ячейке (yy). Мы добавляем 100, потому что по умолчанию он равен 1911 вместо 2011 (опустите +100, если он не делает этого на вашем)
=left(A1,2) получает первые две цифры в ячейке (мм).
=mid(A1,4,2) получает 2 цифры в середине ячейки, начиная с 4-й цифры (dd).
Почему это происходит в первую очередь:
Я сталкиваюсь с этой проблемой все время, когда я импортирую канадские банковские данные в excel. Короче говоря, формат входных данных не соответствует вашим региональным настройкам.
Кажется, что ваш параметр означает, что Excel требует ввода даты как DD-MM-YY или YY-MM-DD, но ваши входные данные отформатированы как MM-DD-YY.
Итак, excel видит ваши дни как месяцы и наоборот, что означает, что любая дата с днем ниже 12 будет признана датой, НО НЕПРАВИЛЬНАЯ ДАТА (месяц и день отменена) и любая дата с днем выше 12 вон " t быть признанным как дата вообще, потому что Excel видит день как 13-й + месяц.
К сожалению, вы не можете просто изменить форматирование, потому что Excel уже заблокировал эти назначения день/месяц на месте, и вы просто в конечном итоге перемещаете то, что Excel THINKS - это дни и месяцы вокруг визуально, а не переназначая их.
Честно говоря, для меня удивительно, что в excel нет инструмента для обратного отсчета даты, потому что я думаю, что это происходит все время. Но приведенная выше формула делает это довольно просто.
ПРИМЕЧАНИЕ: если ваши даты не имеют ведущих нулей (например, 4/8/11 против 04/08/12), становится сложнее, потому что вам нужно извлечь разные количества цифр в зависимости от даты (например, 4/9/11 против 4/10/11). Затем вам нужно построить пару утверждений в вашей формуле. Гросс.