Dateadd vba excel

Урок 15 по VBA — функции работы с датой и временем

И так, в этой по своей природе унылой публикации я кратко рассмотрю vba функции даты и времени, которые позволяют получить или установить параметры даты и времени, задать таймер выполнения заданного блока кода или сценария в целом. Также в конце статьи будет затронута функция vba языка format. Которая отвечает за форматирование данных.

Функции VBA даты и времени

Date () – позволяет получить значение текущей даты, установленной в системе.

Time () – вернет установленное в системе время

Now () – комбинированная функция, так как она позволяет получить значение системной даты и времени.

DateAdd (интервал, число, дата) – позволяет прибавить к заданной дате (параметр “дата”) указанное количество лет, кварталов, месяцев или секунд. На выходе мы получим следующее значение: дата + (число * интервал).

DateDiff (интервал, дата, дата2) – позволяет получить разницу между заданными датами, например. В годах, месяцах и так далее, разница определяется параметром “интервал”.

DatePart (интервал, дата) – позволяет получить заданную часть даты, например, только значение года, месяца или дня недели. Результат возврата регулируется параметром “интервал”.

DateSerial (год, месяц, день) – данная функция vba принимает три числовые значения, по которым возвращается дата.

DateValue (строка) – аналог предыдущей функции, но тут в качестве параметра мы передаем не числа. А строковое значение, на основе которого будет возвращаться дата, vba работа с датами.

Day (дата) – позволяет получить значение текущего дня (если отсутствует параметр “дата”) или для заданной даты.

Year (дата) – позволяет получить значение текущего года (если отсутствует параметр “дата”) или для заданной даты.

Month (дата) – позволяет получить значение текущего месяца (если отсутствует параметр “дата”) или для заданной даты.

Weekday (дата) – позволяет получить значение текущей недели (если отсутствует параметр “дата”) или для заданной даты.

Hour (время) – позволяет получить текущее значение часов (если отсутствует параметр “время”) или для заданного времени, vba дата и время.

Minute (время) – позволяет получить текущее значение минут (если отсутствует параметр “время”) или для заданного времени.

Second (время) – позволяет получить текущее значение секунд (если отсутствует параметр “время”) или для заданного времени.

Timer () – удобная функция для определения промежутка времени, ушедшего на выполнение заданного блока кода. Сама функция возвращает целое количество секунд, которые прошли начиная с полуночи.

TimeSerial (часы, минуты, секунды) – позволяет получить время, заданное тремя параметрами

TimeValue (строка) – работает аналогично предыдущей функции, но для получения времени, передается не целое значение, а строковое.

MonthName (числовое значение) – VBA функция позволяет получить название месяца, в качестве параметра указываем номер месяца.

WeekDay (дата) — задает возвращает имя месяца словами по его номеру. Возвращаемое значение зависит от региональных настроек. Если они русские, то вернется русское название месяца.

Помимо указанных выше vba функций даты и времени, можно использовать и такие вариации:

Date (дата) – позволяет задать системную дату

Time (время) – позволяет задать системное время.

В приведенных выше функциях даты и времени vba языка используется параметр “интервал”, который может принимать следующие значения:

VBA функции форматирование данных

Для форматирования данных в языке VBA используется функция Format , которая позволяет сформировать данные любого встроенного типа, используя заданный образец. Общий синтаксис функции format:

Format (Выражение, [“формат”, [первый день недели, [первая неделя года]]]) – тут параметр “выражение” является любым допустимым значением. Формат – необязательный параметр, который задает формат данных, должен заключаться в кавычки. Остальные два параметра также являются необязательными, и задают день недели, который нужно считать первым, и первую неделю года.

Читать еще: Неизвестный формат файла excel

Параметр формат может принимать следующие значения:

General Number – числовое значение без выделения разрядов тысяч, например, 12150,2003
Currency – денежный формат, выделяются тысячные разряды и два знака после запятой, например, 255,33р.
Fixed – числовое значение без выделения разрядов тысяч и двумя знаками после запятой, например, 22439,12.
Standart – числовое значение, которое умножается на 100, при этом, остаются два знака после запятой и символ процента, например, 55,63%.
Scientific – числовой формат для научных вычисление, например, 5,23Е+03
Yes/No – данный параметр определяет, что функция вернет “Да” если форматированное выражение содержит строку Нет, или ненулевое значение и “Нет” в противном случае.
True/False – аналог предыдущего параметра, но для строк “Истина” и “Ложь”.
On/Off – для строк вида “Вкл” и “Выкл”.
General Date – форматирование выражения в соответствии с установленными в системе стандартами даты и времени, например, 10.11.02 12:25:50
Long Date – вывод полного формата даты, в зависимости от установок в системе, например, 10 октября 2013 г, vba функции даты.
Medium Date – средний формат дат, например, 10-Окт-13.
Short Date – короткий вывод формата даты, например, 10.10.13.
Long Time – вывод в формате времени (часы, минуты, секунды), например, 12:20:40.
Medium Time – 12 часовой формат времени (часы, минуты), например, 05:30.
Short Time — 24 часовой формат времени (часы, минуты), например, 17:30.

Спасибо за внимание. Автор блога Владимир Баталий

Open Notes

Обо всём, что мне интересно

Полезные команды VBA

Пополняемый список полезных отрывков кода VBA для выполнения часто востребованных действий в MS Excel.

Служебные команды для ускорения скорости выполнения макроса:

‘Отключение отображения выполняемых действий Application.ScreenUpdating = False ‘Предотвращение появления предупреждающих сообщений Application.DisplayAlerts = False ‘Предотвращение появления предупреждения об обновлении связей данных Application.AskToUpdateLinks = False ‘Очистка буфера обмена Application.CutCopyMode = False

Проверка имени пользователя, запустившего макрос:

Чтобы проверить, какой пользователь открыл книгу Excel можно использовать один из следующих вариантов:

If Application.UserName = «Имя_автора_документа» Then .

If Environ(«username») = «user» Then .

Поиск последней строки таблицы:

Set myWSheet = ThisWorkbook.Sheets(«Имя_листа») With myWSheet ‘Определение индекса последней строки таблицы lastRow = .Cells(Rows.Count, 1).End(xlUp).Row ‘Определение значения в ячейки последней строке столбца A lastARow = .Range(«A» & lastRow).Value End With

Замена формулы на значение:

Добавление нового листа с именем после всех существующих:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = «Имя_листа»

Как узнать последний день предыдущего месяца:

LastMonthDay = DateAdd(«d», -1, DateSerial(Year(dtDate), Month(dtDate), 1))

Определение оставшихся дней месяца:

dToEndOfMonth = DateDiff(«d», dFrom, DateAdd(«d», -1, _ DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))

Номер текущего дня в неделе (воскресенье — первый день):

DayOfWeek = DatePart(«w», dToday)

Создание нового файла из текущего:

pathNewBook = «C:Temp» nameNewBook = «Имя_нового_файла.xls» Workbooks.Add ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook ActiveWorkbook.Close True

Сохранить текущий файл в формате CSV

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

ActiveWorkbook.SaveAs FileName:=»Name.csv», FileFormat:=xlCSV, _ CreateBackup:=False, Local:=True ActiveWorkbook.Saved = True ActiveWorkbook.Close True

Копирование данных из одного файла в другой:

wbPath = «C:Temp» wbName = «Имя_файла_откуда_копируем.xls» Workbooks.Open (wbPath & wbName) Set WB = Workbooks(wbName) WB.Sheets(«Лист 1»).Range(«A1:С10»).Copy Sheet(«Лист_в_текущем_файле»).Range(«A2»).PasteSpecial xlPasteValues

Чтобы открыть файл только для чтения, следует использовать:

Workbooks.Open (Filename:=wbPath & wbName, ReadOnly:=True)

Предотвращение ошибки при неудачном поиске значения в таблице:

Set DateRowObj = WB.Sheets(«Имя_листа»).Range(«A:A»)._ Find(What:=dtToAsDate, LookIn:=xlFormulas) If (DateRowObj Is Nothing) Then WB.Close False MsgBox «Данные не найдены.» Else DateRow = DateRowObj.Row ‘Номер строки с искомым значением End If

Как получить имя активной книги Excel без его расширения (без .xls либо без .xlsx):

wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, «.») — 1)

Читать еще: Как остановить макрос vba excel

Проверка существования файла:

fPath = «C:Temp» fName = «Файл.txt» If Dir(fPath & fName) = «» Then MsgBox «Файл не найден:» & Chr(13) & fPath & fName Exit Sub End If

Кнопка, скрывающая/разворачивающая часть таблицы:

Private Sub tbVid_Click() Application.ScreenUpdating = False If tbVid Then tbVid.Caption = «Скрыть» ActiveSheet.Rows(«2:29»).Hidden = False Else tbVid.Caption = «Развернуть» ActiveSheet.Rows(«2:29»).Hidden = True End If End Sub

Обновление сводной таблицы:

currPath = ThisWorkbook.Path currWBName = ThisWorkbook.Name ListName.PivotTables(«СводнаяТаблица1»).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & «[» & _ currWBName & «]Лист1!R1C1:R10C5»)

Обращение к элементам Frame:

Замена #ДЕЛ/0! в диапазоне:

Selection.Replace What:=»#DIV/0!», Replacement:=»», LookAt:=xlPart,_ SearchOrder:=xlByRows, MatchCase:=False,_ SearchFormat:=False, ReplaceFormat:=False

Количество строк в отфильтрованной таблице:

Быстро убрать лишние пробелы в диапазоне:

Программно снять защиту с листа:

Работа с диапазоном

Умножить диапазон на число:

ThisWorkbook.Sheets(1).Range(«A1:A10») = _ ThisWorkbook.Sheets(1).Evaluate(«A1:A10» & «*80»)

Добавить ко всем значениям диапазона строку:

ThisWorkbook.Range(«A1:A10»).Value = _ Evaluate(«=»»» & addTxt & «»» & » & ThisWorkbook.Range(«A1:A10»).Address)

Сортировка выбранного столбца в сводной таблице

Col = Selection.Column ‘Номер выбранного столбца ColMax = ActiveSheet.PivotTables(«СводнаяТаблица»).PivotColumnAxis. _ PivotLines.Count If Col — 1 <= ColMax And Col 1 Then ActiveSheet.PivotTables(«СводнаяТаблица»).PivotFields(«Label»).AutoSort _ xlDescending, » «, ActiveSheet.PivotTables(«СводнаяТаблица»). _ PivotColumnAxis.PivotLines(Col — 1), 1 End If

Счетчик времени выполнения процедуры

‘Счётчик, ставится в начале процедуры StartUpdDate = Now ‘Сообщение, выводится в конце процедуры MsgBox «Данные обновлены за » & Fix(1440 * (Now – StartUpdDate)) & » мин. » & 86400 * (Now – StartUpdDate) Mod 60 & » сек.»

Функция транслитерации с русского на английский

Function Translit(Txt As String) As String Txt = Txt Rus = Array(«ий», «ый», «ъе», «ъя», «ъю», _ «ъё», «ье», «ья», «ью», «ьё», «а», «б», «в», «г», _ «д», «е», «ё», «ж», «з», «и», «й», «к», «л», _ «м», «н», «о», «п», «р», «с», «т», «у», «ф», «х», _ «ц», «ч», «ш», «щ», «ъ», «ы», «ь», «э», «ю», «я», _ «ИЙ», «ЫЙ», «ЪЕ», «ЪЯ», «ЪЮ», _ «ЪЁ», «ЬЕ», «ЬЯ», «ЬЮ», «ЬЁ», «А», «Б», «В», «Г», _ «Д», «Е», «Ё», «Ж», «З», «И», «Й», «К», «Л», _ «М», «Н», «О», «П», «Р», «С», «Т», «У», «Ф», «Х», _ «Ц», «Ч», «Ш», «Щ», «Ъ», «Ы», «Ь», «Э», «Ю», «Я», _ » «, «_», «?», _ «a», «b», «c», «d», «e», «f», «g», «h», «i», «j», «k», «l», «m», _ «n», «o», «p», «q», «r», «s», «t», «u», «v», «w», «x», «y», «z», ««», «»») Eng = Array(«y», «y», «ye», «ya», «yu», _ «yo», «ye», «ya», «yu», «yo», «a», «b», «v», «g», _ «d», «e», «yo», «zh», «z», «i», «y», «k», «l», «m», _ «n», «o», «p», «r», «s», «t», «u», «f», «h», «ts», _ «ch», «sh», «sch», «», «y», «», «eh», «u», «ya», _ «Y», «Y», «Ye», «Ya», «Yu», _ «Yo», «Ye», «Ya», «Yu», «Yo», «A», «B», «V», «G», _ «D», «E», «Yo», «Zh», «Z», «I», «Y», «K», «L», «M», _ «N», «O», «P», «R», «S», «T», «U», «F», «H», «Ts», _ «Ch», «Sh», «Sch», «», «Y», «», «Eh», «U», «Ya», _ » «, «_», «?», _ «a», «b», «c», «d», «e», «f», «g», «h», «i», «j», «k», «l», «m», _ «n», «o», «p», «q», «r», «s», «t», «u», «v», «w», «x», «y», «z», «», «») For i = 1 To Len(Txt) с = Mid(Txt, i, 1) flag = 0 For J = 0 To 116 If Rus(J) = с Then outchr = Eng(J) flag = 1 Exit For End If Next J If flag Then outstr = outstr & outchr Else outstr = outstr & с Next i Translit = outstr End Function

Поиск файлов в папке

Dim strDirPath, strMaskSearch, strFileName as String strDirPath = «C:/test/» ‘Папка поиска strMaskSearch = «*.xls*» ‘Маска поиска ‘Получаем первый файл соответствующий шаблону strFileName = Dir(strDirPath & strMaskSearch) Do While strFileName <> «» ‘До тех пор пока файлы «не закончатся» MsgBox strFileName strFileName = Dir ‘Следующий файл Loop

Читать еще: Vba excel работа с массивами

Функция DATEADD

Эта функция возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number. Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т.д. Допустимые значения аргумента datepart приведены ниже и взяты из BOL.

Dayofyear — день года

Пусть сегодня 23/01/2004, и мы хотим узнать, какой день будет через неделю. Мы можем написать

SELECT DATEADD(day, 7, current_timestamp)

SELECT DATEADD(ww, 1, current_timestamp)

В результате получим одно и то же; что-то типа 2004-01-30 19:40:58.923.

Однако мы не можем в этом случае написать

SELECT DATEADD(mm, 1/4, current_timestamp)

потому, что дробная часть значения аргумента datepart отбрасывается, и мы получим 0 вместо одной четвертой и, как следствие, текущий день.

Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP функцию T-SQL GETDATE() с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта.

Пример (схема 4). Определить, какой будет день через неделю после последнего полета.

SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))

Использование подзапроса в качестве аргумента допустимо, т.к. этот подзапрос возвращает ЕДИНСТВЕННОЕ значение типа datetime.

Dateadd vba excel

Урок 15 по VBA — функции работы с датой и временем

Функции VBA даты и времени

VBA функции форматирование данных

Open Notes

Обо всём, что мне интересно

Полезные команды VBA

Функция DATEADD

Похожие главы из других книг:

Функция pthread_rwlock_init

Функция pthread_rwlock_rdlock

Функция pthread_rwlock_tryrdlock

Функция pthread_rwlock_wrlock

Функция pthread_rwlock_trywrlock

Функция SUM

Функция uni()

Функция uni()

Хэш-функция.

Функция uni()

Хэш-функция