У меня постоянно возникают проблемы при работе с датами в Excel, видимо, я что-то делаю не так, но не понимаю что.
У меня есть электронная таблица, экспортированная с нашего сервера обмена, которая содержит столбец с датами. Они выведены в формате США, хотя я нахожусь в Великобритании.
Столбец, о котором идет речь, выглядит следующим образом
04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011
В Excel я выделил столбец и выбрал Format Cells...
. В этом диалоговом окне я выбрал Дата
, выбрал Английский (США)
в качестве локали и выбрал подходящий формат даты из списка. Я нажал кнопку OK и попытался отсортировать данные по этому столбцу.
В диалоге сортировки я выбираю этот столбец, выбираю сортировку по значениям, но порядок дает мне только варианты от A до Z, а не от самого старого до самого нового, как я ожидал.
Это, в свою очередь, сортирует данные о дате по первым двум цифрам.
Я знаю, что могу переформатировать эти данные в ISO, и тогда сортировка от A до Z будет работать, но я не должен этого делать, очевидно, я что-то упускаю. Что это?
EDIT: Я перепутал вознаграждение, но это должно было пойти на ответ @r0berts, его первое предложение Text to Columns без разделителя и выбор 'MDY' в качестве типа данных работает. Кроме того, если у вас есть время (например, 04/21/2015 18:34:22
), вам нужно сначала избавиться от данных о времени. Однако после этого метод, предложенный @r0berts, работает нормально.
Проблема: Excel не хочет распознавать даты как даты, даже если через "Формат ячеек - Число - Пользовательский" вы явно пытаетесь указать ему, что это даты в формате "mm/dd/yyy
". Как вы знаете, когда excel распознает что-то как дату, он далее хранит это как число - например, "41004
", но отображает как дату в соответствии с указанным вами форматом. Чтобы добавить путаницы, excel может конвертировать только часть дат, например 08/04/2009, но оставить другие, например 07/28/2009, неконвертированными.
Решение: шаги 1, а затем 2.
1) Выберите столбец даты. В разделе Данные выберите кнопку Текст в столбцы. На первом экране оставьте радиокнопку на "delimited" и нажмите Next. Снимите флажки с разделителей (любые флажки пустые; без галочек) и нажмите Next. В разделе Формат данных столбца выберите Дата и выберите MDY в соседнем комбинированном окне и нажмите Закончить. Теперь у вас есть значения даты (т.е. Excel распознал ваши значения как тип данных Date
), но форматирование, вероятно, все еще является датой по локали, а не mm/dd/yyyy
, как вы хотите.
2) Чтобы получить желаемый формат даты США, сначала нужно выбрать столбец (если он не выбран), затем в разделе Формат ячейки - Число выбрать Дата и выбрать Локаль : Английский (США). Это даст вам формат типа "m/d/yy
". Затем вы можете выбрать Custom и там вы можете либо ввести "mm/dd/yyy
", либо выбрать это из списка пользовательских строк.
Альтернатива : используйте LibreOffice Calc. После вставки данных из сообщения Патрика выберите Paste Special (Ctrl+Shift+V
) и выберите Unformatted Text. Откроется диалоговое окно "Импорт текста". Набор символов остается Unicode, а для языка выберите English(USA); также следует установить флажок "Определять специальные числа". Ваши даты сразу же отображаются в стандартном американском формате и поддаются сортировке. Если вы хотите использовать специальный американский формат MM/DD/YYYY, вам нужно указать его один раз через "Формат ячеек" - либо до, либо после вставки.
Можно сказать - Excel должен был распознать даты, как только я указал ему через "Формат ячеек", и я не могу не согласиться с этим. К сожалению, только с помощью шага 1, описанного выше, я смог заставить Excel распознать эти текстовые строки как даты. Очевидно, что если вы делаете это часто, то это очень хлопотно, и вы можете создать процедуру на visual basic, которая будет делать это за вас одним нажатием кнопки.
Данные | Текст в столбцы.
Обновление по поводу ведущего апострофа после вставки: Вы можете видеть в строке формул, что в ячейке, где дата не распознается, есть ведущий апостроф. Это означает, что в ячейке, отформатированной как число (или дата), находится текстовая строка. Можно сказать, что ведущий апостроф мешает электронной таблице распознать число. Вы должны знать, что искать это в строке формул, потому что электронная таблица просто отображает то, что выглядит как выровненное по левому краю число. Чтобы справиться с этой проблемой, выберите столбец, который вы хотите исправить, выберите в меню Данные | Текст в столбцы
и нажмите OK. Иногда вы можете указать тип данных, но если вы ранее установили формат столбца в соответствии с вашим конкретным типом данных - вам это не понадобится. Команда действительно предназначена для разделения текстового столбца на два или более с помощью разделителя, но она прекрасно работает и для этой задачи. Я тестировал ее в Libreoffice, но такой же пункт меню есть и в Excel.
Выделите весь столбец и перейти к найти и заменить и просто заменить в "/"
В С /
.
Это может не иметь отношение к оригинальный вопрос, но это может помочь кому-то, кто не в состоянии отсортировать столбец с датами.
Я обнаружил, что Excel не распознает столбец с датами, которые были до 1900 года, настаивая на том, что они были столбец текста (поскольку 1/1/1900 имеет числовой эквивалент 1, и отрицательные числа, видимо, не пускают). Так я же вообще заменить всех моих кавалеров (которые были в 1800-х годах), чтобы положить их в 1900-х годах, например, 180 -и GT; 190, 181 -> 191 и др. Затем процесс сортировки работало нормально. Наконец-то я сделал замену другим способом, например, 190 -и GT; 180.
Надеюсь, что это помогает некоторым другим историком там.
У меня была точно такая же проблема с датами в Excel. Формат соответствует требованиям на дату в Excel, но без редактирования каждой ячейки не признают дата, когда вы имеете дело с тысячами строк, это не практично, чтобы вручную редактировать каждую ячейку. Решение для выполнения поиска и замены в строке даты, где я заменил hypen с дефисом. Excel работает через колонку заменить подобное подобным, но в результате фактором является то, что он сейчас recongnises нужные! Исправлено!
Я имел дело с подобной проблеме с тысячами строк, извлеченных из баз данных SAP и, непонятным образом, две различные форматы даты в тот же день колонна (большинство из которых наши стандартные "в формате гггг-ММ-ДД", но около 10% того, что "ММ-ДД-гггг и"). Редактирование вручную после 650 строк каждый месяц не вариант.
Никто (ноль... 0... шь) из перечисленных выше вариантов не работает. Да, я пытался их всех. Копирование в текстовый файл или явным образом экспорт в txt еще, почему-то, никак не повлиял на формат (или отсутствие это огромная работа) для 10% времени, что просто сидел в своем углу, отказываясь вести себя.
Единственный способ я был в состоянии исправить это, чтобы вставить пустой столбец справа от неправильно столбец с датой, и через следующую, очень простую формулу:
(при условии, что некорректно данные в столбце D`)
=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))
Я мог бы скопировать результаты в моей новой, вычисляемый столбец, поверх некорректно нужные вставки на "ценностей" и только после чего я могу удалить мой вычисляемый столбец.
Я подозреваю, что проблема в Excel не может понять формат... хотя вы выберите формат даты, он остается в виде текста.
Вы можете проверить это легко: при попытке обновить формат даты, выберите столбец и нажмите правой кнопкой мыши и выберите команду формат ячеек (как вы уже делаете), но выбрать опцию 2001-03-14 (внизу списка). Затем проверьте формат ячеек.
Я подозреваю, что только некоторые из форматов даты правильно обновить, указывая, что Excel по-прежнему рассматривая его как строку, а не дату (обратите внимание на различные форматы в снимке экрана ниже)!
Есть несколько обходных путей для этого, но никто из них не будет автоматизировано, просто потому, что вы'экспорт заново каждый раз. Я бы предложил с помощью VBA, где вы можете просто запустить макрос, который преобразует из нас на сегодняшний день формат, но это будет означать, скопировав и вставив VBA в лист каждый раз.
Кроме того, можно создать в Excel, которая считывает созданный экспортированы в Excel (от биржи), а затем выполнение кода VBA, чтобы обновить формат даты для вас. Смею предположить, экспортированного файла обмена Excel всегда будет иметь то же имя файла/каталога и предоставить рабочий пример:
Итак, создайте новый лист Excel, называется ImportedData.файл xlsm (Excel с поддержкой). Это файл, куда мы будем импортировать экспортированный файл Excel в обмен!
Добавить этот VBA в ImportedData.файл xlsm
Sub DoTheCopyBit()
Dim dateCol As String
dateCol = "A" 'UPDATE ME TO THE COLUMN OF THE DATE COLUMN
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
directory = "C:\Users\Dave\Desktop\" 'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx") 'UPDATE ME (this is the Exchange exported file location)
Do While fileName <> ""
'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)
Workbooks(fileName).Worksheets("Sheet1").Copy _
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")
Dim splitty() As String
splitty = Split(Range(dateCol & row).Value, "/")
Range(dateCol & row).NumberFormat = "@"
Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
row = row + 1
Loop
End Sub
Что я и сделал, обновить формат даты гггг-ММ-ДД, потому что таким образом, даже если приложение Excel дает вам фильтр и сортировка а-я, а не новые значения, он все еще работает!
Я'уверен, что приведенный выше код имеет ошибки, но я, очевидно, понятия не имеете, какой тип данных у вас есть, но я тестировал его с одного столбца дат (как у вас) и она отлично работает для меня!
https://superuser.com/questions/801609/how-do-i-add-vba-in-ms-office
Похоже, что Excel не распознает ваши даты как даты, он распознает их как текст, поэтому вы получаете параметры сортировки от A до Z. Если вы все сделаете правильно, вы должны получить что-то вроде этого:
Следовательно, важно убедиться, что Excel распознает дату. Самый простой способ сделать это - использовать сочетание клавиш CTRL+SHIFT+3.
Вот что я сделал с вашими данными. Я просто скопировал их из вашего сообщения выше и вставил в excel. Затем я применил ярлык, указанный выше, и получил нужный вариант сортировки. Проверьте изображение.
Простое решение здесь - создать новые колонка =датазнач(клетка) формулу, затем скопировать формулы в остальные строки - несколько секунд исправить
Я все понял!
Есть места в начале и в конце дня.
Эта проблема была сводит меня с ума, а потом легко исправить. По крайней мере, это сработало для моего сведения. Это легко сделать и вспомнить. Но я не знаю, почему это работает, но изменение типов как указано выше, не.
Я не имея никаких удачи с все вышеперечисленные предложения - придумал очень простое решение, которое работает как шарм. Вставить данные в Google Таблицах, выделите столбец дата, нажать на формат, количество, количество и еще раз, чтобы изменить его в числовой формат. Я затем скопировать и вставить данные в таблицу Excel, нажать на нужные, потом формат ячеек на дату. Очень быстрая. Надеюсь, что это помогает.
Если Excel упорно отказывается признавать свой столбца как даты, то замените его на другой :
Вставить
, а только вставить "значения"Я просто скопировал номер 1 (один) и умножить ее на столбец дат (данных) используя функцию специальная вставка. Затем она изменяет общего форматирования я.е 42102 Затем перейдите применять даты на запись и она теперь признает его как дату.
Надеюсь, что это помогает
Все вышеуказанные решения, в том числе r0berts - были неудачными, но нашел это странное решение, которое оказалось самым быстрым исправить.
Я пытался разобраться, что "нужные" на загруженную таблицу операций по счету.
Это были загружены через браузер Chrome. Никакое количество манипуляций в "нужные" по их признана старой версии к Новой Сортировки.
Но, потом я скачал через интернет-браузера - удивительно - я могла бы мгновенно, не касаясь колонны.
Я не могу объяснить, почему различные браузеры влияют на форматирование данных, за исключением того, что он явно был очень быстро исправить.
ОБМЕН НЕМНОГО ДОЛГО, НО ГОРАЗДО ПРОЩЕ РЕШЕНИЕ ВОПРОСА..... Не нужно запускать макросы или вызывающим вещи....простой МС и редактирования формул в Excel.
смешанные даты снимка в формате Excel:
#значение
. - Наконец, из строки мы создали - мы перевели эти даты по формуле дата.#Значение
были выбраны как функция iferror добавлено в дату формуле & столбец был отформатирован по мере необходимости (вот, ДД/ММ/гг)См. снимок листа Excel выше (= смешанные даты снимка в формате Excel)
Мое решение, в Великобритании - у меня даты с точками в них-такой:
03.01.17
Я решил это следующим:
14-03-12
(необходимо для мою на середине черточки)При сортировке столбца все даты за год сортируются.
Я попробовал различные варианты, но самым простым решением для меня было следующим образом...
См. изображения 1 и 2 для примера... (Примечание - некоторые поля были скрыты намеренно, так как они не способствуют тому пример). Я надеюсь, что это помогает...
Поле с - смешанный формат, который сводил меня с ума. Это, как данные непосредственно из базы данных и не было никаких лишних пробелов или сумасшедших персонажей. При отображении его в виде текстового например, 01/01/2016 отображается как '42504' тип значение, смешавшись с 04/15/2006, который показал, как является.
Поле F - где я получил длину поля с (формула лен будет в длину 5 из 10 в зависимости от формата даты). Поле имеет общий формат для простоты.
Поле г - получение месяце компонента смешанного дата - исходя из результата длина в поле F (5 или 10), я либо получить месяц из даты значение в поле, или получить месяц символов в строке.
Поле H - получили номер компонента смешанного дата - исходя из результата длина в поле F (5 или 10), я либо получить день от даты валютирования в поле, или получить День символы в строке.
Я могу сделать это за год, то создать дату из трех компонентов, что согласуется. В противном случае, я могу использовать индивидуальный день, месяц и год значения в моем анализе.
Изображение 1: Основные таблицы, показывающие значения и имена полей
Изображение 2: таблицу выше формулы соответствующие пояснения
Я надеюсь, что это помогает.
Выберите даты и выполните этот код за это..
On Error Resume Next
For Each xcell In Selection
xcell.Value = CDate(xcell.Value)
Next xcell
End Sub
Это происходит все время, когда дата обмена данными между районов в Excel. Если у вас есть контроль над программой VBA, который экспортирует данные, я предлагаю экспортировать число, представляющее дату, а не сама дата. Номер однозначно коррелирует с одной датой, независимо от форматирования и язык. Например, вместо файла CSV показывает 24/09/2010 покажет 40445.
В экспорте петли, когда вы держите в каждой ячейке, если это'с даты, преобразовать в число с помощью CLng(myDateValue). Это пример моего цикла прохождения всех строк таблицы и экспорт в CSV (обратите внимание, я также заменить запятые в строках с <запятая> тег, который я снял при импорте, но вам не нужно это):
arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value
For i = LBound(arr, 1) To UBound(arr, 1)
strLine = ""
For j = LBound(arr, 2) To UBound(arr, 2) - 1
varCurrentValue = arr(i, j)
If VarType(varCurrentValue) = vbString Then
varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
End If
If VarType(varCurrentValue) = vbDate Then
varCurrentValue = CLng(varCurrentValue)
End If
strLine = strLine & varCurrentValue & ","
Next j
'Last column - not adding comma
varCurrentValue = arr(i, j)
If VarType(varCurrentValue) = vbString Then
varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
End If
If VarType(varCurrentValue) = vbDate Then
varCurrentValue = CLng(varCurrentValue)
End If
strLine = strLine & varCurrentValue
strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
Print #intFileHandle, strLine
Next i
Я использую Mac.
Перейти в Excel предпочтения и GT; редактировать> У современных вариантов> автоматически преобразовать системную дату
Кроме того,
Перейти к таблицам & фильтры> группа дат при фильтрации.
Проблема, вероятно, началось, когда вы получили файл с другой системы, и что облажалась ваша дата функции Excel