Формат даты в vba excel

Формат даты в vba excel

6 Jandrejc [2011-08-18 13:53:00]

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

Итак, в основном в одном столбце есть два разных формата: dd.mm.yyyy и mm/dd/yyyy . Я пытаюсь написать VBA script, чтобы изменить формат всех дат в столбце yyyy-mm-dd . Это то, что у меня есть до сих пор:

Я знаю, что это не элегантный кусок кода, но я начинаю с VBA, поэтому, пожалуйста, простите меня. Проблема с этим кодом заключается в том, что он просто переписывает неизмененный столбец в столбец B, когда я изменяю аргумент в функции Format от yyyy-mm-dd до dd/mm/yyyy , он работает, но только для дат в формате mm/dd/yyyy и оставляет dd.mm.yyyy нетронутым. Я был бы признателен за любые советы.

date vba format excel

6 ответов

8 Решение aevanko [2011-08-18 14:21:00]

ОБНОВЛЕНО: NEW ANSWER

Вот решение, которое сделает эту работу! Подпрограмма включает в себя функцию, которая выполняет замену (сама функция действительно полезна!). Запустите sub и все записи в столбце A будут исправлены.

Поместите эту функцию в тот же модуль:

Как это работает.. У меня есть отличная функция RegexReplace, которая позволяет выполнять замену с использованием регулярных выражений. Submearly петли через вашу колонку A и заменяет регулярное выражение для тех двух случаев, о которых вы упомянули. Причина, по которой я сначала использую Instr(), — это определить, нужна ли ему замена и какой вид. Вы можете технически пропустить это, но делать замену на ячейках, которые не нужны, это действительно дорого. В конце я отформатирую ячейку в пользовательский формат даты независимо от того, что внутри для безопасной меры.

Если вы не знакомы с Regex (для ссылки: http://www.regular-expressions.info/), я использую следующее выражение:

  • Каждый элемент в() — группы захвата — иначе, материал, который вы хотите испортить
  • d обозначает число [0-9].
  • <2>означает 2 of, а <4>- 4 of. Я был здесь явно для безопасности.
  • efore. в первой замене необходимо, так как "." имеет особое значение.
  • В регулярном выражении VBA вы ссылаетесь на группы захвата, используя $+ no. группы. Вот как я переворачиваю порядок из 3-х элементов.

Для этого вам не нужен VBA. Эта формула рабочего листа с одним слоем сделает трюк:

Это предполагает, что день и месяц всегда указываются как двузначные числа (например, всегда 03, а не только 3), а год имеет четыре цифры (т.е. "ограничен" до 1000-9999). Но если это не относится к вам, то формула может быть легко скорректирована в соответствии с вашими потребностями.

Читайте также:  Виджеты для андроид 4pda

Посмотрите, делает ли это то, что вы хотите. Возможно, вам придется немного адаптировать его для своего приложения.

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

1 pnuts [2014-08-29 22:17:00]

@Jean-François Corbett имеет решение формулы, которое работает, но может быть сокращено более чем наполовину из-за отклонения сообщения об ошибке (на основе того, что #VALUE! является информативным) и другого IF, оба DATE, применяя IFERROR чем ISERROR и SUBSTITUTE вместо одного LEFT, MID, RIGHT set:

Это применимо к интерпретации, упомянутой @Issun в комментарии к OP, и предполагает, что вывод будет связан с ячейками, отформатированными yyyy-mm-dd .

Может быть написано с помощью такой подпрограммы:

Когда я пытаюсь вывести дату в Excel в формате dd/mm/yyyy hh , у меня это получается, дата выглядит, как и предполагалось:

В случае выше я вывожу дату и время следующим образом:

Но вывести дату в формате dd/mm/yyyy hh:mm:ss у меня не получается, вывод выглядит следующим образом:

3 ответа 3

  1. Дату надо присваивать с типом Date, а не String.
  2. Формат вывода менять через формат ячеек, а не через форматирование строки.

Можно сделать как-то так:

Обращаю внимание, что в строке формата используется dd/mm/yyyy , а не dd/mm/yyyy — в случае неэкранированных слешей разделитель будет взят из региональных настроек.

  1. Заранее задать формат для диапазона
  2. Как-то более нормально создавать дату — кажется, я перемудрил.

Возвращает значение типа Variant ( String), которое содержит выражение, отформатированное в соответствии с указаниями, содержащимися в выражении формата.

Предупреждение Возникла проблема с использованием этой функции. Последний понедельник в некоторых годовых годах может быть возвращен в неделю 53, когда она должна быть неделей 1. Дополнительные сведения и временное решение можно найти в статье функции Format и datepart могут возвращать неверный номер недели для последнего месяца в году.

Format( выражение [ , формат ] [ , первый_день_недели ] [ , первая_неделя_года ] )

Функция Format имеет следующие аргументы:

Обязательный аргумент. Любое допустимое выражение.

Необязательный. Допустимое именованное или определяемое пользователем выражение формата.

Необязательный. Константа, задающая первый день недели.

Необязательный аргумент. Константа, задающая первую неделю года.

Аргумент Format может использовать разнообразные параметры, в зависимости от типа данных аргумента Expression . Дополнительные сведения см. в статьях, указанных в таблице ниже.

Читайте также:  Чем болела любовь полищук

Аргумент первый_день_недели может принимать следующие значения:

Используются параметры API NLS.

Воскресенье (по умолчанию)

Аргумент первая_неделя_года может принимать следующие значения:

Используются параметры API NLS.

Начинается с недели, на которую приходится 1 января (по умолчанию).

Первой неделей считается та, которая содержит хотя бы четыре дня нового года.

Первой неделей считается первая полная неделя года.

Используйте стандартные именованные числовые форматы или создайте пользовательские числовые форматы.

Значения даты и времени

Используйте стандартные именованные форматы даты и времени или создайте пользовательские форматы даты и времени.

Серийные номера даты и времени

Используйте форматы даты и времени или числовые форматы.

Создайте пользовательские форматы строк.

Если вы попытаетесь отформатировать число без задания формата, функция Format будет работать аналогично функции Str, хотя и с поддержкой международных форматов. Однако положительные числа, отформатированные как строки с использованием функции Format, не будут включать начальный пробел, зарезервированный для знака значения; с другой стороны, в случае преобразования с помощью функции Str начальный пробел сохранится.

При форматировании нелокализованных числовых строк следует использовать определяемый пользователем числовой формат, чтобы обеспечить получение желаемых результатов.

Примечание: Если для свойства Calendar задано значение Gregorian, а для аргумента формат указано форматирование даты, предоставленное выражение должно иметь тип Gregorian. Если для свойства Calendar в Visual Basic задано значение Hijri, выражение должно иметь тип Hijri.

Если используется григорианский календарь, значение символов выражения формат не изменяется. Если используется календарь Хиджра, все символы формата даты (например, dddd, mmmm, yyyy) имеют то же значение, но применяются к календарю Хиджра. Символы формата остаются английскими; символы, отображаемые в текстовом виде (например, AM и PM), выводят строку (на английском или арабском языке), связанную с этим символом. Диапазон некоторых символов при использовании календаря Хиджра изменяется.

Отображает полное название месяца (у названий месяцев Хиджра нет сокращений).

Примеры

Использование функции Format в выражении. Функцию Format можно использовать в любых выражениях. Можно, например, использовать ее в запросе в виде части псевдонима поля или в свойстве Данные текстового поля в форме или отчете. Ниже представлен пример выражения, которое можно использовать в свойстве отчета Фильтр, чтобы вывести только записи, относящиеся к прошлой неделе.

В этом примере в источнике записей отчета есть поле "Дата", содержащее дату изменения каждой конкретной записи и использующееся в отчете. Во время выполнения отчета результаты фильтруются и отображаются только те записи, в которых значение недели в поле "Дата" (Format([Дата],"ww")) равно прошлой неделе (Format(Now(),"ww")-1).

Читайте также:  Opcache revalidate freq битрикс как изменить

Использование функции Format в коде VBA

Примечание: В примерах ниже показано, как использовать эту функцию в модуле Visual Basic для приложений (VBA). Чтобы получить дополнительные сведения о работе с VBA, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.

В данном примере показаны различные варианты использования функции Format для форматирования значений с помощью предварительно заданных форматов и форматов, определенных пользователем. Для разделителя даты ( /), разделителя времени ( 🙂 и литералов AM/ PM фактические отформатированные выходные данные, отображаемые системой, зависят от ее региональных параметров. В среде разработки для отображения времени и даты используется краткий формат времени и даты, заданный в региональных параметрах программы. При отображении в работающей программе применяется краткий формат даты и времени из региональных параметров системы, который может отличаться от региональных параметров программы. В этом примере используется локаль "Английский (США)".

MyTime и MyDate отображаются в среде разработки с использованием текущих системных параметров для краткого формата времени и даты.

Использование функции Format с пустыми строками

В Microsoft Access версии 2,0 и более ранних версиях можно использовать функцию Format , чтобы возвращать одно значение для пустая строка, а другое — для значения. Например, вы можете использовать выражение формата, например следующее, с функцией Format , чтобы вернуть соответствующее строковое значение из кода:

В Microsoft Access 97 и более поздних версиях необходимо отдельно проверить, не равно ли значение Null, а затем вернуть соответствующее значение с учетом результата. Например, можно использовать функцию IIf в выражении с функцией Format:

varX = IIf(IsNull(varStrX),"Null", Format(varStrX, "@;ZLS"))

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

При преобразовании базы данных из Microsoft Access 2.0 или более ранней версии в Microsoft Access 2002 или более поздней версии необходимо изменить текст программы так, чтобы проверка для случая Null выполнялась отдельно.

Ссылка на основную публикацию
Adblock detector