Изменить формат даты vba excel
Microsoft Excel функция Format принимает выражение даты и возвращает его в виде форматированной строки.
Функция Format - это встроенная в Excel функция, которая относится к категории функций даты и времени. Её можно использовать как функцию VBA в Excel.
В качестве функции VBA вы можете использовать эту функцию в коде макроса, который вводится через редактор Microsoft Visual Basic Editor.
Синтаксис
Синтаксис функции Format в Microsoft Excel:
Аргументы или параметры
expression Значение даты для форматирования. format Необязательно. Это формат, применяемый к expression . Вы можете определить свой собственный формат или использовать один из названных форматов, предопределенных в Excel, например:
Формат | Пояснение |
---|---|
General Date | Отображает дату на основе настроек вашей системы |
Long Date | Отображает дату на основе настройки длинной даты вашей системе |
Medium Date | Отображает дату на основе средней настройки даты вашей системы |
Short Date | Отображает дату на основе настройки короткой даты вашей системы |
Long Time | Отображает время, основанное на настройке длительного времени вашей системы |
Medium Time | Отображает время на основе средней настройки времени вашей системы |
Short Time | Отображает время на основе настройки короткого времени вашей системы |
Константа | Значение | Пояснение |
---|---|---|
vbUseSystem | 0 | Использует настройку NLS API |
VbSunday | 1 | воскресенье (по умолчанию, если параметр не указан) |
vbMonday | 2 | понедельник |
vbTuesday | 3 | вторник |
vbWednesday | 4 | среда |
vbThursday | 5 | четверг |
vbFriday | 6 | пятница |
vbСуббота | 7 | Суббота |
Константа | Значение | Пояснение |
---|---|---|
vbUseSystem | 0 | Использует настройку NLS API |
vbFirstJan1 | 1 | Неделя, содержащая 1 января |
vbFirstFourDays | 2 | Первая неделя, которая имеет не менее 4 дней в году |
vbFirstFullWeek | 3 | Первая полная неделя года |
Возвращаемое значение
Функция FORMAT возвращает строковое значение.
Применение
- Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Тип функции
Пример (как функция VBA)
Функцию FORMAT можно использовать только в коде VBA в Microsoft Excel.
Рассмотрим несколько примеров функции Excel FORMAT чтобы понять, как использовать Excel функцию FORMAT в коде Excel VBA:
Свойство Format можно настроить до заранее заранее установленных форматов даты и времени или использовать настраиваемые форматы для типа данных Date/Time.
Параметры
Предопределяемые форматы
В следующей таблице показаны предопределенные параметры свойств Format для типа данных Date/Time.
Настраиваемые форматы
Вы можете создать настраиваемые форматы дат и времени с помощью следующих символов.
Символ | Описание |
---|---|
: (двоеточие) | Разделитель компонентов времени. Сепараторы устанавливаются в региональных параметрах Windows. |
/ | Разделитель компонентов даты. |
c | То же самое, что и заранее задатки формата General Date. |
d | День месяца в одной или двух числовых цифрах, по мере необходимости (от 1 до 31). |
dd | День месяца в двух числовом цифре (от 01 до 31). |
ddd | Первые три буквы буднего дня (от солнца до Сб). |
dddd | Полное имя буднего дня (с воскресенья по субботу). |
ddddd | То же самое, что и заранее задатки формата. |
dddddd | То же самое, что и заранее задатки формата Long Date. |
w | День недели (от 1 до 7). |
ww | Неделя года (от 1 до 53). |
m | Месяц года в одной или двух числовых цифрах по мере необходимости (от 1 до 12). |
mm | Месяц года в двух числовом цифре (от 01 до 12). |
mmm | Первые три буквы месяца (с января по декабрь). |
mmmm | Полное имя месяца (с января по декабрь). |
q | Дата отображается как квартал года (от 1 до 4). |
y | Число дня в году (от 1 до 366). |
yy | Последние две цифры года (от 01 до 99). |
yyyy | Полный год (от 0100 до 9999). |
h | Час в одной или двух цифрах, по мере необходимости (от 0 до 23). |
hh | Час в двух цифрах (от 00 до 23). |
n | Минута в одну или две цифры, по мере необходимости (от 0 до 59). |
nn | Минута в две цифры (от 00 до 59). |
s | Второй в одной или двух цифрах, по мере необходимости (от 0 до 59). |
ss | Второе место в двух цифрах (от 00 до 59). |
ttttt | То же самое, что и заранее задав формат. |
AM/PM | Двенадцать часов с буквами верхнего шкафа "AM" или "PM" по мере необходимости. |
am/pm | Двенадцать часов с буквами "am" или "pm" по мере необходимости. |
A/P | Двенадцать часов с буквой верхнего шкафа "A" или "P" по мере необходимости. |
a/p | Двенадцать часов с нижней буквой "a" или "p" по мере необходимости. |
AMPM | Двенадцать часов с соответствующим конструктором утреннего и дневного времени, как определено в региональных параметрах Windows. |
Настраиваемые форматы отображаются в соответствии с настройками, указанными в региональных параметрах Windows. Пользовательские форматы, несовместимые с настройками, указанными в региональных параметрах Windows, игнорируются.
Если вы хотите добавить запятую или другой сепаратор в настраиваемый формат, заключив сепаратор в кавычках следующим образом: mmm d", "yyyy .
Пример
Ниже приводится пример настраиваемого формата даты и времени.
Значение | Отображение |
---|---|
ddd", "mmm d", "yyyy | Пн, 2 июня 1997 г. |
mmmm dd", "yyyy | 02 июня 1997 г. |
"This is week number "ww | Это неделя Номер 22 |
"Today is "dddd | Сегодня вторник |
Для отображения "A.D" можно использовать настраиваемый формат. до или "B.C". через год в зависимости от того, вошел ли положительный или отрицательный номер. Чтобы увидеть работу этого настраиваемого формата, создайте новое поле таблицы, установите его тип данных на номер и введите формат следующим образом:
Положительные цифры отображаются в качестве лет с помощью "A.D". до года. Отрицательные цифры отображаются в качестве лет с "B.C". после года.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Format – это функция, которая преобразует число, дату или строку в текст, отформатированный в соответствии с именованным выражением формата или инструкциями, составленными из специальных символов.
Синтаксис и параметры
Format(Expression, [FormatExpression], [FirstDayOfWeek], [FirstWeekOfYear])
- Expression – любое допустимое выражение (переменная), возвращающее числовое значение или строку (обязательный параметр).
- FormatExpression – выражение формата, именованное или содержащее инструкции из специальных символов (необязательный параметр).
- FirstDayOfWeek – константа, задающая первый день недели (необязательный параметр).
- FirstWeekOfYear – константа, задающая первую неделю года (необязательный параметр).
Именованные выражения форматов
Именные форматы даты и времени
Имя формата | Описание |
---|---|
General Date | Стандартное отображение даты и времени в соответствии с параметрами системы. |
Long Date | Длинный формат даты. |
Medium Date | Средний формат даты. |
Short Date | Краткий формат даты. |
Long Time | Длинный формат времени. |
Medium Time | Средний формат времени. |
Short Time | Краткий формат времени. |
Проверьте отображение даты и времени с использованием именованных форматов на вашем компьютере при помощи следующего кода VBA Excel:
Скорее всего, результат будет таким:
Именованные форматы чисел
Имя формата | Описание |
---|---|
General Number | Стандартное отображение числа без знака разделителя групп разрядов. |
Currency | Денежный формат. |
Fixed | Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части. |
Standard | Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части. |
Percent | Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа. |
Scientific | Отображение числа в экспоненциальном виде. |
Yes/No | Возвращается «Нет», если число равно 0, иначе отображается «Да». |
True/False | Возвращается «Ложь», если число равно 0, иначе отображается «Истина». |
On/Off | Возвращается «Выкл», если число равно 0, иначе отображается «Вкл». |
Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
Получаем следующий результат:
Вместо вопросительного знака в отображении числа в формате Currency, по идее, должен быть знак валюты (₽ или руб.).
Специальные символы для выражений форматов
Символы для форматов даты и времени
Символ | Описание |
---|---|
Точка (.) | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца в виде числа. |
Пробел | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца прописью. |
Двоеточие (:) | Разделитель компонентов времени (часы, минуты, секунды). |
d | День в виде числа без нуля в начале (1–31). |
dd | День в виде числа с нулем в начале (01–31). |
m | Месяц в виде числа без нуля в начале (1–12). Если (m) следует после (h) или (hh), отображаются минуты (0–59). |
mm | Месяц в виде числа с нулем в начале (01–12). Если (mm) следует после (h) или (hh), отображаются минуты (00–59). |
mmm | Месяц прописью в сокращенном виде (янв–дек). |
mmmm | Полное название месяца (январь–декабрь). |
y | День года в виде числа (1–366). |
yy | Год в виде 2-значного числа (00–99). |
yyyy | Год в виде 4-значного числа (1900–9999). |
h | Часы в виде числа без нуля в начале (0–23). |
hh | Часы в виде числа с нулем в начале (00–23). |
n (m) | Минуты в виде числа без нуля в начале (0–59). |
nn (mm) | Минуты в виде числа с нулем в начале (00–59). |
s | Секунды в виде числа без нуля в начале (0–59). |
ss | Секунды в виде числа с нулем в начале (00–59). |
В этой таблице перечислены далеко не все символы для выражений форматов даты и времени. Вы можете ознакомиться со всеми символами, в том числе и для форматирования чисел, на сайте разработчика.
Примеры отображения даты с помощью разных по количеству наборов символа d:
Символы для числовых форматов
Примеры использования символов в выражениях числовых форматов VBA Excel:
Символы для текстовых форматов
Символ | Описание |
---|---|
At-символ (@) | Заполнитель для символов, отображающий знак или пробел. |
Амперсанд (&) | Заполнитель для символов, отображающий знак или ничего (пустая строка). |
Меньше ( <) | Принудительный перевод всех буквенных символов в нижний регистр. |
Больше (>) | Принудительный перевод всех буквенных символов в верхний регистр. |
Примеры использования символов в выражениях строковых форматов VBA Excel:
Форматы для различных значений одного выражения
Различные форматы для разных числовых значений
В выражении формата для чисел предусмотрено от одного до четырех разделов, отделяемых друг от друга точкой с запятой. Отображаемая строка зависит от значения, возвращенного параметром Expression функции Format.
Количество разделов | Результат форматирования |
---|---|
Один раздел | Выражение формата применяется ко всем значениям. |
Два раздела | Первый раздел применяется к положительным значениям и нулям, второй – к отрицательным значениям. |
Три раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям. |
Четыре раздела | Первый раздел применяется к положительным значениям, второй – к отрицательным значениям, третий – к нулям, четвертый – к значениям Null. |
Пример использования четырех разделов в выражении формата числовых значений:
Date – это функция, которая возвращает значение текущей системной даты. Тип возвращаемого значения – Variant/Date.
Синтаксис
Пример
Функция DateAdd
DateAdd – это функция, которая возвращает результат прибавления к дате указанного интервала времени. Тип возвращаемого значения – Variant/Date.
Синтаксис
Параметры
Параметр | Описание |
---|---|
interval | Обязательный параметр. Строковое выражение из спецсимволов, представляющее интервал времени, который требуется добавить. |
number | Обязательный параметр. Числовое выражение, задающее количество интервалов, которые необходимо добавить. Может быть как положительным (возвращается будущая дата), так и отрицательным (возвращается предыдущая дата). |
date | Обязательный параметр. Значение типа Variant/Date или литерал, представляющий дату, к которой должен быть добавлен интервал. |
Таблицу аргументов (значений) параметра interval смотрите в параграфе «Приложение 1».
Примечание к таблице аргументов: три символа – y, d, w – указывают функции DateAdd на один день, который необходимо прибавить к исходной дате number раз.
Пример
Функция DateDiff
DateDiff – это функция, которая возвращает количество указанных интервалов времени между двумя датами. Тип возвращаемого значения – Variant/Long.
Синтаксис
Параметры
Параметр | Описание |
---|---|
interval | Обязательный параметр. Строковое выражение из спецсимволов, представляющее интервал времени, количество которых (интервалов) требуется вычислить между двумя датами. |
date1, date2 | Обязательные параметры. Значения типа Variant/Date , представляющие две даты, между которыми вычисляется количество указанных интервалов. |
firstdayofweek | Необязательный параметр. Константа, задающая первый день недели. По умолчанию – воскресенье. |
firstweekofyear | Необязательный параметр. Константа, задающая первую неделю года. По умолчанию – неделя, в которую входит 1 января. |
Таблицу аргументов (значений) параметра interval смотрите в параграфе «Приложение 1».
Примечание к таблице аргументов: в отличие от функции DateAdd , в функции DateDiff спецсимвол "w" , как и "ww" , обозначает неделю. Но расчет осуществляется по разному. Подробнее об этом на сайте разработчиков.
Параметры firstdayofweek и firstweekofyear определяют правила расчета количества недель между датами.
Таблицы констант из коллекций firstdayofweek и firstweekofyear смотрите в параграфах «Приложение 2» и «Приложение 3».
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
Для справки: выражение Range("1:1048576") описывает диапазон с 1 по 1048576 строку, где число 1048576 – это номер последней строки на рабочем листе Excel 2016.
В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
- Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
- Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
- Range("A1:C3").Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Для справки: порядковый номер ячейки в диапазоне считается построчно слева направо с перемещением к следующей строке сверху вниз.
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6) .
Свойства ячейки (объекта Range)
Свойство | Описание |
---|---|
Address | Возвращает адрес ячейки (диапазона). |
Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20") . |
EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2") . |
Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
Name | Возвращает или задает имя ячейки (диапазона). |
NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@" . |
Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. |
Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
Читайте также: