Найти количество недель между датами excel vba
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. Задача, на первый взгляд, простая, но (как всегда) есть несколько весьма существенных нюансов.
Во-первых, в разных странах отсчет начала и конца самой недели – различаются. У нас в России днем отсчета недели принят понедельник, а в других странах (например, США и Израиле) – воскресенье.
Во-вторых, вопрос - какую неделю считать первой? На сегодняшний момент существуют как минимум два основных варианта с разной логикой. Рассмотрим их последовательно.
Способ 1. Стандарт ГОСТ ИСО 8601-2001 и функция НОМНЕДЕЛИ.ISO
Первой рабочей неделей года считается та, на которую выпадает первый четверг года (или 4 января, если хотите). Здесь логика проста. Первая неделя – это та, на которую пришлось больше трех дней (больше половины недели) из наступившего года. Некоторое неудобство в том, что в году получается когда 52, а когда 53 рабочих недели. Плюс ко всему 1 января может запросто оказаться 52 неделей предыдущего года.
Но именно этот вариант официально принят в России с 2002 года как государственный и остается им на данный момент (см. ГОСТ ИСО 8601-2001). Чтобы посчитать номер недели по дате по такой системе, можно использовать вот такую формулу:
В Excel 2013 функцию для расчета номера недели по ISO добавили в стандартный набор – она называется НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)
Способ 2. Неделя с 1-м января и функция НОМНЕДЕЛИ (WEEKNUM)
В этом случае первой неделей года считается та, на которую попадает 1 января. Здесь также возникает сложность в том, что в году может оказаться 52 или 53 недели и, плюс ко всему, 1 января может выпасть на воскресенье, т.е. шесть последних дней года могут оказаться уже в неделе с номером 1, что затрудняет отчетность. Тем не менее, если такой способ нумерации вам нужен, то его можно реализовать функцией НОМНЕДЕЛИ (WEEKNUM) , появившейся в Excel начиная с 2007 года. Синтаксис этой функции таков:
Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.
Возвращает значение, указывающее количество интервалов времени между двумя значениями Date .
Перегрузки
Вычитает Date1 значение, Date2 указывающее количество интервалов времени между двумя Date значениями.
Вычитает Date1 из Date2 значения, указывающего количество интервалов времени между двумя Date значениями.
DateDiff(DateInterval, DateTime, DateTime, FirstDayOfWeek, FirstWeekOfYear)
Вычитает Date1 значение, Date2 указывающее количество интервалов времени между двумя Date значениями.
Параметры
Обязательный. Значение перечисления DateInterval или строковое выражение, представляющее интервал времени, используемый в качестве единицы измерения разницы между Date1 и Date2 .
Обязательный. Первое значение даты/времени для использования в расчете.
Обязательный. Второе значение даты/времени для использования в расчете.
Необязательный элемент. Значение, выбранное из перечисления FirstDayOfWeek, указывающее первый день недели. Если этот параметр не задан, используется значение Sunday.
Необязательный элемент. Значение, выбранное из перечисления FirstWeekOfYear, указывающее первую неделю года. Если этот параметр не задан, используется значение Jan1.
Возвращаемое значение
Значение типа long, указывающее количество интервалов времени между двумя значениями Date .
Исключения
Значение Date1 , Date2 или DayofWeek выходит за пределы допустимого диапазона.
Значение Date1 или Date2 имеет недопустимый тип.
Примеры
В этом примере функция используется DateDiff для отображения количества дней между заданной датой и сегодня.
Комментарии
Функцию DateDiff можно использовать для определения количества указанных интервалов времени между двумя значениями даты и времени. Например, можно вычислить DateDiff количество дней между двумя датами или количество недель между сегодня и окончанием года.
Поведение
Обработка параметров. DateDiff Вычитает значение из значения Date1 Date2 для получения разницы. Ни значение не изменяется в вызывающей программе.
Возвращаемые значения. Так как Date1 они Date2 имеют Date тип данных, они содержат точные значения даты и времени до 100 наносекунд в системном таймере. DateDiff Однако всегда возвращает количество интервалов времени в виде Long значения.
Если Date1 представляет более позднюю дату и время, Date2``DateDiff возвращает отрицательное число.
Интервалы дня. Если Interval задано значение DateInterval.DayOfYear , оно обрабатывается так же, как DateInterval.Day и , так как DayOfYear не является значимым единицей для интервала времени.
Интервалы недели. Если Interval задано значение DateInterval.WeekOfYear , возвращаемое значение представляет количество недель между первым днем недели, содержащим Date1 и первым днем недели, содержащим Date2 . В следующем примере показано, как это приводит к DateInterval.Weekday различным результатам.
В предыдущем примере возвращается значение 0, DateDiff так wD как разница между двумя датами составляет менее семи дней, но возвращается 1, wY так как между первыми днями соответствующих календарных недель существует семидневная разница.
Большие интервалы. Если Interval задано значение DateInterval.Year , возвращаемое значение вычисляется исключительно из частей Date1 года и Date2 . Аналогичным образом возвращаемое значение DateInterval.Month вычисляется исключительно из частей года и месяца аргументов, а также для DateInterval.Quarter кварталов, содержащих две даты.
Например, при сравнении с 31 декабря по 1 января следующего года DateDiff возвращается 1 для DateInterval.Year , DateInterval.Quarter или DateInterval.Month , хотя в большинстве случаев прошло всего один день.
Для таких культур, как японский, имеющие несколько эпох, DateDiff метод не возвращает разницу в годах, если разница охватывает две или более эр. Вместо этого можно вычислить разницу в значениях, возвращаемых свойствомYear, как показано в следующем примере. date2.Year - date1.Year
Другие интервалы. Так как каждое Date значение поддерживается структурой DateTime , его методы предоставляют дополнительные параметры при поиске интервалов времени. Например, можно использовать Subtract метод в одной из перегруженных форм: DateTime.Subtract вычитает из TimeSpan переменной Date другое Date значение и DateTime.Subtract вычитает Date значение для возврата TimeSpan. Вы можете определить, сколько миллисекунд требуется, как показано в следующем примере.
Аргумент Interval может иметь один из следующих параметров.
Значение перечисления | Строковое значение | Единица времени |
---|---|---|
DateInterval.Day | "d" | День |
DateInterval.DayOfYear | "y" | День |
DateInterval.Hour | "h" | Час |
DateInterval.Minute | "n" | Минута |
DateInterval.Month | "m" | Месяц |
DateInterval.Quarter | "q" | Quarter |
DateInterval.Second | "s" | Second |
DateInterval.Weekday | "w" | Неделя |
DateInterval.WeekOfYear | "ww" | Неделя календаря |
DateInterval.Year | "yyyy" | Year; |
Аргумент DayOfWeek может иметь один из следующих параметров.
Значение перечисления | Значение | Описание |
---|---|---|
FirstDayOfWeek.System | 0 | Первый день недели, указанный в параметрах системы |
FirstDayOfWeek.Sunday | 1 | Воскресенье (по умолчанию) |
FirstDayOfWeek.Monday | 2 | Понедельник (соответствует стандарту ISO 8601, раздел 3.17) |
FirstDayOfWeek.Tuesday | 3 | Вторник |
FirstDayOfWeek.Wednesday | 4 | Среда |
FirstDayOfWeek.Thursday | 5 | Четверг |
FirstDayOfWeek.Friday | 6 | Пятница |
FirstDayOfWeek.Saturday | 7 | Суббота |
Аргумент WeekOfYear может иметь один из следующих параметров.
Значение перечисления | Значение | Описание |
---|---|---|
FirstWeekOfYear.System | 0 | Первая неделя года, указанная в параметрах системы |
FirstWeekOfYear.Jan1 | 1 | Неделя, в которой происходит 1 января (по умолчанию) |
FirstWeekOfYear.FirstFourDays | 2 | Неделя, которая имеет по крайней мере четыре дня в новом году (соответствует стандарту ISO 8601, раздел 3.17) |
FirstWeekOfYear.FirstFullWeek | 3 | Первая полная неделя в новом году |
См. также раздел
Применяется к
DateDiff(String, Object, Object, FirstDayOfWeek, FirstWeekOfYear)
Вычитает Date1 из Date2 значения, указывающего количество интервалов времени между двумя Date значениями.
Параметры
Обязательный. Значение перечисления DateInterval или строковое выражение, представляющее интервал времени, используемый в качестве единицы измерения разницы между Date1 и Date2 .
Обязательный. Первое значение даты/времени для использования в расчете.
Обязательный. Второе значение даты/времени для использования в расчете.
Необязательный элемент. Значение, выбранное из перечисления FirstDayOfWeek, указывающее первый день недели. Если этот параметр не задан, используется значение Sunday.
Необязательный элемент. Значение, выбранное из перечисления FirstWeekOfYear, указывающее первую неделю года. Если этот параметр не задан, используется значение Jan1.
Возвращаемое значение
Значение типа long, указывающее количество интервалов времени между двумя значениями Date .
Исключения
Значение Date1 , Date2 или DayofWeek выходит за пределы допустимого диапазона.
Значение Date1 или Date2 имеет недопустимый тип.
Примеры
В этом примере функция используется DateDiff для отображения количества дней между заданной датой и сегодня.
Комментарии
С помощью DateDiff функции можно определить, сколько указанных интервалов времени существует между двумя значениями даты и времени. Например, можно использовать для DateDiff вычисления количества дней между двумя датами или числа недель между сегодня и концом года.
Поведение
Обработка параметров. DateDiff Вычитает значение Date1 из значения Date2 , чтобы получить разницу. Ни значение не изменяется в вызывающей программе.
Возвращаемые значения. Так как Date1 они Date2 относятся к типу Date данных, они содержат точные значения даты и времени до 100 наносекундных тактов в системном таймере. DateDiff Однако всегда возвращает количество интервалов времени в виде Long значения.
Если Date1 представляет более позднюю дату и время, чем Date2 , DateDiff возвращает отрицательное число.
Интервалы дня. Если Interval задано значение DateInterval.DayOfYear , он обрабатывается так же, как и , так как DateInterval.Day``DayOfYear не является значимым единицей для интервала времени.
Интервалы недели. Если Interval задано значение DateInterval.WeekOfYear , возвращаемое значение представляет количество недель между первым днем недели, содержащимся Date1 в ней, и первым днем недели. Date2 В следующем примере показано, как это приводит к разным результатам. DateInterval.Weekday
В предыдущем примере DateDiff возвращается значение 0, так wD как разница между двумя датами меньше семи дней, но возвращает 1, так wY как между первыми днями соответствующих календарных недель существует семидневная разница.
Большие интервалы. Если Interval задано значение DateInterval.Year , возвращаемое значение вычисляется исключительно из частей Date1 года и Date2 . Аналогичным образом возвращаемое значение DateInterval.Month вычисляется исключительно из частей аргументов года и месяца, а DateInterval.Quarter также из кварталов, содержащих две даты.
Например, при сравнении от 31 декабря до 1 января следующего года DateDiff возвращается значение 1 для DateInterval.Year , DateInterval.Quarter или DateInterval.Month , даже если не более одного дня прошло.
Для таких культур, как японский, имеющие несколько эпох, метод не возвращает разницу в годах, DateDiff если разница охватывает две или более эпох. Вместо этого можно вычислить разницу в значениях, возвращаемых свойствомYear, как показано в следующем примере: date2.Year - date1.Year
Другие интервалы. Так как каждое Date значение поддерживается структурой DateTime , его методы предоставляют дополнительные параметры при поиске интервалов времени. Например, можно использовать Subtract метод в любой из перегруженных форм: DateTime.Subtract вычитает TimeSpan переменную из переменной Date , чтобы вернуть другое Date значение, и DateTime.Subtract вычитает Date значение для возврата TimeSpan. Вы можете определить, сколько миллисекунд занимает процесс, как показано в следующем примере.
Возвращает значение типа Variant (Long), указывающее на количество интервалов времени между двумя указанными датами.
Синтаксис
DateDiff(интервал, дата1, дата2, [ первый_день_недели, [ первая_неделя_года ]] )
Синтаксис функции DateDiff использует следующие именованные аргументы:
Часть | Описание |
---|---|
интервал | Обязательный аргумент. Строковое выражение, которое обозначает интервал времени, используемый для вычисления разницы значений дата1 и дата2. |
дата1, дата2 | Обязательный элемент; Variant (Date). Две даты, которые требуется использовать в расчете. |
первый_день_недели | Необязательный аргумент. Константа, задающая первый день недели. Если она не указана, им является воскресенье. |
первая_неделя_года | Необязательно. Константа, задающая первую неделю года. Если она не указана, первой неделею является неделя, начинающаяся 1 января. |
Параметры
Аргумент интервал имеет следующие параметры:
Setting | Описание |
---|---|
yyyy | Год |
q | Квартал |
m | Месяц |
y | День года |
d | День |
w | День недели |
ww | Неделя |
h | Часы |
n | Минуты |
s | Секунды |
Аргумент первый_день_недели может принимать следующие значения:
Константа | Значение | Описание |
---|---|---|
vbUseSystem | 0 | Используются параметры API NLS. |
vbSunday | 1 | Воскресенье (по умолчанию) |
vbMonday | 2 | Понедельник |
vbTuesday | 3 | Вторник |
vbWednesday | 4 | Среда |
vbThursday | 5 | Четверг |
vbFriday | 6 | Пятница |
vbSaturday | 7 | Суббота |
Константа | Значение | Описание |
---|---|---|
vbUseSystem | 0 | Используются параметры API NLS. |
vbFirstJan1 | 1 | Начать с недели, содержащей 1 января (по умолчанию). |
vbFirstFourDays | 2 | Первой неделей считается та, которая содержит хотя бы четыре дня нового года. |
vbFirstFullWeek | 3 | Первой неделей считается первая полная неделя года. |
Примечания
Используйте функцию DateDiff для определения количества указанных интервалов времени между двумя датами. Например, DateDiff можно использовать для вычисления числа дней между двумя датами или числа недель между сегодняшним днем и концом года.
Для вычисления числа дней между аргументами дата1 и дата2 используется значение "День года" ("y") или "День" ("d"). Если интервал задан как "День недели" ("w"), DateDiff возвращает число недель между двумя датами. Если день дата1 приходится на понедельник, DateDiff считает число понедельников до дня дата2. День, соответствующий значению аргумента дата2, учитывается, а дата1 — нет.
Однако если интервал задан как "Неделя" ("ww"), функция DateDiff возвращает число календарных недель между двумя датами. Для этого рассчитывается число воскресений между днями дата1 и дата2. DateDiff учитывает день дата2, если он приходится на воскресенье; день дата1 не учитывается, даже если он приходится на воскресенье.
Если значение дата1 соответствует более поздней дате, чем значение дата2, функция DateDiff возвращает отрицательное число. Аргумент первый_день_недели влияет на вычисления, если заданы значения "w" и "ww".
Если аргумент дата1 или дата2 содержит литерал даты, указанный год становится постоянной частью этой даты. Однако если аргумент дата1 или дата2 заключен в двойные кавычки (" "), а год опущен, при каждом вычислении выражения дата1 или дата2 в код вставляется текущий год. Это позволяет писать код, который можно использовать для разных лет.
При сравнении 31 декабря с 1 января следующего года функция DateDiff для года ("yyyy") возвращает значение 1, не смотря на то, что разница составляет всего один день.
Если в свойстве Calendar задан григорианский календарь, аргументы дата1 и дата2 следует указывать соответствующим образом. Если используется календарь Хиджра, дата должна соответствовать ему.
Пример
В данном примере функция DateDiff отображает число дней между указанной датой и сегодняшним днем.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
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».
Функция DATEDIFF в VBA — это встроенная функция в VBA, которая также классифицируется как функция даты и времени в VBA, эта функция используется для получения разницы между двумя датами, эта функция принимает три аргумента, первый аргумент — это часть разницы, которую мы хотим которые могут быть годами, месяцами или секундами и двумя датами, а результатом является целое число.
Функция DATEDIFF в VBA
Функция DATEDIFF в VBA вычисляет разницу между двумя датами в днях, месяцах, кварталах и годах.
В Excel найти разницу между двумя датами можно разными способами. Вам не нужна специальная формула для вычисления разницы между двумя датами.
Например, посмотрите на изображение ниже.
Если мы хотим рассчитать разницу между этими двумя датами, мы можем просто вычесть дату 1 из даты 2.
Это дало нам разницу между двумя датами в несколько дней. Это проблема с этой общей формулой. Если нам нужна разница в месяцах, годах, кварталах и т. д.… она не может дать.
В этой статье мы покажем вам, как использовать эту функцию DateDiff в VBA.
Что такое функция DATEDIFF в Excel VBA?
DATEDIFF в VBA означает «Разница дат между двумя датами».
Эта функция может дать нам номер временного интервала между двумя датами. Когда мы хотим найти разницу между двумя датами, мы можем найти ее в днях, неделях, месяцах, кварталах и т. д.
Чтобы понять функцию, посмотрите на приведенный ниже синтаксис функции.
Интервал: Это не что иное, как то, как вы хотите рассчитать разницу в дате. Будь то в днях, месяцах, неделях, кварталах и т. д.… Ниже приведен их список.
Дата 1: На каком первом свидании вы хотите найти разницу.
Дата 2: На какой второй дате вы хотите найти отличие от даты 1.Дата 1: На каком первом свидании вы хотите найти разницу.
Вот формула Дата 2 — Дата 1.
[First Day of Week]: Какой первый день недели? Мы можем согласиться со следующими аргументами.
[First Week Of the Year]: Какая первая неделя года. Мы можем ввести следующие аргументы.
Примеры функции DATEDIFF в Excel VBA
Ниже приведены примеры Excel VBA DateDiff.
Пример № 1. Чтобы найти разницу в днях
Предположим, у вас есть две даты: «15-01-2018» и «15-01-2019». Давайте найдем всевозможные различия между этими двумя датами.
Шаг 1: Сначала создайте имя макроса.
Код:
Шаг 2: Определите две переменные как дату.
Код:
Шаг 3: Теперь для переменной Date1 назначьте «15-01-2018», а для переменной Date2 назначьте «15-01-2019».
Код:
Шаг 4: Теперь, чтобы сохранить результаты, определите еще одну переменную как Long.
Код:
Шаг 5: Теперь присвойте значение этой переменной с помощью функции DATEDIFF в VBA.
Код:
Шаг 6: Первый аргумент в том, какая разница нам нужна между этими двумя датами. Допустим, нам нужно найти количество дней, поэтому укажите аргумент как «D».
Код:
Шаг 7: Что такое первая дата, чтобы найти разницу. Наша первая дата — «15-01-2018», которую мы уже присвоили переменной «Date1». Поэтому укажите здесь имя переменной.
Код:
Шаг 8: Что такое вторая дата, чтобы найти разницу. Вторая дата — «15-01-2019», которая содержит значение через переменную «Date2».
Код:
Код:
Теперь запустим код с помощью клавиши F5 или вручную, мы получим разницу между этими двумя датами в количестве дней.
Итак, с «15-01-2018» по 15-01-2019 точная разница составляет один год, поэтому в итоге мы получили 365 дней.
Таким образом, мы можем найти разницу между двумя датами во временных интервалах.
Пример № 2 — найти разницу в месяцах
Код:
Запустите этот код, используя клавишу F5, или вы можете запустить вручную, чтобы показать результат, как показано ниже.
Пример № 3 — найти разницу в годах
Код:
Запустите этот код с помощью клавиши F5 или вручную, чтобы увидеть результат.
Назначение как практика
Надеюсь, вы поняли функцию VBA DATEDIFF. Взгляните на приведенное ниже домашнее задание для вас. Найдите разницу между указанными ниже датами в «Месяцах».
Если вы не нашли способ, ниже приведен готовый код для вас.
Код:
Вы можете запустить этот код вручную или нажать клавишу F5, чтобы увидеть результат.
Читайте также: