Excel датазнач не работает
он работает, в макросе вижу что Cell из 30/11-05 превратилась в 30.11.2005, но на листе изменений нет.
На самом деле Excel сразу понимает, что это дата и с ней можно работать. Но загвоздка в том, что я не могу получить какой диапазон дат у меня закачен, с такими датами почему-то не работают функции "макс" и "мин".
Илья, Можно при импорте текстового файла явно указать формат данных нужных столбцов, причём непосредственно в Мастере Импорта [Шаг 3], либо после импорта заменить - на /
Оба предложенных варианта позволяют получить корректный результат, на всякий случай проверено ещё раз, вручную и программно (MS Excel 97/2000)
Cell.Value = DateValue(Cell.Value)
P.S. В представленном файле все "даты" представляют собой именно текст.
Если ничего из вышепредложенного не помогает, то можно использовать стандартную функцию рабочего листа =ДАТАЗНАЧ(), которая и предназначена для работы с "датами", которые MS Excel воспринимает как текст. Для опредения макс/мин, можно использовать следующие формулы :
Однако, эти формулы не будут возвращать корректный результат, если в указанном диапазоне также будут находиться даты, представленные именно в числовом формате, поэтому в Вашем конкретном случае можно использовать более универсальные формулы, например :
Все представленные формулы являются формулами массива, а значит после их ввода необходимо обязательно нажать CTRL + SHIFT + ENTER
P.S. Если и этот вариант Вам не поможет, то есть один радикальный способ борьбы с числовыми значениями, которые MS Excel интепритирует как текст.
Originally posted by pashulka
Если ничего из вышепредложенного не помогает, то можно использовать стандартную функцию рабочего листа =ДАТАЗНАЧ(), которая и предназначена для работы с "датами", которые MS Excel воспринимает как текст. Для опредения макс/мин, можно использовать следующие формулы :
Однако, эти формулы не будут возвращать корректный результат, если в указанном диапазоне также будут находиться даты, представленные именно в числовом формате, поэтому в Вашем конкретном случае можно использовать более универсальные формулы, например :
Все представленные формулы являются формулами массива, а значит после их ввода необходимо обязательно нажать CTRL + SHIFT + ENTER
P.S. Если и этот вариант Вам не поможет, то есть один радикальный способ борьбы с числовыми значениями, которые MS Excel интепритирует как текст.
Уважаемый pashulka! Спасибо Вам огромное!
Я как обычно не догадался пользоваться массивами, а они в данной ситуации очень помогают.
В связи с этим остаются 2 вопроса:
1. Можно ли присвоить переменной значение любой из этих формул, не пользуясь ячейкой Excel?
2. Пока я пытался сделать строку датой, обнаружил еще одну проблемму. При импорте макросом дат, число которых меньше 12, дата переворачивается (01/12-05 превращается в 12.01.05), и переделать ее уже не представляется возможным (мне по-крайней мере).
При импорте вручную этой проблеммы нет.
Так же данной проблеммы не существует на более ранних версиях Excel (у меня стоит Excel 2002).
Можно ли бороться с этим и если можно, то как? (может поможет тот самый радикальный способ? :) )
Примеры для импорта прилагаю.
Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), - преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ("11.09.2009") возвращает число 40067, соответствующее 11 сентября 2009 года. Но, функция ДАТАЗНАЧ() понимает только определенные форматы записи дат. Например, 2009-сент-11 она не поймет, а 11-сент-2009 - поймет.
Если на листе содержатся даты в текстовом формате, то их скорее всего не удастся правильно отфильтровать, отсортировать или отформатировать в виде дат без предварительного преобразования в числовую форму (см. статью Как EXCEL хранит дату и время ).
Функция ДАТАЗНАЧ() предназначена для преобразования в формат дат.
Синтаксис функции ДАТАЗНАЧ()
ДАТАЗНАЧ(дата_как_текст)
Дата_как_текст. Текст, представляющий собой дату в формате даты EXCEL (об этом ниже), или ссылка на ячейку с таким текстом. Например, "11.9.2009" и "11-сент-2009" введенные (без кавычек) в ячейки в текстовом формате.
Если в аргументе дата_как_текст опущен год, функция ДАТАЗНАЧ() использует текущий год по показаниям встроенных часов компьютера. Сведения о времени в аргументе дата_как_текст игнорируются.
Задача 1 Фильтрация и сортировка дат
Предположим, что столбце A установлен формат Текстовый и он содержит текстовые строки "22 сентябрь 2011"; "23 сентябрь 2011" и т.д. (без кавычек).
Постараемся отфильтровать значения, которые относятся к октябрю 2011 года.
Сначала попытаемся отфильтровать даты как есть, т.е. в формате текста.
Так как значения дат находятся в текстовом формате, то и Фильтр в столбце А будет содержать только критерии для текста.
Выбрав в Фильтре критерий Текстовые фильтры / Содержит. введите текст Октябрь и нажмите ОК. Будут отфильтрованы значения относящиеся к октябрю 2011 и 2012 годов (последняя строка).
Кроме того, если была допущена опечатка, то заметить, что отфильтровались не все значения будет очень трудно. Поэтому стоит перед фильтрацией преобразовать значения в текстовый формат. Для этого используем функцию ДАТАЗНАЧ() , записав в столбце В следующую формулу :
К столбцу В применим формат Краткая Дата и затем применим фильтр.
Как видно из рисунка выше, фильтр теперь будет содержать критерии удобные для отбора дат.
Аналогично Фильтрации работает и сортировка списка с датами. Сравните 2 сортировки выполненные для столбца А (даты в текстовом формате) и В (формат даты).
Задача 2 Вычисления с датами в текстовом формате
Для того, чтобы производить вычисления с датами функция ДАТАЗНАЧ() не нужна. Пусть в ячейке А1 хранится текстовое значение "11.9.2009". Например, чтобы прибавить к этой дате 2 дня, можно записать формулу
Форматы дат, которые функция ДАТАЗНАЧ() не понимает
Дату в текстовом формате можно записать разными способами, например "2/8/2009" или "2\8\2009" или "2001, сентября 11". Но функция ДАТАЗНАЧ() (и соответственно EXCEL) поймет далеко не все форматы, понятные человеку (функция первый пример поймет, а второй и третий не поймет). Сводная таблица форматов приведена на рисунке ниже и в файле примера (лист форматы ) .
Кроме того, EXCEL умеет преобразовывать далеко не все форматы дат (записанных в текстовой форме), которые предлагает сам. Например, если дату в ячейке ввести в формате EXCEL, который называется Длинный формат даты (предположим "22 сентября 2011 г."), и попытаться преобразовать его в дату с помощью функции ДАТАЗНАЧ() , то получим ошибку.
Альтернатива функции ДАТАЗНАЧ()
Функция ДАТАЗНАЧ() полезна если имеется длинный перечень дат (например, неудачно импортированных из MS WORD). Если дат в текстовом формате немного, то можно выделить ячейку с датой, нажать клавишу F2 , затем ENTER .
Другой способ - прибавить к значению 0, т.е. формулы =ДАТАЗНАЧ(A1) и =А1+0 эквивалентны!
О преобразовании дат из текстового формата читайте в этой статье .
ДАТАЗНАЧ() vs ДАТА()
Если значение даты содержится в 3-х разных ячейках (в А1 содержится день, в А2 - месяц и А3 - год), то преобразовать это значение можно с помощью функций ДАТАЗНАЧ() и ДАТА() :
=ДАТАЗНАЧ(A1 & "/" & A2 & "/" & A3)
В случае, если месяц в ячейке А2 введен прописью, то функция ДАТА() не справится.
В этой статье описаны синтаксис формулы и использование функции ДАТАЗНАЧ в Microsoft Excel.
Описание
Функция ДАТАЗНАЧ преобразует дату, которая хранится в виде текста, в порядковый номер, который Excel воспринимает как дату. Например, формула =ДАТАЗНАЧ("1.1.2008") возвращает число 39 448, соответствующее 1 января 2008 года. Однако следует помнить, что в зависимости от настроек даты вашего компьютера результаты функции ДАТАЗНАЧ могут отличаться от представленных в этом примере.
Функция ДАТАЗНАЧ полезна в случаях, когда лист содержит даты в текстовом формате, которые требуется отфильтровать, отсортировать или отформатировать в виде дат или использовать в вычислениях с датами.
Чтобы дата была отформатирована в виде даты, необходимо применить к ячейке формат даты. Ссылки на дополнительные сведения о выводе чисел в качестве дат можно найти в разделе См. также.
Синтаксис
Аргументы функции ДАТАЗНАЧ описаны ниже.
Дата_как_текст — обязательный аргумент. Текст, представляющий дату в формате даты Excel, или ссылка на ячейку с таким текстом. Например, "30.01.2008" и "30-янв-2008" — это текстовые строки в кавычках, представляющие даты.
Если в аргументе дата_как_текст опущен год, функция ДАТАЗНАЧ использует текущий год по показаниям встроенных часов компьютера. Сведения о времени в аргументе дата_как_текст игнорируются.
Примечания
В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 447 дней.
Большинство функций автоматически преобразует даты в числовой формат.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция DATEVALUE (ДАТАЗНАЧ) в Excel используется для тех ситуаций, когда дата в ячейке указана в виде текста. С её помощью Excel конвертирует текстовое значение даты в число, которое система распознает как дату.
Какие данные отображает
Синтаксис
Аргументы
date_text (дата_как_текст): это аргумент даты указанной в ячейке в формате текста или результат какой-либо формулы в виде текста;
- для правильной работы функции, текст, который нам необходимо преобразовать в формулу важно указать в кавычках (прим. “01/01/2016”);
- если date_text это результат какого-либо вычисления, важно чтобы ячейка была в текстовом формате, для корректной работы функции.
Дополнительная информация
DATEVALUE (ДАТАЗНАЧ) возвращает числовое значение даты:
Примеры использования функции DATEVALUE (ДАТАЗНАЧ)
Пример №1. Преобразование даты в текстовом формате в число
Пример №2. Преобразование даты в текстовом формате с помощью формулы
Когда дата введена как текст в ячейку, вы можете использовать функцию DATAVALUE (ДАТАЗНАЧ) , чтобы получить числовое значение даты. Обратите внимание, что дата должна быть в текстовом формате.
Эта функция полезна, когда у вас есть дата в текстовом формате, по которой вы хотите фильтровать, сортировать данные в таблице. В текстовом формате, вы не сможете сортировать даты и проводить вычисления в Excel, так как система будет рассматривать его как текст, а не число.
Пример №3. Использование функции без указания года
Если значение года не указано, то система использует текущий год при отображении значения даты (текущий год 2016 в указанном примере).
Пример №4. Использование функции без указания дня
Если в дате не указан день, то формула использует первый день месяца.
Читайте также: