Elettracompany.com

Компьютерный справочник
8 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel последний день месяца

VBA получить последний день месяца

Я довольно новичок в VBA. У меня есть диапазон дат, и мне нужно написать код, который изменит дату в ячейке на последний день месяца, используемый в ячейке. Например, если дата в ячейке 28/03/2018 , я хочу, чтобы она была заменена на 31/03/2018 . Есть идеи, как я могу это сделать?

7 Ответов

Вы также можете получить нужный вам результат с помощью одной строки кода благодаря формуле Eomonth:

Вы можете использовать пользовательскую функцию GetLastDayOfMonth() и передать интересующий вас диапазон:

Функция проверяет месяц и год переданной даты и возвращает новую дату, которая является последним днем месяца для данного конкретного месяца и года.

Затем в версии TestMe можно сравнить заданную дату с последним днем месяца, сгенерированным функцией. Если даты совпадают, то эта дата является последним днем соответствующего месяца. Используя DateAdd() можно получить на следующий день lastDay.

В приведенном выше примере я явно написал диапазон («A1») 9 раз, поэтому, вероятно, легче следовать.

Во-первых, если вы просто хотите последний день месяца в Excel, используйте функцию =EOMONTH или см. Этот пост SO для кода vba: VBA запуск макроса в последний день месяца

Прокатка собственного кода даты и времени-плохая идея. Не делай этого.

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

VBA путь:

  1. Строительство новых дата:
    • год : от старой даты
    • месяц : от старой даты
    • день : 1
  2. Добавьте 1 месяц к новой дате.
  3. Вычтите 1 день из новой даты.

Шаги 2 и 3 избавят вас от всех сложностей вычислений дат.

Для Excel способ, пожалуйста, обратитесь к другому ответу .

Эта функция VBA вычислит последний день месяца:

Работает путем перехода к началу месяца, добавления месяца, а затем вычитания дня (так 1 февраля — >1 марта — >28 февраля)

Похожие вопросы:

Как я могу найти последний день месяца в C#? Например, если у меня есть дата 03/08/1980, как я могу получить последний день месяца 8 (в данном случае 31)?

Как я могу получить последний день месяца, когда timestamp будет 11:59:59 PM?

Мне нужно получить первый и последний день месяца, основываясь на данном значении yearmonth. Я могу получить первый день, как мы получаем последний день месяца здесь ( в python) : from datetime.

Я пытаюсь получить последний день текущего месяца и последний день следующего месяца. Вот код, который я придумал до сих пор, но я вынужден смешивать код NodaTime и the .NET метод.

Я пытаюсь поместить в файл .txt первый день и последний день месяца, используя PowerShell. В приведенном ниже примере я пытался получить первый и последний день июля, но я просто получаю первый.

Как бы я получил последний день текущего месяца, учитывая номер месяца. Например, если месяц равен 8 (август 2014), как я могу получить последний день месяца? (Последний день августа-31-е).

Мне нужно получить последний день месяца, указанный в качестве даты в SQL. Если у меня есть первый день месяца, я могу сделать что-то вроде этого: DATEADD(DAY, DATEADD(MONTH,’2009-05-01′,1), -1) Но.

Можно ли получить последний день последней недели за текущий месяц. Например, я хочу получить 31-е число как последний день недели 5 Для месяца август и 3-е число как последний день недели 1 Для.

я должен получить последний день текущего месяца. как я могу получить

Первый и последний день месяца в Excel

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

Первый день месяца в Excel

Для формулы, преобразующей текущую или другую дату в первый день месяца, используем функции «ДАТА», «ГОД» и «МЕСЯЦ»:

  • Первый день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)
  • Первый день прошлого месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())-1;1)
  • Первый день текущего месяца предыдущего года от текущей даты: =ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ());1)
  • Первый день месяца от произвольной даты, записанной в ячейку «A1»: =ДАТА(ГОД(A1);МЕСЯЦ(A1);1)

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

Последний день месяца в Excel

Для определения последнего месяца от текущей или произвольной даты в Excel, начиная с версии 2007 года, существует функция «КОНМЕСЯЦА» с двумя аргументами: «Начальная дата» и «Количество месяцев». Если «Количество месяцев» равно 0, то будет возвращен последний день того месяца, который содержится в аргументе «Начальная дата».

Примеры для Excel 2007-2016:

  • Последний день текущего месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();0)
  • Последний день прошлого месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-1)
  • Последний день текущего месяца предыдущего года от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-12)
  • Последний день месяца от произвольной даты, записанной в ячейку «A1»: =КОНМЕСЯЦА(A1;0)

Функция «КОНМЕСЯЦА» возвращает значение в числовом виде, поэтому для правильного отображения последнего дня месяца следует к ячейке с формулой применить формат даты.

В версиях Excel по 2003 год функция «КОНМЕСЯЦА» по-умолчанию отсутствует, поэтому будем применять формулу, составленную, как и для первого дня месяца, из функций «ДАТА», «ГОД» и «МЕСЯЦ».

Примеры для Excel 2000-2003:

  • Последний день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
  • Последний день прошлого месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());0)
  • Последний день текущего месяца предыдущего года от текущей даты: =ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ())+1;0)
  • Последний день месяца от произвольной даты, записанной в ячейку «A1»: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;0)

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

Как вариант, для определения последнего дня месяца можно использовать формулу, возвращающую первый день следующего месяца, из значения которой вычитается единица: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;1)-1

Определение первого и последнего дня месяца имеет большую актуальность для автоматического заполнения интервала дат в диалоговых формах с помощью VBA Excel.

Excel последний день месяца

Последний день месяца в MS EXCEL

​: ?​​ вычисляет дату конца​​ типу функции в​Кроме того, можно составить​ реализации данной функции.​ МЕСЯЦ в Excel:​ а только лишь​

Читать еще:  Как отобразить листы в excel 2020

​ примерах ниже, вы​

​ Доступна и обработка​Подскажите, как из​ и первый аргумент​

Первый день месяца в MS EXCEL

​Определим дату, на которую​=КОНМЕСЯЦА(A2;-1)+1​Последний день месяца определяется​: михаил, насколько я​

​: да, даты по​​ikki​​ месяца не зависимо​ Excel.​ гистограмму для анализа​​Пример работы c функциями​

​ текстовыми и числовыми​

​ сможете наглядно увидеть​

​ДЕНЬНЕД.​​ дат. При этом​​ даты извлечь первый​​ функции ДЕНЬНЕД() уменьшили​​ приходится первый вторник​=ДАТА(ГОД(A2);МЕСЯЦ(A2);1)​
​ с помощью функции​

​ понял, автору нужно​ порядку. И надо​: yavorsky90, неужели за​ от А! и​​yavorsky90​​ самых продаваемых дней​​Эти примитивные функции очень​​ ДЕНЬ и ДЕНЬНЕД.​​ значениями. Чтобы отделить​​ это. Важно помнить,​Все они отображают числовые​ может возникнуть потребность​

​ и последний день​ на 3, т.к.​

​ месяца, вторую пятницу​

​Если​ КОНМЕСЯЦА().​ немного другое -​

Первый вторник месяца в MS EXCEL

​ учитывать года. Вариант​ 150 постов на​ числа месяцев. какие​: Здравствуйте, форумчане. У​ недели, понять на​

​ полезны при группировки​​ Функция ДЕНЬ получает​​ год, месяц или​ что при отображении​

​ значения, соответствующие григорианскому​ вычленения из общего​ месяца.​​ пятница через 3​​ или третью среду​

​Если ячейка​ проверка на пжоследнюю​ Nic70y как-то работает,​ форуме ни разу​ единички?​

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

​ день от полной​​ даты в качестве​​ календарю. Даже если​ значения конкретного числа,​Например, есть дата:​ дня через вторника).​ месяца.​содержит дату, а​

​А1​ дату месяца из​ пробую понять как.​ не встречались с​yavorsky90​

​ есть в столбике​ приходится наибольшее количество​ месяцам, дням недели​ число любого дня:​ даты для этого​

​ текста (автоматическая ориентация​ в таблице Excel​ например, года. Для​ 15.06.2017 г.​В файле примера приведена​Пусть в ячейке​B2​содержит дату, то​ имеющихся в списке,​ikki​

Извлечь первый и последний день месяца из даты (Формулы/Formulas)

​ выражением «формула массива»?​​: Мне надо, чтобы​
​ А набор дат​ продаж:​ и конкретным дням.​Функция ДЕНЬНЕД возвращает номер​
​ в Excel предусмотрены​ по левому краю​
​ для отображения введенной​ этого существует отдельные​Нужно из нее​ универсальная формула для​А2​число месяцев, то​
​ последний день месяца,​
​ а не в​

​: НЕ формула массива,​​я специально заключил​ ​ в столбце В​ ​ за год, из​В таком виде очень​Допустим у нас имеется​
​ дня недели (1-понедельник,​

​ функции для работы​​ ячейки), функция ГОД​

Примеры функций для работы с датами: ГОД, МЕСЯЦ и ДЕНЬ в Excel

​ даты был выбран​ функции: ГОД, МЕСЯЦ,​ извлечь в одну​ нахождения нужного дня​может находится дата​ формула​ которому принадлежит эта​ календаре :)​ ввести в B2,​ формулу в фигурные​ стояли 1 если​ них надо найти​ удобно сегментировать отчеты​

Примеры использования функций для обработки даты в Excel

​ простой отчет по​ 2-второник… и т.д.)​ с датами.​ не будет выполнена.​ календарь Хиджра, то​ ДЕНЬ и ДЕНЬНЕД.​ ячейку B2 дату:​ по порядковому номеру​ (см. файл примера).​=ДАТА(ГОД(A2);МЕСЯЦ(A2)+B2;1)​ дата, определяется так​yavorsky90​ протянуть вниз​ скобки.​ в соответствующей строке​

​ последнюю дату в​ по продажам за​

  • ​ продажам:​
  • ​ для любой даты:​
  • ​Пример: Есть таблица с​

​ Ее результатом станет​ при вычленении года​Таблицы Excel хранят даты,​ 01.06.2017 г., в​ дня недели.​Для определения первого вторника​вернет первое число месяца,​ (см. файл примера):​: Михаил, так не​=—ИЛИ(МЕСЯЦ(A2)<>МЕСЯЦ(A3);ЕПУСТО(A3))​вам их вводить​ А последняя существующая​ каждом месяце.​ длительные, средние и​

​Нам нужно быстро организовать​Во втором опциональном аргументе​ перечнем дат и​ отображение #ЗНАЧ. Поэтому​ и других составных​

​ которые представлены в​ ячейку B3 дату:​

​В качестве аргументов формулы​ месяца, которому принадлежит​ отстоящего от исходного​=КОНМЕСЯЦА(A1;0)​ получается, потому что​yavorsky90​ не надо. они​ дата месяца, и​Делал вот так:​ короткие периоды времени.​ данные для визуального​ функции ДЕНЬНЕД следует​ в каждой из​ форматируемые даты должны​ значений посредством функций,​ качестве последовательности числовых​ 30.06.2017 г.​ задаются: порядковый номер​ дата в ячейке​ на число месяцев​Или другой формулой: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;0)​ напротив 28.12.2007 тоже​

​: Спасибо огромное, все​ появятся сами -​ 0 — в​ =If(A1=Eomonth(A1;0);1;0). Но как​Стоит сразу отметить что​ анализа без использования​ указать число 2​ них необходимо отделить​ быть представлены в​ приложение представит число,​ значений. Начинается она​Файл во вложении.​ дня недели, день​А2​ из ячейки​Первый день месяца определяется​ надо поставить 1​ работает.​

​ после того, как​ других случаях.​ прописать, чтобы формула​ для того чтобы​ сводных таблиц. Для​

​ для нашего формата​ значение только года.​

​ числовом варианте. Дни,​ которое является эквивалентом​ с 1 января​Заранее спасибо.​

​ недели, месяц и​, можно использовать формулу:​B2​

​ в статье Первый​ (это последний существующий​ikki​

​ вы завершите ввод​yavorsky90​

​ ставила единицы если​ получить разницу между​ этого приведем отчет​ отсчета дня недели​Введем исходные данные в​

​ месяцы и год​ по григорианской системе​ 1900 года. Этой​_Boroda_​

​ год.​=$A2-ДЕНЬ($A2)+7-ДЕНЬНЕД($A2-ДЕНЬ($A2)-1;3)​(т.е. если​ день месяца.​ в столбце день​: должен заметить -​ формулы ctrl+shift+enter​

​: ikki ,что-то не​ в А1 не​ двумя датами нам​ в таблицу где​ (с понедельника-1 по​ Excel.​

​ могут быть разделены​ летоисчисления.​ дате будет соответствовать​

​: нач​Решение обратной задачи: если​

​Для определения любого другого​

Примеры практического применения функций для работы с датами

​А2​Первый день месяца можно​ месяца).​ последняя формула наврёт​пс. год формула​

​ работает​ 31.12.2012, а 30.12.2012,​ не поможет ни​

​ можно удобно и​ восркесенье-7):​Для решения поставленной задачи,​ точкой, слешем или​Чтобы воспользоваться функцией ГОД,​ число 1. При​=B1-ДЕНЬ(B1)+1​ дана дата, а​ дня (третья среда,​содержит 05/08/1999, а​

​ определить с помощью​Михаил С.​ в случае, если​ не учитывает, но​Файл удален​ а 31.12.2012 нет​ одна из выше​

​ быстро группировать данные​Если пропустить второй необязательный​ необходимо в ячейки​ запятой.​ нужно ввести в​ этом 1 января​илиКод=КОНМЕСЯЦА(B1;-1)+1илиКод=—ТЕКСТ(B1;»1.М.г»)А последняя и​

​ нужно определить, какой​ четвертый четверг) вышеуказанная​B2​ функции ДАТА() или​: Тогда вопрос должен​

​ к примеру, следом​ вы этого и​- велик размер​ совсем? То есть,​ описанных функций. Для​ по годам месяцам​ для заполнения аргумент,​ столбца B ввести​Рассмотрим пример работы с​ ячейку следующую формулу​

Читать еще:  Прогресс бар vba excel

​ 2009 года заложено​ так, без единички,​ по счету это​ формула должна быть​– 3, то​ КОНМЕСЯЦА().​ звучать примерно так:​

как найти последний существующий день месяца.

​ за ячейкой 20.05.2007​​ не просили :)))​ — [​ чтобы формула ставила​ данной задачи следует​ и дням недели:​ тогда будет использоваться​ формулу:​ функцией ГОД в​

​ функции с одним​ в таблицах, как​ получитсяКод=—ТЕКСТ(B1;»М.г»)​ день недели в​ модифицирована. Например,​ результат будет =​Если ячейка​ «Как найти максимальную​ будет ячейка 12.05.2008.​ikki​МОДЕРАТОРЫ​

​ 1 напротив последнего​​ воспользоваться специально предназначенной​

​Теперь у нас есть​​ формат по умолчанию​=ГОД (адрес ячейки, из​ Excel. Если нам​ аргументом:​ число 39813. Именно​конКод=КОНМЕСЯЦА(B1;0)​ текущем месяце (например,​

​Второй вторник: =$A2-ДЕНЬ($A2)+7-ДЕНЬНЕД($A2-ДЕНЬ($A2)-1;3)​​ 01/11/1999).​А2​ дату каждого месяца​если такая вероятность​: пс. если, как​]​ существующего дня месяца.​ функцией РАЗНДАТ:​

​ инструмент для работы​​ (английский с воскресенья-1​ даты которой нужно​
​ нужно получить год​​=ГОД(адрес ячейки с датой​ такое количество дней​​vitalife​​ 01/03/2011 – первый​

​+7​​Тот же результат вернет​содержит дату, то​ из списка дат?»​

​ есть — добавьте​​ в вашем наконец-то​

​yavorsky90​​ikki​Тип значений в ячейках​ с этим отчетом​ по суботу-7).​ вычленить значение года)​
​ из исходной даты​ в числовом формате)​ между двумя обозначенными​
​: Благодарю!​ вторник), приведено в​(просто добавили 7​ формула​ нижеследующие формулы вернут​=—(ИНДЕКС(A2:A33;ПОИСКПОЗ(КОНМЕСЯЦА(A2;0);A2:A33))=A2)​

​ «дата» требует особого​​ по продажам. Мы​Создадим формулу из комбинаций​В результате мы извлекаем​ нам не поможет​Аргумент функции является обязательным​ датами.​

​В таблицах Excel предусмотрена​​ статье Первый вторник​ дней);​=КОНМЕСЯЦА(A2;B2-1)+1​ первый день месяца​MCH​

​ на год.​​ расположены по порядку​ файл получился. Прикрепляю​Михаил С.​
​ подхода при обработке​

​ можем фильтровать и​​ функций ИНДЕКС и​ года из каждой​

​ функция ПРАВСИМВ так​​ для заполнения. Он​Функция ГОД используется аналогично​ возможность работы с​ месяца (обратная задача).​Третья пятница: =$A2-ДЕНЬ($A2)+7-ДЕНЬНЕД($A2-ДЕНЬ($A2)​Последний день месяца определяется​
​ этой даты (см.​: Ну и от​Михаил С.​ — формулу можно​

​ меньший​​: непонятно, что вам​

​ данных. Поэтому следует​​ сегментировать данные по​ ДЕНЬНЕД:​ даты.​ как она не​ может быть заменен​ смежным:​ различными видами текстовой​vitalife​

​-4+14​​ в статье Последний​ файл примера):​ меня несколько вариантов​: =—(КОНМЕСЯЦА(A2;0)=A2)​ сделать гораздо проще.​112347​ нужно. Eomonth() корректно​

​ использовать соответствующие данному​​ определенным критериям времени:​Получим более понятный вид​Аналогичный пример работы функции​ работает с датами,​ на «дата_в_числовом_формате». В​
​МЕСЯЦ;​

​ и числовой информации.​​: Добрый день.​(добавили 14 дней​

Урок 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.

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

Microsoft Excel

трюки • приёмы • решения

Как функция ДЕНЬНЕД определяет конкретные даты в Excel

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

Определение дня в году

1 января — это первый день года, а 31 декабря — последний. А как насчет остальных дней, идущих между ними? Следующая формула возвращает день года для даты, хранящемся в ячейке A1: =A1-ДАТА(ГОД(A1);1;0) . Например, если ячейка A1 содержит дату 16 февраля 2010 года, формула возвращает 47, потому что эта дата является 47-м днем в году.

Следующая формула возвращает количество дней, оставшихся в году с момента определенной даты (предполагается, что она содержится в ячейке A1): =ДАТА(ГОД(A1);12;31) .

Определение дня недели

Если вам необходимо определить день недели для даты, функция ДЕНЬНЕД справится с этой задачей. Функция принимает в качестве аргумента дату и возвращает целое число от 1 до 7, соответствующее дню недели. Следующая формула, например, возвращает 6, потому что первый день 2010 года приходится на пятницу: =ДЕНЬНЕД(ДАТА(2010;1;1)) .

Функция ДЕНЬНЕД использует еще и необязательный второй аргумент, обозначающий систему нумерации дней для результата. Если вы укажете 2 в качестве второго аргумента, то функция вернет 1 для понедельника, 2 — для вторника и т. д. Если же вы укажете 3 в качестве второго аргумента, то функция вернет 0 для понедельника, 1 — для вторника и т. д.

Вы можете также определить день недели для ячейки, содержащей дату, применяя пользовательский числовой формат. Ячейка, использующая следующий формат, выводит день недели: ДДДД. Имейте в виду, что ячейка действительно содержит полную дату, а не только номер дня.

Определение даты последнего воскресенья

Формула в этом разделе возвращает последний указанный день. Вы можете использовать следующую формулу для получения даты прошлого воскресенья. Если текущий день — воскресенье, то формула возвращает текущую дату. Результатом будет серийный номер даты (вам нужно отформатировать ячейку для отображения читабельной даты): =СЕГОДНЯ()-ОСТАТ(СЕГОДНЯ()-1;7) .

Чтобы изменить эту формулу для нахождения даты другого дня, а не воскресенья, поменяйте 1 на другое число в диапазоне от 2 (понедельник) до 7 (суббота).

Определение дня недели после даты

Следующая формула возвращает указанный день недели, который наступает после определенной даты. Например, вы можете применять эту формулу для определения даты первой пятницы после 4 июля 2010 года. Формула предполагает, что ячейка А1 содержит дату, а ячейка А2 — число от 1 до 7 (1 соответствует воскресенью, 2 — понедельнику и т. д.): =A1+A2-ДЕНЬНЕД(A1)+(А2 .

Если ячейка А1 содержит 4 июля. 2010, а ячейка А2 содержит б (что обозначает пятницу), то формула возвращает 9 июля, 2010. Это первая пятница после 4 июля 2010 года (дня, который приходится на воскресенье).

Нахождение n-го определенного дня недели в месяце

Вам может понадобиться формула для нахождения даты определенного по счету дня недели. Предположим, что день выплаты зарплаты в вашей компании приходится на вторую пятницу каждого месяца и вам нужно определить эти
дни выплат для каждого месяца года. Следующая формула выполнит требуемый расчет:
=ДАТА(А1;А2;1)+А3-ДЕНЬНЕД(ДАТА(А1;А2;1))+(А4-(А3>=ДЕНЬНЕД(ДАТА(А1;А2;1))))*7

Эта формула предполагает, что:

  • ячейка А1 содержит год;
  • ячейка А2 содержит месяц;
  • ячейка A3 содержит номер дня (1 — воскресенье, 2 — понедельник и т. д.);
  • ячейка А4 содержит число — например 2, указывающее второе появление дня недели, заданного в ячейке A3.

При использовании этой формулы для определения даты второй пятницы в июне 2010 года результатом будет 11 июня, 2010.

Определение последнего дня месяца

Чтобы определить дату, которой соответствует последний день месяца, вы можете использовать функцию ДАТА. Однако вам нужно увеличивать месяц на 1 и указывать в качестве значения дня 0. Другими словами, «0-й» день следующего месяца — это последний день текущего месяца.

Следующая формула предполагает, что дата хранится в ячейке А1. Формула возвращает дату, которой соответствует последний день месяца: =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0) .

Вы можете модифицировать эту формулу, чтобы определить, сколько дней включает в себя указанный месяц. Следующая формула возвращает целое число, которое соответствует количеству дней в месяце для даты из ячейки А1 (убедитесь, что вы отформатировали ячейку как число, а не как дату): =ДЕНЬ(ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;0))

Определение квартала даты

Для финансовых отчетов может оказаться полезным представление информации по кварталам. Следующая формула возвращает целое число от 1 до 4, которое соответствует календарному кварталу для даты в ячейке А1: =ОКРУГЛ ВВЕРХ(МЕСЯЦ(A1)/3;0) . Эта формула делит номер месяца на 3, а затем округляет результат.

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