Excel дата конца месяца

В этой статье описаны синтаксис формулы и использование функции КОНМЕСЯЦА в Microsoft Excel.

Описание

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

Синтаксис

КОНМЕСЯЦА(нач_дата;число_месяцев)

Аргументы функции КОНМЕСЯЦА описаны ниже.

• нач_дата    — обязательный аргумент. Начальная дата. Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 года следует воспользоваться выражением ДАТА(2008;5;23). Если ввести даты как текст, это может привести к возникновению проблем.

• Число_месяцев    — обязательный аргумент. Количество месяцев до или после даты «нач_дата». Положительное значение аргумента «число_месяцев» означает будущие даты; отрицательное значение — прошедшие даты.

  Примечание: Если значение аргумента «число_месяцев» не является целым числом, оно усекается.

Замечания

• В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.

• Если start_date не является допустимой датой, то EOMONTH возвращает #NUM! значение ошибки #ЗНАЧ!.

• Если start_date «плюс месяцы» возвращает недействительные даты, то EOMONTH возвращает #NUM! значение ошибки #ЗНАЧ!.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Дата
1-янв-11
Формула	Описание	Результат
=КОНМЕСЯЦА(A2;1)	Дата последнего дня месяца, следующего за датой в ячейке А2.	28.02.2011
=КОНМЕСЯЦА(A2;-3)	Дата последнего дня месяца, на три месяца раньше даты в ячейке А2.	31.10.2010

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

Как получить последний день месяца по дате в Excel

Поскольку каждый из месяцев года имеет определенное число дней (30 или 31), данная функция полезна для быстрого нахождения номера числа любого месяца. Единственным исключением является февраль – 28 дней. Однако, раз в 4 года этот месяц имеет 29 дней (если год високосный).

Пример 1. Вывести количество дней в каждом месяце 2019 года в отдельный столбец таблицы.

Вид таблицы данных:

Для расчета числа дней используем следующую формулу массива, предварительно выделив ячейки B2:B13:

В качестве даты как точки отсчета была указана 01.01.2019 (первый день текущего года). Для расчета числа дней первого и последующих месяцев была вычтена 1 (если второй аргумент функции КОНМЕСЯЦА принимает значение 0, определяется число дней текущего месяца). Функция ТЕКСТ используется для преобразования полученного значения и вывода только числа дней (формат «ДД»).

Результаты вычислений:

Важный момент! Чтобы выполнять арифметические операции со значениями, которые возвращает данная формула следует использовать функцию ЗНАЧЕН. Она позволит преобразовать текст в числовое значение:

На этот раз мы использовали формулу массива, которую следует вводить через комбинацию клавиш CTRL+SHIFT+Enter для подверженная. Таким образом перед суммированием функцией СУММ значение из каждой ячейки преобразуется в число с помощью функции ЗНАЧЕН.



Как определить последнее воскресенье мая месяца в Excel

Пример 2. Использовать формулы, включающие функцию КОНМЕСЯЦА, для определения следующих значений:

1. Каким днем недели является последний день мая месяца текущего года?
2. Какой номер дня соответствует последнему воскресению мая месяца в текущем году?

Для будущей проверки результатов вычисления формул сразу уточним, что на момент написания данного урока на дворе шел 2019-й год.

Для нахождения последнего дня мая месяца для текущего года используем следующую формулу:

Функция ТЕКСТ используется для преобразования данных (вывода текстового представления дня недели). Функция ДЕНЬНЕД возвращает числовое значение дня недели (от 1 до 7). Функция КОНМЕСЯЦА вычисляет дату последнего дня текущего месяца (аргументы СЕГОДНЯ и 0 соответственно).

Результат вычислений:

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

Полученный результат:

Чтобы убедиться, что был получен корректный результат, используем формулу для проверки последнего понедельника мая месяца в текущем году. В ячейке B9 водим формулу:

Результат вычислений:

Правила использования функции КОНМЕСЯЦА в Excel

Функция имеет следующий синтаксис:

=КОНМЕСЯЦА(нач_дата;число_месяцев)

Описание аргументов:

• нач_дата – обязательный, принимает значения формата Дата, используемые как точка отсчета, относительно которой будет найден месяц, для которого требуется определить номер последнего дня;
• число_месяцев – обязательный, принимает числовое значение, характеризующее количество месяцев, которые требуется прибавить или вычесть, чтобы найти месяц, для которого определяется номер последнего дня.

Примечания:

1. В качестве аргумента нач_дата вводят данные, являющиеся результатом вычислений функций, работающих с датами (например, =ДАТА(2019;06;12). Вводить даты как текст не рекомендуется, поскольку это может привести к возникновению ошибок. Например, не следует указывать даты следующим способом — =КОНМЕСЯЦА(“12.06.2019”;3).
2. Если в качестве аргумента число_месяцев указано дробное число, функция КОНМЕСЯЦА усекает дробную часть. Например, =КОНМЕСЯЦА(СЕГОДНЯ();1,89) и КОНМЕСЯЦА(СЕГОДНЯ();1) выполнятся, вернув одинаковый результат.
3. Если первый аргумент рассматриваемой функции принимает значение, которое не может быть преобразовано к формату данных Дата, результатом выполнения будет код ошибки #ЗНАЧ!. Аналогичная ошибка возникнет, если второй аргумент указан не преобразуемыми к числовому значению данными. Промежуточные преобразования не выполняются даже над данными логического типа. Например, =КОНМЕСЯЦА(СЕГОДНЯ;ИСТИНА) выполнится с ошибкой.
4. Аргумент число_месяцев может принимать следующие числовые данные:

• a. Из диапазона отрицательных значений – определение месяца из прошедших дат (например, =КОНМЕСЯЦА(СЕГОДНЯ();-3) – определение числа дней в месяце, который отстоит на 3 месяца от текущего);
• b. Из диапазона положительных значений – последующие даты;
• c. 0 – текущий месяц.

Перейти к содержанию

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

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

• Первый день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());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

​Смотрите также​:​ 2003 эта функция​​ той частью =​​», что в Excel,​ когда делаю F2​IvanOK​​Казанский​​ Windows RT с​

1. ​ формате, отстоящую вперед​ выходные дни и​ от начальной даты.​

   ​Функция​
​ дней до конца​

   

​ вторник), приведено в​​=КОНМЕСЯЦА($A2;0)-ОСТАТ(КОНМЕСЯЦА($A2;0)​​ по формуле ниже:​​Чтобы получить дату последнего​Serge 007​ используется только кириллицей,​а вы все​

1. ​ что в VBA​ потоп enter все​: ето реальный пример​

   ​,​
​ архитектурой ARM. Подробнее​

   

2. ​ или назад на​ их количество.​КОНМЕСЯЦА​Описание​ года можно в​

   ​ статье Первый вторник​
​+4​

   

​=EOMONTH(A1,-8)​​ дня месяца в​,ну вы согласны с​ поэтому будет работать​ таки попробуйте то​Смотрите видео, так​ стает на всои​ с кода который​

​cells(i,1).FormulaR1C1 = «(КОНМЕСЯЦА(RC[-3],0))»вот​ об этих различиях.​
​ заданное количество рабочих​
​ТДАТА​

​Возвращает дату в числовом​

office-guru.ru

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

​ДАТА​ этой статье.​ месяца (обратная задача).​

​;7)​​=КОНМЕСЯЦА(A1;-8)​​ Excel, используйте функцию​ тем что даже​ на русском языке,​ что я писал​

​ будет понятнее​​ места…вот в чем​​ работает и дает​

​ часть формулы, но​IvanOK​

​ дней.​​Возвращает текущую дату и​​ формате для последнего​

​Возвращает заданную дату в​Формула =»Осталось «&КОНМЕСЯЦА(A1;0)-A1&» дней до​

​Найдем количество дней, оставшихся​​Для определения последней пятницы​​Примечание:​

​EOMONTH​ на вашем примере​ а в 2007/2010​ и все поймете​http://youtu.be/5ghjY0V39fk​

​ проблема…​ нормальный результат в​ все ошыбки именно​: я програмно заполняю​РАБДЕНЬ.МЕЖД​ время в числовом​ дня месяца, отстоящего​ числовом формате.​ конца «&ТЕКСТ(A1;»[$-FC19]ММММ») позволяет​ до конца месяца.​

​ месяца:​Вычисления происходят примерно​(End of Month).​ есть етот косяк​ — это уже​вы мне подали​IvanOK​и ето в​ 2003, проблема возникла​ из-за етой функции,​

excel2.ru

Сколько осталось дней до конца месяца в MS EXCEL

​ формулы конмесяца и​Возвращает числовое значение даты,​

​ формате.​ вперед или назад​РАЗНДАТ​ вывести фразу «Осталось​Если требуется найти сколько​=КОНМЕСЯЦА($A2;0)-ОСТАТ(КОНМЕСЯЦА($A2;0)​

​ так: = 6​

​ В русской версии​vitalife​ встроенная функция, поэтому​ хорошую идею, в​: = есть, попробуйте​ 2010​

​ в 2010​ саму причину проблеми​ он выбивает #имя,​ предшествующей заданному количеству​СЕКУНДЫ​​ на заданное число​​Вычисляет количество дней, месяцев​
​ 26 дней до​

​ осталось дней до​​+1​
​ — 8 =​ –​: Добрый день.​ если ее прописываем​ ексель 2003 я​

​ для себя на​Добавлено через 52 минуты​протестировал в 2003,​ понять не могу​ но когда при​ рабочих дней или​Преобразует дату в числовом​

​ месяцев.​ или лет между​ конца августа». Название​ конца месяца начиная​

excel2.ru

Функции даты и времени (справка)

​;7)​ -2 или -2​КОНМЕСЯЦА​Подскажите, как из​

​ через .Formula или​​ етого не замечал,​ и так и​проблема решена с​ работает и так​ почему так​ нажатии клавиши F2​ следующей за ними,​ формате в секунды.​ЧАС​ двумя датами. Эта​ месяца склоняется! см.​

​ с текущей даты,​	​В файле примера приведена​
​ + 12 =​	​(конец месяца).​ даты извлечь первый​
​ .FormulaR1C1, то нужно​	​ а вот 2010​ так, разных офисах​ помощю вашего совета​ и етак….к 2010​ето все работает….проверено​
​ и потом enter​	​ руководствуясь при этом​ВРЕМЯ​
​Преобразует дату в числовом​	​ функция полезна в​ файл примера.​ то нужно записать​
​ универсальная формула для​	​ 10, т.е. получается​Для примера попробуем получить​
​ и последний день​	​ писать на английском,​ с етим проблема​ и вы меня​
​а вообще то​	​ еще не тестил….​Serge 007​ ошыбка прекрасно исчезает…​ параметрами, указывающими выходные​Возвращает заданное время в​
​ формате в часы.​	​ формулах расчета возраста.​О форматировании дат читайте​ формулу​ нахождения нужного дня​ октябрь 2011 года.​ дату последнего дня​
​ месяца.​	​ либо все писать​Serge 007​
​ поймете)​	​ 2003 понимает и​Все имена заняты​: Вместо​
​ то есть формула​	​ дни и их​ числовом формате.​
​НОМНЕДЕЛИ.ISO​	​ДАТАЗНАЧ​ в статье Пользовательский формат​
​=КОНМЕСЯЦА(СЕГОДНЯ();0)-СЕГОДНЯ()​	​ по порядковому номеру​Урок подготовлен для Вас​ текущего месяца.​
​Например, есть дата:​	​ на русском, но​, ну даже на​Serge 007​ так и етак…​: А то, что​»(КОНМЕСЯЦА(RC[-3],0))»​
​ считается нормально, в​	​ количество.​ВРЕМЗНАЧ​Возвращает номер недели по​
​Преобразует дату из текстового​	​ ДАТЫ и ВРЕМЕНИ​В формуле предполагается, что​
​ дня недели.​	​ командой сайта office-guru.ru​=EOMONTH(A1,0)​
​ 15.06.2017 г.​	​ через .FormulaLocal​ вашем видео показано​
​: Ткните пальцем где​	​Serge 007​ идет обращение к​
​пишите​	​ чем проблема, ето​ГОД​Преобразует время из текстового​
​ ISO для заданной​	​ формата в числовой.​ в MS EXCEL.​ если насупил последний​В качестве аргументов формулы​Источник: http://www.excel-easy.com/examples/last-day-of-the-month.html​
​=КОНМЕСЯЦА(A1;0)​	​Нужно из нее​Serge 007​ что 2003 на​ есть «=» в​:​
​ третьему с конца​	​»=EOMONTH(RC[-3],0)»​ все в офисе​Преобразует дату в числовом​ формата в числовой.​ даты.​ДЕНЬ​Чтобы просмотреть более подробные​ день месяца, то​
​ задаются: порядковый номер​	​Перевел: Антон Андронов​Примечание​
​ извлечь в одну​	​: Ну наконец-то! А​ русском выдает нормально,​ Вашей записи Вы​IvanOK​

​ столбцу — это​​IvanOK​ 2010 хотя в​ формате в год.​СЕГОДНЯ​МИНУТЫ​Преобразует дату в числовом​ сведения о функции,​ целых дней уже​ дня недели, день​Автор: Антон Андронов​: функция​

support.office.com

Вычисление функции конмесяца в ексель 2010

​ ячейку B2 дату:​​ то всё утверждаете​ а на 2010​ видео смотрели? Специально​, не путайте форумчан​ так и задумано?​:​ 2003 етой проблемы​ДОЛЯГОДА​Возвращает текущую дату в​Преобразует дату в числовом​ формате в день​ щелкните ее название​ не осталось в​ недели, месяц и​

​Определим последний вторник месяца,​​EOMONTH​ 01.06.2017 г., в​ что работает​ нет…. вот и​ для Вас снял,​Вот это:​

​IvanOK​​Serge 007​ нет вообще​

​Возвращает долю года, которую​​ числовом формате.​​ формате в минуты.​​ месяца.​
​ в первом столбце.​ этом месяце.​ год. Универсальная формула​ последнюю пятницу или​(КОНМЕСЯЦА) возвращает порядковый​ ячейку B3 дату:​Вот это-то и​
​ вся проблема​

​ что бы Вы​​»(КОНМЕСЯЦА(RC[-3],0))»​
​:​
​, хорошо почему тогда​
​alx74​

​ составляет количество дней​​ДЕНЬНЕД​​МЕСЯЦ​​ДНИ​Примечание:​Если требуется найти сколько​

​ представляет собой комбинацию​​ последнюю среду.​
​ номер даты. Примените​ 30.06.2017 г.​ интересно, что за​m-ch​ ерунду не писали​
​НЕ БУДЕТ работать​Все имена заняты​

​ в 2003 работает​​: Может с F9​ между начальной и​Преобразует дату в числовом​Преобразует дату в числовом​Возвращает количество дней между​ Маркер версии обозначает версию​
​ осталось дней до​ двух вышеуказанных формул​Пусть в ячейке​ формат даты для​

​Файл во вложении.​​ косяк в 2003,​: Будет работать либо​Если У Вас​ НИ В ОДНОЙ​, не важно куда​

​ без вяких проблем​​ связано (автоматический пересчет​​ конечной датами.​​ формате в день​ формате в месяцы.​ двумя датами.​ Excel, в которой​ конца месяца от​ (файл примера размещен​А2​ правильного отображения.​Заранее спасибо.​ что FormulaR1C1 принимается​ так, либо по​ работает — так​ версии Excel по​ оно обращается проблема​Serge 007​
​ формул)? Если попробовать​Важно:​
​ недели.​
​ЧИСТРАБДНИ​ДНЕЙ360​
​ она впервые появилась.​ произвольной даты, расположенной​ в статье Первый​

​может находится дата.​​Теперь попытаемся вычислить дату​​_Boroda_​​ на локальном языке?​
​ другому (проверил только​​ продемонстрируйте это​​ причине того что​ в другом…. при​: Это вряд ли​ не F2 и​ Вычисляемые результаты формул и​НОМНЕДЕЛИ​Возвращает количество полных рабочих​Вычисляет количество дней между​ В более ранних​ в ячейке​​ вторник месяца).​​ Для определения последнего​ последнего дня следующего​
​: нач​Такого быть не​
​ что в 2003​

​IvanOK​​ это НЕ ФОРМУЛА,​ внесении формулы в​В таком виде,​ Enter, а F9​ некоторые функции листа​Преобразует дату в числовом​

​ дней между двумя​​ двумя датами на​ версиях эта функция​А1​Решение обратной задачи: если​ понедельника месяца можно​ месяца.​=B1-ДЕНЬ(B1)+1​
​ должно (необходимо использовать​ и в 2007)​:​

​ а текст. Для​​ ячейку не делаеться​​ как Вы выложили,​​ нажать.​ Excel могут несколько​ формате в число,​ датами.​ основе 360-дневного года.​
​ отсутствует. Например, маркер​, то используйте формулу:​ дана дата, а​ использовать формулу:​
​=EOMONTH(A1,1)​илиКод=КОНМЕСЯЦА(B1;-1)+1илиКод=—ТЕКСТ(B1;»1.М.г»)А последняя и​ FormulaLocal), однако факт​функция КОНМЕСЯЦА() в​Serge 007​ того что бы​
​ расчет, а выдает​​ код не будет​Казанский​ отличаться на компьютерах​ которое указывает, на​ЧИСТРАБДНИ.МЕЖД​ДАТАМЕС​ версии 2013 означает,​

​=КОНМЕСЯЦА(A1;0)-A1​​ нужно определить, какой​=КОНМЕСЯЦА($A2;0)-ОСТАТ(КОНМЕСЯЦА($A2;0)​=КОНМЕСЯЦА(A1;1)​ так, без единички,​ налицо. Так что​
​ 2003 офисе изначально​, я часть формулы​ выражение стало формулой?​ ошыбку #имя, проблема​ работать в принципе,​
​: Покажите код. Возможно,​ под управлением Windows​ какую неделю года​Возвращает количество полных рабочих​Возвращает дату в числовом​ что данная функция​СОВЕТ​ по счету это​+5​Мы даже можем рассчитать​ получитсяКод=—ТЕКСТ(B1;»М.г»)​ в 2010 работает​ отсутствует, работает только​ показал и моя​ оно обязано начинаться​

​ в том что​​ в любой версии​ FormulaLocal вместо Formula.​ с архитектурой x86​
​ приходится дата.​ дней в интервале​ формате, отстоящую на​ доступна в выпуске​:​
​ день недели в​;7)​ дату последнего дня​конКод=КОНМЕСЯЦА(B1;0)​ правильно, а в​ при подключенном «Пакете​ ошыбка в том​

​ со знака «​​ функцию ексель как​​Попробуйте сами и​​IvanOK​ или x86-64 и​РАБДЕНЬ​ между двумя датами,​

CyberForum.ru

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

​ заданное число месяцев​​ Excel 2013 и всех​
​О том как​ текущем месяце (например,​Для определения последнего вторника​ месяца, который был​
​vitalife​ 2003 — нет​
​ анализа»​ что удалил с​=​ бы не распознает,​ убедитесь​:​
​ компьютерах под управлением​
​Возвращает дату в числовом​

​ руководствуясь параметрами, указывающими​​ вперед или назад​​ последующих версиях.​​ найти количество оставшихся​ 01/03/2011 – первый​ месяца:​
​ 8 месяцев назад​

​: Благодарю!​​IvanOK​

excelworld.ru

​В «Пакете анализа»​

Функция КОНМЕСЯЦА

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

В этой статье описаны синтаксис формулы и использование функции КОНМЕСЯЦА в Microsoft Excel.

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

Аргументы функции КОНМЕСЯЦА описаны ниже.

нач_дата — обязательный аргумент. Начальная дата. Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 года следует воспользоваться выражением ДАТА(2008;5;23). Если ввести даты как текст, это может привести к возникновению проблем.

Число_месяцев — обязательный аргумент. Количество месяцев до или после даты «нач_дата». Положительное значение аргумента «число_месяцев» означает будущие даты; отрицательное значение — прошедшие даты.

Примечание: Если значение аргумента «число_месяцев» не является целым числом, оно усекается.

В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.

Если нач_дата не является допустимой датой, КОНМЕСЯЦА Возвращает значение #NUM! значение ошибки #ЧИСЛО!.

Если нач_дата и месяцы возвращают недопустимую дату, КОНМЕСЯЦА Возвращает #NUM! значение ошибки #ЧИСЛО!.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Функция КОНМЕСЯЦА вычисляет последний день месяца в Excel

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

Как получить последний день месяца по дате в Excel

Поскольку каждый из месяцев года имеет определенное число дней (30 или 31), данная функция полезна для быстрого нахождения номера числа любого месяца. Единственным исключением является февраль – 28 дней. Однако, раз в 4 года этот месяц имеет 29 дней (если год високосный).

Пример 1. Вывести количество дней в каждом месяце 2019 года в отдельный столбец таблицы.

Вид таблицы данных:

Для расчета числа дней используем следующую формулу массива, предварительно выделив ячейки B2:B13:

В качестве даты как точки отсчета была указана 01.01.2019 (первый день текущего года). Для расчета числа дней первого и последующих месяцев была вычтена 1 (если второй аргумент функции КОНМЕСЯЦА принимает значение 0, определяется число дней текущего месяца). Функция ТЕКСТ используется для преобразования полученного значения и вывода только числа дней (формат «ДД»).

Важный момент! Чтобы выполнять арифметические операции со значениями, которые возвращает данная формула следует использовать функцию ЗНАЧЕН. Она позволит преобразовать текст в числовое значение:

На этот раз мы использовали формулу массива, которую следует вводить через комбинацию клавиш CTRL+SHIFT+Enter для подверженная. Таким образом перед суммированием функцией СУММ значение из каждой ячейки преобразуется в число с помощью функции ЗНАЧЕН.

Как определить последнее воскресенье мая месяца в Excel

Пример 2. Использовать формулы, включающие функцию КОНМЕСЯЦА, для определения следующих значений:

1. Каким днем недели является последний день мая месяца текущего года?
2. Какой номер дня соответствует последнему воскресению мая месяца в текущем году?

Для будущей проверки результатов вычисления формул сразу уточним, что на момент написания данного урока на дворе шел 2019-й год.

Для нахождения последнего дня мая месяца для текущего года используем следующую формулу:

Функция ТЕКСТ используется для преобразования данных (вывода текстового представления дня недели). Функция ДЕНЬНЕД возвращает числовое значение дня недели (от 1 до 7). Функция КОНМЕСЯЦА вычисляет дату последнего дня текущего месяца (аргументы СЕГОДНЯ и 0 соответственно).

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

Чтобы убедиться, что был получен корректный результат, используем формулу для проверки последнего понедельника мая месяца в текущем году. В ячейке B9 водим формулу:

Правила использования функции КОНМЕСЯЦА в Excel

Функция имеет следующий синтаксис:

• нач_дата – обязательный, принимает значения формата Дата, используемые как точка отсчета, относительно которой будет найден месяц, для которого требуется определить номер последнего дня;
• число_месяцев – обязательный, принимает числовое значение, характеризующее количество месяцев, которые требуется прибавить или вычесть, чтобы найти месяц, для которого определяется номер последнего дня.

1. В качестве аргумента нач_дата вводят данные, являющиеся результатом вычислений функций, работающих с датами (например, =ДАТА(2019;06;12). Вводить даты как текст не рекомендуется, поскольку это может привести к возникновению ошибок. Например, не следует указывать даты следующим способом — =КОНМЕСЯЦА(“12.06.2019”;3).
2. Если в качестве аргумента число_месяцев указано дробное число, функция КОНМЕСЯЦА усекает дробную часть. Например, =КОНМЕСЯЦА(СЕГОДНЯ();1,89) и КОНМЕСЯЦА(СЕГОДНЯ();1) выполнятся, вернув одинаковый результат.
3. Если первый аргумент рассматриваемой функции принимает значение, которое не может быть преобразовано к формату данных Дата, результатом выполнения будет код ошибки #ЗНАЧ!. Аналогичная ошибка возникнет, если второй аргумент указан не преобразуемыми к числовому значению данными. Промежуточные преобразования не выполняются даже над данными логического типа. Например, =КОНМЕСЯЦА(СЕГОДНЯ;ИСТИНА) выполнится с ошибкой.
4. Аргумент число_месяцев может принимать следующие числовые данные:

• a. Из диапазона отрицательных значений – определение месяца из прошедших дат (например, =КОНМЕСЯЦА(СЕГОДНЯ();-3) – определение числа дней в месяце, который отстоит на 3 месяца от текущего);
• b. Из диапазона положительных значений – последующие даты;
• c. 0 – текущий месяц.

Первый и последний день месяца в 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

Часто в процессе работы с датами возникает необходимость динамического определения даты и последнего дня недели в месяце. Естественно можно просто посмотреть в обычный календарь, но для автоматизации некоторых задач в Excel с помощью формул нужно динамически получить дату последнего месяца особенно в феврале и определить день недели. Ведь у високосного года (366) месяц февраль имеет 29 дней, а в обычном году – 28.

Сколько дней в феврале для любого года и любой даты?

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

Анализируя пример на данном рисунке, следует помнить, что данную формулу можно применять для любого месяца, чтобы получить дату последнего дня, а не только для февраля. Иначе она выглядела бы так:

Функция ДАТА позволяет генерировать дату на лету с помощью трех аргументов:

1. Год – можно указать любое число только лишь в диапазоне от 1900 и до 9999.
2. Месяц – любое положительное или отрицательное число.
3. День – любое положительное или отрицательное число.

Например, если в третьем аргументе указано число 0 как номер дня, Excel вычислит дату последнего дня из предыдущего месяца. Для наглядного примера составим формулу с числом 0, которая возвращает 29 февраля 2000-го года:

В следующем примере формулы вместо ввода статических значений в первые 2 аргумента функции ДАТА (год и месяц), используется функция ГОД, которая возвращает годы с исходных дат. Аналогично используется функция МЕСЯЦ возвращающая порядковый номер месяца в году из исходной даты. К этому номеру в формуле добавляется число 1. Благодаря чему мы получаем номер следующего месяца. Так как в третьем аргументе стоит число 0, автоматически получаем порядковый номер последнего дня в текущем месяце, который нас интересует.

Какой день недели для последнего дня месяца?

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

Формула состоит из двух частей, разделенных соединяющим оператором амперсантом (&» — «&):

1. В первой части функция ТЕКСТ преобразует числовое значение, которое возвращает функция ДАТА в формат даты. Он указано во втором аргументе функции – «дд.мм.гг»
2. Во второй части функция ТЕКСТ определяет день недели для исходной даты в первом ее аргументе, с помощью формата ячеек «дддд» указанном во втором аргументе.

Пример использования функции КОНМЕСЯЦА

В новых версиях Excel предусмотрена функция КОНМЕСЯЦА, которая служит альтернативным решением для выше описанной формулы:

Благодаря функции КОНМЕСЯЦА можно определять дату для текущего, предыдущего или будущего месяца заполнив лишь только 2 аргумента:

1. Нач_дата – исходная дата или ссылка на нее.
2. Число_месяцев – порядковый номер месяца начиная от исходной даты. Может быть любое положительное или отрицательное число. Примеры заполнения аргументов для функции КОНМЕСЯЦА, которая возвращает дату 29 февраля 2000-го года:

Если же мы укажем во втором аргументе отрицательное число, тогда функция возвращает нам дату последнего дня для предыдущего месяца:

Разумеется, если мы указываем во втором аргументе число 0 тогда получим необходимое значение даты последнего дня для текущего месяца:

Как определить високосный год или нет в Excel?

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

При работе с датами особую сложность предоставляет тот факт, что раз в 4-е года количество дней в году составляет 366, тогда как в обычных годах 365. Поэтому иногда приходится воспользоваться вышеописанными примерами и формулами для различных вычислений в Excel.

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

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

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

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

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

Первый день месяца в 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 ввести​Рассмотрим пример работы с​ ячейку следующую формулу​

​ 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 дней​

Возможно, вам легко получить дату окончания месяца, но если в Excel есть данные с несколькими датами, как вы можете быстро получить сразу все даты окончания каждого месяца? Теперь я предлагаю вам несколько быстрых приемов решения этой проблемы в Excel.

Получить дату окончания месяца с формулами в Excel

Получить дату окончания месяца с помощью VBA в Excel

---

Получить дату окончания месяца с формулами в Excel

Использование формулы для определения даты окончания каждого месяца может быть очень простым для каждого пользователя Excel.

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

Также вы можете использовать эту формулу = EOMONTH (A2,0; XNUMX) чтобы получить дату окончания месяца. (Перед вводом этой формулы вам необходимо отформатировать ячейки результатов как формат даты.)

Совет: в Excel 2003 или более ранней версии, если вы хотите использовать формулу = EOMONTH (A2,0; XNUMX), вам необходимо установить Пакет аналитических инструментов кликнув Tools
> Надстройки первый. Смотрите скриншоты:

---

Получить дату окончания месяца с помощью определенной функции в Excel

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

1. Нажмите F11 + Альт вместе на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.

2. Затем нажмите Вставить > Модули чтобы вставить новое окно модуля. А затем скопируйте следующий код VBA в окно.

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

Function LastDayInMonth(Optional pDate As Date = 0) As Date
'Updateby20140723
If pDate = 0 Then pDate = VBA.Date
LastDayInMonth = VBA.DateSerial(VBA.Year(pDate), VBA.Month(pDate) + 1, 0)
End Function

3. Сохраните и закройте окно. Затем выберите ячейку и введите эту формулу = LastDayInMonth (A2) (A2 содержит дату, с которой вы хотите получить дату окончания месяца), затем нажмите Enter ключ, и вы получите дату окончания месяца.

Внимание: Этот VBA также требует, чтобы вы отформатировали ячейки результатов как формат даты перед вводом этой формулы = LastDayInMonth (A2).

---

Относительные статьи:

• Найти первый / последний рабочий день месяца в Excel

---

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

• Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
• Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
• Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
• Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
• Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
• Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
• Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
• Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
• Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

---

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (0)

Оценок пока нет. Оцените первым!

Получить день

Получить дату с номера дня

= ДАТА(год; 1; день)

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

В показанном примере формула в C5:

= ДАТА(2015;1; B5)

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

Например, ДАТА возвращает 9 апреля 2016 года со следующими аргументами:

= ДАТА(2016;1;100)

В январе нет 100-го дня, поэтому ДАТА просто продвигается на 100 дней вперед с 1 января, а цифры возвращают правильную дату.

Формула на этой странице использует эту механику. В этом случае предполагается, что в этом году 2015 год, поэтому 2015 год жестко закодирован в течение года, а 1 используется в течение месяца. Значение дня выводится из столбца B, а функция ДАТА рассчитывает дату, как объяснено выше.

Если у вас есть дата в формате Юлиан, например 10015, где используется формат «дддгг», вы можете изменить формулу следующим образом:

= ДАТА(ПРАВСИМВ(A1;2); 1; ЛЕВСИМВ(A1;3))

Здесь мы используем ПРАВСИМВ, чтобы извлечь 2 символа справа за год, и ЛЕВСИМВ, чтобы извлечь 3 символа слева в течение дня. Месяц представлен как 1, как в первом примере.

Получить день с даты

= ДЕНЬ(дата)

Если вам необходимо извлечь день из даты, вы можете использовать функцию ДЕНЬ. В общей форме приведенной выше формула дата должна быть в форме, которую Excel распознает как действительную дату.

Функция ДЕНЬ принимает только один аргумент — дату, из которой вы хотите извлечь день. В этом примере формула:

= ДЕНЬ (B3)

B4 содержит значение даты для 5 января 2016 года. Функция ДЕНЬ возвращает число 5, представляющее компонент дня даты.

Обратите внимание: вы можете использовать ДЕНЬ для извлечения дня из дня, введенного как текст:

= ДЕНЬ(«1/5/2016»)

Но это может привести к непредсказуемым результатам на компьютерах, использующих разные региональные настройки даты. В общем, лучше (и более гибко) указать адрес ячейки, в которой уже есть действительное значение даты в качестве аргумента для ДЕНЬ.

Получить имя дня с даты

ТЕКСТ (B4; «дддд»)

Если вам необходимо получить имя дня (например, понедельник, вторник и т. д.) С даты, есть несколько вариантов в зависимости от ваших потребностей.

Если вы хотите отображать имя дня, вам не нужна формула — вы можете использовать формат персонализированного номера. Выберите дату, затем перейдите к Форматирование ячеек (Ctrl + 1 или Cmd + 1)> Custom и введите один из этих настраиваемых форматов:

«ддд» // «сред»

«дддд» // «среда»

Excel отобразит только имя дня, но оно оставит неизменным значение даты.

Если вы хотите преобразовать значение даты в текстовое значение, вы можете использовать функцию ТЕКСТ с настраиваемым числовым форматом, например, «ддд». Формула выглядит так:

= ТЕКСТ (B4; «ддд»)

Функция ТЕКСТ преобразует значения в текст с использованием формата чисел, который вы предоставляете. Обратите внимание, что в преобразовании теряется дата, остается только текст названия дня.

Если вы не хотите использовать вспомогательную таблицу, вы также можете отобразить номер дня недели в название месяца непосредственно в функции ВЫБОР следующим образом:

= ВЫБОР (ДЕНЬНЕД(B4); «Вс»; «Пн»; «Вт»; «Ср»; «Чт»; «Пт»; «Сб»)

В этом случае введите имена дней недели, которые вы хотите вернуть (сокращенно или нет), в качестве значений в ВЫБОР после первого аргумента. ДЕНЬНЕД будет извлекать номер буднего дня, и ВЫБОР будет использовать это число для возврата n-го значения в списке. Это работает, потому что ДЕНЬНЕД возвращает число 1-7, соответствующее заданному дню недели. С настройками по умолчанию, воскресенье = 1 и суббота = 7. Смотрите ДЕНЬНЕД для получения дополнительной информации.

ВЫБОР — это больше работы по настройке, но она также более гибкая, так как позволяет привязать дату к любым требуемым значениям (т. е. Вы можете использовать значения, которые являются пользовательскими, сокращенными, не сокращенными, разными языками и т. д.).

Получить дни до даты

= дата-СЕГОДНЯ ()

Чтобы вычислить дни до определенной даты, вы можете использовать вычитание и функцию СЕГОДНЯ. В обобщенной версии вышеприведенной формулы дата — это более поздняя дата — день, на который вы рассчитываете, — а функция СЕГОДНЯ используется для создания текущей даты.

В примере D5 содержит эту формулу:

= B4-СЕГОДНЯ()

В Excel даты — это просто серийные номера. В стандартной системе дат для окон, основанной на 1900 году, где 1 января 1900 года является номером 1. Даты действительны до 9999, что является серийным номером 2 958 465. Это означает, что 1 января 2050 года серийный номер 54789.

В примере датой является 9 марта 2016 года, который является серийным номером 42 438. Так:

= B4-СЕГОДНЯ()

= 1 января 2050 — 27 апреля 2014 г.

= 54;789 — 42;438

= 12;351

Это означает, что до 1 января 2050 года, когда отсчитывается с 9 марта 2016 года, существует 13 033 дня.

Примечание: вам не нужно использовать функцию СЕГОДНЯ. Во втором примере формула в D6:

= B6-C6

В третьем примере одна и та же базовая формула используется вместе с оператором конкатенации (&) для встраивания вычисленных дней в простое текстовое сообщение:

= «Осталось всего» & B6-C6 & «дней!»

Поскольку между 10 декабря 2014 года и 25 декабря 2014 года существует 15 дней, результатом является следующее сообщение:

Осталось всего 15 дней!

Получить дни между датами

= Поздняя дата-Ранняя дата

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

В примере формула в ячейке D6 возвращает 365, поскольку между 1/1/1999 и 1/1/2000 есть 365 дней.

Даты в Excel — это просто серийные номера, которые начинаются 1 января 1900 года.

В примере формула в ячейке D6 просто вычитает числовое значение 1/1/1999 (36161) из числового значения 1/1/2000 (36526), чтобы получить результат 365. Шаги выглядят так:

= C5-B5

= 1/1 / 1999-1 / 1/2000

= 36161-36526

= 365

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

= СЕГОДНЯ() – ранняя дата

Если вам необходимо вычислить количество дней между более поздней датой и сегодняшним днем, используйте:

= Поздняя дата-СЕГОДНЯ()

Обратите внимание, что СЕГОДНЯ будет пересчитываться на постоянной основе. Если вы откроете книгу позднее, значение, используемое для СЕГОДНЯ, обновится, и вы получите новый результат.

Функция ДНИ, введенная в Excel 2013, также вычисляет дни между двумя датами.

Получайте дни между датами, игнорируя годы

= РАЗНДАТ (нач_дата; кон_дата; «гд»)

Для вычисления дней между двумя датами, игнорируя значения года, используйте функцию РАЗНДАТ. В показанном примере формула в D5:

= РАЗНДАТ(B5; C5; «yd»)

Функция РАЗНДАТ может обрабатывать различные расчеты «разницы дат» для вычисления разницы между двумя датами в годах, месяцах и днях. РАЗНДАТ принимает 3 аргумента: начальную дату, конечную дату и единицу измерения, которая контролирует, какой результат будет возвращен.

В этом случае мы хотим, чтобы дни игнорировались годами, поэтому мы поставляем «yd» для единицы.

После настройки функция полностью автоматическая и возвращает результат в запрошенном модуле.

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

= Дата-ДЕНЬ(дата) +1

Чтобы получить первый день месяца для определенной даты, вы можете использовать простую формулу, основанную на функции ДЕНЬ.

В показанном примере формула в ячейке B5:

= B5-ДЕНЬ(B5) +1

Функция ДЕНЬ возвращает значение дня для даты. В примере показано, что значение дня для даты в B5 (5 июня 2016 года) равно 5. Поскольку даты — это только порядковые номера в Excel, мы можем вычесть 5, а затем добавить 1, чтобы получить значение даты на 1 июня 2016 года.

Получить первый день предыдущего месяца

= КОНМЕСЯЦА(дата; -2) +1

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

В показанном примере формула в ячейке B5:

= КОНМЕСЯЦА(B5; -2) +1

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

В показанном примере месяцев указываются как -2, что заставляет КОНМЕСЯЦА возвращаться 4/30/2015. Затем добавляется 1 день для получения 5/1/2015.

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

= КОНМЕСЯЦА(дата; 0)

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

В показанном примере формула в ячейке B5:

= КОНМЕСЯЦА(B5;0)

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

Чтобы получить последний день предыдущего месяца, используйте:

= КОНМЕСЯЦА(дата; -1)

Чтобы получить последний день следующего месяца, используйте:

= КОНМЕСЯЦА(дата; 1)

Если вы предпочитаете создавать умные формулы, которые озадачивают других, вы также можете использовать функцию ДАТА, чтобы получить последний день месяца:

= ДАТА(ГОД (дата); МЕСЯЦ(дата) +1;0)

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

Получить последний рабочий день в месяц

=РАБДЕНЬ (КОНМЕСЯЦА(дата) + 1; -1)

Чтобы получить последний рабочий день в месяце, вы можете использовать функцию РАБДЕНЬ вместе с функцией КОНМЕСЯЦА.

В примере формула в C5:

= РАБДЕНЬ (КОНМЕСЯЦА (B5;0) + 1; -1)

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

Эта дата переходит в функцию РАБДЕНЬ как «дата начала», а также -1 для «дней». Функция РАБДЕНЬ автоматически отступает на 1 день, принимая во внимание любые выходные. В результате будет последний рабочий день месяца.

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

= РАБДЕНЬ(КОНМЕСЯЦА(B5;0) + 1; -1; праздничные дни)

Функция РАБДЕНЬ предполагает, что выходные — это суббота и воскресенье. Если вам необходимо настроить выходные дни, вы можете использовать функцию РАБДЕНЬ.МЕЖД.

Получить n-ый день года

= Дата-ДАТА (ГОД (дата); 1;0)

Если вы хотите получить день года с даты (например, n-го числа года), вы можете использовать формулу, которая использует функции ДАТА и ГОД вместе.

Например, с датой «1 июня 2016 года» в ячейке B4 следующая формула вернет 153:

= B4-ДАТА(ГОД (B4); 1;0)

То есть 1 июня 2016 года — 153-й день этого года.

Эта формула использует тот факт, что даты — это просто порядковые номера в Excel. Он определяет последний день предыдущего года и вычитает его, начиная с исходной даты B4. Результат — n-й день года.

Обратите внимание, что аргумент день в ДАТА предоставляется как ноль. Хорошей особенностью функции ДАТА является то, что она может обрабатывать значения ДЕНЬ, которые находятся «вне диапазона», и соответствующим образом корректировать результат. Когда мы даем ДАТА год, 1 месяц и 0 дней, функция ДАТА возвращает последний день предыдущего года.

Чтобы настроить формулу для возврата n-го дня года для текущей даты, просто используйте функцию СЕГОДНЯ для даты:

= СЕГОДНЯ () — ДАТА (ГОД (СЕГОДНЯ ()); 1;0)

Получить день недели

Получить последний день недели

= Дата-ОСТАТ(дата-день; 7)

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

Формулы, в которых используется понятие «день недели», основаны на идее, что каждый день недели имеет определенное число. По умолчанию Excel назначает 1 на воскресенье и 7 на субботу.

В примере формула B6 является датой 1/16/2015, а формула в C6:

= B6-ОСТАТ(B6-7;7)

Число 7 (числовой аргумент в функции ОСТАТ) представляет день недели (день), который вы хотите. Только помните, что 1 — это воскресенье, а 7 — суббота. Так принято в США.

Чтобы решить эту формулу, Excel сначала вычитает день (7 в данном случае) из даты, а затем передает результат в функцию ОСТАТ в качестве числа. ОСТАТ возвращает остаток от деления этого числа на 7, которое затем вычитается из даты.

= B6-ОСТАТ(B6-7;7)

= B6-ОСТАТ(42013;7)

= B6-6

= 42014

= 1/10 / 2015

Если вы хотите получить последний день недели с текущей даты, вы можете использовать функцию СЕГОДНЯ следующим образом:

= СЕГОДНЯ () — ОСТАТ(СЕГОДНЯ () — день; 7)

Примечание. Если дата имеет тот же день недели, дата будет возвращена.

Получить следующий день недели

= Дата + 7-ДЕНЬНЕД(дата + 7-день)

Если вы хотите найти следующий конкретный день недели (т.е. следующую среду, пятницу или понедельник) с определенной датой начала, вы можете использовать общую формулу, которая использует функцию ДЕНЬНЕД с чем-то, называемым «день недели» (день). С помощью этой формулы вы можете делать такие вещи:

• Получить следующую пятницу
• Получить следующую субботу
• Получить следующий вторник

Формулы, в которых используется понятие «день недели», основаны на идее, что каждый день недели имеет определенное число. По умолчанию Excel назначает 1 на воскресенье и 7 на субботу. Таким образом, функция ДЕНЬНЕД при заданной дате возвращает число от 1 до 7. В двух словах эта формула сначала переводит дату вперед на 7 дней, а затем возвращается к правильной дате, вычитая результат вычисления, который использует функцию ДЕНЬНЕД.

В примере формула B6 является датой 1/16/2015, а формула в C6:

= B6 + 7-ДЕНЬНЕД(B6 + 7-2)

Число 2 представляет день недели (день), который вы хотите, в данном случае, понедельник.

Чтобы решить эту формулу, Excel сначала добавляет 7 к начальной дате в C6, а затем уменьшает B6 + 7-2 до 42025 (порядковый номер в системе даты Excel, который представляет 1/21/2015). Затем Excel вычисляет день недели для этой даты (4) и вычитает, что из исходной даты начала + 7 (42027 = 1/23/2015), чтобы получить окончательное значение даты 42013 = 1/9/2015

= B6 + 7-ДЕНЬНЕД(B6 + 7-2)

= 42027-ДЕНЬНЕД(42025)

= 42027-4

= 42023

= 1/9/2015

Если вы хотите получить следующий день недели с текущей даты, вы можете использовать функцию СЕГОДНЯ, например:

= СЕГОДНЯ () + 7-НЕДЕЛЯ (СЕГОДНЯ () + 7-день)

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

Получить n-й день недели в месяце

= Дата-ДЕНЬ(дата) + 1 + n * 7-ДЕНЬНЕД(дата-ДЕНЬ (дата) + 8-день)

Чтобы получить n-й день недели в месяце (т. е. первый вторник, третий вторник, четвертый четверг и т. д.), вы можете использовать формулу, основанную на функциях ДЕНЬ и НЕДЕЛЯ.

В показанном примере формула в E5:

= B5-ДЕНЬ(B5) + 1 + D5 * 7-ДЕНЬНЕД(B5-ДЕНЬ(B5) + 8-C5)

Во-первых, эта формула определяет «дату начала». «Первое число месяца» рассчитывается с помощью функции ДЕНЬ:

= B5-ДЕНЬ(B5) +1

Затем n * 7 дней до первого числа месяца, чтобы получить дату начала n недель с первого числа месяца. В общей форме формулы n представляет «n-ое».

Следующий шаг — рассчитать корректировку, необходимую для достижения конечного результата. Корректировка рассчитывается с использованием ДЕНЬНЕД и ДЕНЬ:

ДЕНЬНЕД(B5-ДЕНЬ(B5) + 8-C5)

В ДЕНЬНЕД снова вычисляется первое число месяца, затем добавляются 8 дней и вычитается значение для день (день недели).

День недели сокращенно «день» в общей форме формулы. Это число от 1 (воскресенье) до 7 (суббота), которое может быть изменено по мере необходимости.

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

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

= КОНМЕСЯЦА(дата; 0) + 1-ДЕНЬНЕД (КОНМЕСЯЦА(дата; 0) + 1-день)

Чтобы получить последний день недели за месяц (то есть в прошлую субботу, последнюю пятницу, последний понедельник и т. Д.), Вы можете использовать формулу, основанную на функциях КОНМЕСЯЦА и ДЕНЬНЕД.

В показанном примере формула в D5:

= КОНМЕСЯЦА(B5;0) + 1-ДЕНЬНЕД(КОНМЕСЯЦА(B5;0) + 1-C5)

Во-первых, эта формула определяет первый день следующего месяца * после * данной даты. Он делает это, используя КОНМЕСЯЦА, чтобы получить последний день месяца, а затем добавить один день:

= КОНМЕСЯЦА(B5;0) +1

Затем формула вычисляет количество дней, требуемых для «возврата назад» к последнему запрошенному буднему дню в предшествующем месяце (т. е. Месяце исходной даты):

ДЕНЬНЕД(КОНМЕСЯЦА(B5;0) + 1-C5)

В ДЕНЬНЕД КОНМЕСЯЦА снова используется для получения первого дня следующего месяца. С этой даты вычитается значение для дня недели, и результат вводится в ДЕНЬНЕД, который возвращает количество дней для отката.

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

В общем виде формулы вверху страницы день недели сокращенно «день». Это число от 1 (воскресенье) до 7 (суббота), которое можно изменить, чтобы получить другой день недели. Например, чтобы получить последний четверг месяца, установите значение день равным 5.

Получить первый понедельник до любой даты

= Дата-ДЕНЬНЕД (дата-2)

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

Если вы предполагаете, что у вас есть случайная дата и хотите оглянуться назад во времени, чтобы найти ближайший предыдущий понедельник, вы можете увидеть, что вам нужно будет «отбросить» определенное количество дней в зависимости от дня недели (например, понедельник, вторник, и т. д.) даты. Если дата является понедельником, вам нужно будет вернуться на 7 дней, если дата является вторником, вам нужно вернуться на 1 день и так далее. Ниже приведены корректировки, необходимые для каждого дня недели.

Понедельник 7

Вторник 1

Среда 2

Четверг 3

Пятница 4

Суббота 5

Воскресенье 6

Чтобы рассчитать эту корректировку динамически для любой даты, вы можете использовать тот факт, что понедельник — это второй день недели в схеме по умолчанию Excel. Просто вычислите день недели даты на 2 дня раньше и используйте этот результат для значения корректировки. Итак, при допущении даты 4 июня 2015 года (четверг) в ячейке A1 формула решается следующим образом:

= A1-ДЕНЬНЕД (A1-2)

= 4;2015 июня — ДЕНЬНЕД (4 июня015-2)

= 4;2015 июня — ДЕНЬНЕД(2 июня 2015 г.)

= 4 июня015-3

= Июнь 1;2015

Если вы считаете, что настройка дня недели запутанна и не интуитивна, вы используете альтернативную формулу, которая использует функцию ВЫБОР с жестко заданными значениями регулировки:

= A1-ВЫБОР(ДЕНЬНЕД(A1;2); 7;1;2;3;4;5;6)

В этой версии используется ДЕНЬНЕД, чтобы получить индекс для дня недели (с понедельника как 1), а затем ВЫБОР возвращает правильное количество дней для возврата.

Если вам нужно найти понедельник до сегодняшнего дня, просто используйте функцию СЕГОДНЯ на дату:

= СЕГОДНЯ () — ДЕНЬНЕД(СЕГОДНЯ () — 2)

Получить месяц

Получить месяц с даты

= МЕСЯЦ (дата)

Если вам необходимо извлечь месяц из даты, вы можете использовать функцию МЕСЯЦ. В общей форме приведенной выше формулы дата должна быть в форме, которую Excel распознает как действительную дату.

Функция МЕСЯЦ принимает только один аргумент — дату, из которой вы хотите извлечь месяц. В этом примере формула:

= МЕСЯЦ (B3)

B4 содержит значение даты на 5 января 2016 года. Функция МЕСЯЦ возвращает число 1, соответствующее месяцу (январю) даты.

Обратите внимание, что вы можете использовать МЕСЯЦ для извлечения месяца со дня, введенного как текст:

= МЕСЯЦ(«1/5/2016»)

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

Получить название месяца с даты

= ТЕКСТ (дата; «мммм»)

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

Если вы хотите отображать название месяца, вам не нужна формула — вы можете использовать формат персонализированного номера. Выберите дату и перейдите к Форматирование ячеек (Ctrl + 1 или Cmd +1), затем выберите Custom и введите один из этих настраиваемых форматов:

«Ммм» // «Янв»

«Мммм» // «Январь»

Excel отобразит только название месяца, но оно оставит неизменным значение даты.

Если вы хотите преобразовать значение даты в текстовое значение, вы можете использовать функцию ТЕКСТ с настраиваемым числовым форматом, например «ммм».

= ТЕКСТ (B4; «мммм»)

Функция ТЕКСТ преобразует значения в текст с использованием формата чисел, который вы предоставляете. Обратите внимание, что дата теряется при преобразовании: остается только текст для названия месяца.

Если вы хотите максимальной гибкости, вы можете «сопоставить» название месяца с именем месяца непосредственно в функции ВЫБОР следующим образом:

= ВЫБОР (МЕСЯЦ (B4); «Янв»; «Фев»; «Мар»; «Апр»; «Май»; «Июн»; «Июл»; «Авг»; «Сен»; «Окт»; «Ноя» «;» Дек»)

Введите названия месяца, которые хотите вернуть (сокращенно или нет), в качестве значений в ВЫБОР после первого аргумента, который вводится как МЕСЯЦ (дата). МЕСЯЦ выведет номер месяца, и ВЫБОР будет использовать это число для возврата n-го значения в списке. Это работает, потому что МЕСЯЦ возвращает число 1-12, соответствующее имени месяца.

ВЫБОР — это больше работы по настройке, но она также более гибкая, так как позволяет сопоставлять дату с любыми требуемыми значениями (т. е. Вы можете использовать значения, которые являются обычаями, аббревиатурами, аббревиатурами на другом языке и т. д. ).

Получать месяцы между датами

= РАЗНДАТ (нач_дата; кон_дата; «м»)

Чтобы рассчитать месяцы между двумя датами как целые числа, вы можете использовать функцию РАЗНДАТ.

В показанном примере формула в D5:

= РАЗНДАТ(B5; C5; «м»)

Обратите внимание, что РАЗНДАТ автоматически округляется вниз.

Функция РАЗНДАТ — это функция «совместимости», которая приходит из Лотос 1-2-3. По неизвестным причинам это документировано в Excel 2000, но вы можете использовать его в своих формулах во всех версиях Excel с того времени.

РАЗНДАТ принимает 3 аргумента: дата начала, дата окончания и единица измерения. В этом случае мы хотим месяцы, поэтому мы поставляем «м» для единицы.

РАЗНДАТ автоматически вычисляет и возвращает число в течение месяцев, округленное вниз.

РАЗНДАТ округляется по умолчанию. Если вы хотите рассчитать месяцы до ближайшего целого месяца, вы можете сделать простую корректировку формулы:

= РАЗНДАТ(нач_дата; кон_дата + 15; «м»)

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

Получить год с даты

= ГОД(дата)

Если вам необходимо извлечь год из даты, вы можете использовать функцию ГОД. В общей форме приведенной выше формулы дата должна быть в форме, которую Excel распознает как действительную дату.

Функция ГОД принимает только один аргумент — дату, из которой вы хотите извлечь год. В этом примере формула:

= ГОД(B3)

B4 содержит значение даты для 5 января 2016 года. Функция ГОД возвращает число 2016, представляющее год даты.

Обратите внимание, что вы можете использовать ГОД для извлечения года из дня, введенного как текст:

= ГОД(«1/5/2016»)

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

Извлечение даты с даты и времени

= ЦЕЛОЕ(дата)

Excel обрабатывает даты и время, используя схему, в которой даты — это серийные номера, а время — дробные значения. Например, 1 июня 2000 г. 12:00 PM представлено в Excel как число 36678,5, где 36678 — это дата, а 0,5 — время.

Если у вас есть даты со значениями времени, и вы хотите извлечь только часть даты, вы можете использовать формулу, которая использует функцию ЦЕЛОЕ. Итак, если A1 содержит дату и время, 1 июня 2000 г. 12:00, формула, приведенная ниже, возвращает только часть даты (36678):

= ЦЕЛОЕ(A1)

Временная часть значения (дробная часть) отбрасывается. Вы также можете использовать функцию ОТБР с тем же результатом:

= ОТБР (A1)

Заметки:

1. При использовании любого из приведенных выше методов убедитесь, что вы используете формат даты для результата, который не включает время. В противном случае вы увидите время, отображаемое как 12:00 AM.
2. Для дат и времени (которые должны быть положительными в Excel) нет разницы в использовании ЦЕЛОЕ и ОТБР для извлечения целого числа. Но ЦЕЛОЕ фактически округляет числа до ближайшего целого, что делает разницу, когда значения отрицательные.

Извлечение времени с даты и времени

= ОСТАТ(дата; 1)

Excel обрабатывает даты и время, используя схему, в которой даты — это серийные номера, а время — дробные значения. Например, 1 июня 2000 г. 12:00 PM представлено в Excel как число 36678,5, где 36678 — это дата, а .5 — время.

Если у вас есть даты со значениями времени, и вы хотите извлечь только часть времени (дробную часть), вы можете использовать формулу, которая использует функцию ОСТАТ. Если предположить, что A1 содержит дату и время 1 июня 2000 г. 12:00, формула, приведенная ниже, вернет только часть времени (0,5):

= ОСТАТ(A1;1)

Функция ОСТАТ возвращает остаток от деления. Первый аргумент — это число, а второй — делитель. Вот несколько примеров:

= ОСТАТ(5;2) // возвращает 1

= ОСТАТ(7;5) // возвращает 2

Таким образом, если вы используете ОСТАТ с делителем 1, результат будет дробной частью числа, если таковой имеется, потому что каждое целое число может быть равномерно разделено само по себе. Например:

= ОСТАТ(3.125;1) // возвращает 0.125

Короче говоря, = ОСТАТ(число, 1) возвращает только дробную часть числа, отбрасывая целую часть, поэтому это удобный способ извлечь время из даты и времени.

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

Получить возраст от дня рождения

= ЦЕЛОЕ(ДОЛЯГОДА (дата рождения; СЕГОДНЯ ()))

Если вам необходимо рассчитать возраст человека с даты его рождения, вы можете сделать это с помощью функций ДОЛЯГОДА, ЦЕЛОЕ и СЕГОДНЯ. В общей версии приведенной выше формулы, дата рождения — день рождения человека с года, а СЕГОДНЯ — дата, на которую рассчитывается возраст. Поскольку эта формула использует функцию СЕГОДНЯ, она будет продолжать рассчитывать и правильный возраст в будущем.

В этом примере активная ячейка содержит эту формулу:

= ЦЕЛОЕ(ДОЛЯГОДА(D3; СЕГОДНЯ()))

ДОЛЯГОДА вычисляет десятичное число, представляющее долю года между двумя датами. Чтобы определить долю года в десятичном значении, Excel использует целые дни между двумя датами. Это просто в Excel, потому что все даты — это просто серийные номера.

Таким образом, в этом случае дата рождения предоставляется в качестве даты начала из ячейки D4, а сегодняшняя дата предоставляется в качестве конечной даты, с любезного разрешения функции СЕГОДНЯ.

Результатом ДОЛЯГОДА для Майкла Чанга, здесь в феврале 2016 года, является что-то вроде этого:

14.7333333333333

Затем функция ЦЕЛОЕ берет на себя и округляет это число до целочисленного значения, которое является числом 14. Хотя Майклу почти 15 лет, ему все еще только 14 по годам, так что это правильный результат.

Если вы хотите рассчитать возраст человека на определенную дату, просто замените функцию СЕГОДНЯ на эту дату или ссылку на ячейку на эту дату.

Чтобы проверить день рождения и вернуть «Несовершеннолетний» или «Взрослый», вы можете обернуть формулу возраста в функции ЕСЛИ следующим образом:

= ЕСЛИ(ЦЕЛОЕ(ДОЛЯГОДА(A1; СЕГОДНЯ())) <18; «Несовершеннолетний»; «Взрослый»)

Вы можете заменить 18 любым возрастом, имеющим смысл для вашей ситуации.

Чтобы рассчитать возраст на определенную дату, учитывая дату рождения, вы можете использовать функцию ДАТА вместо функции СЕГОДНЯ следующим образом:

= ЦЕЛОЕ(ДОЛЯГОДА(A1; ДАТА (2016;1;1)))

Где A1 содержит дату рождения.

Получайте дни, часы и минуты между датами

= ЦЕЛОЕ(конец-начало) & «дни» & ТЕКСТ(конец-начало; «ч» «часы» «м» «мин-ы» «»)

Чтобы вычислить и отобразить дни, часы и минуты между двумя датами, вы можете использовать функцию ТЕКСТ с небольшой помощью функции ЦЕЛОЕ.

В показанном примере формула в D5:

= ЦЕЛОЕ(C5-B5) &»дни»&ТЕКСТ(C5-B5; «ч» «часы» «м» «мин-ы» «»)

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

ТЕКСТ (C5-B5; «ч» «часы» «м» «мин-ы» «»)

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

Значение для дней вычисляется с помощью функции ЦЕЛОЕ, которая просто удаляет целую часть конечной даты минус дата начала:

ЦЕЛОЕ(C5-B5)

Хотя вы можете использовать «д» в настраиваемом числовом формате в течение нескольких дней, значение «перевернется» обратно к нулю, когда число дней больше 31.

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

= ЦЕЛОЕ(C5-B5) & «дни» & ТЕКСТ(C5-B5; «ч» «часы» «м» «мин-ы» «с» «сек-ы» «»))

Получить финансовый квартал с даты

= ВЫБОР(МЕСЯЦ (дата); 1;1;1;2;2;2;3;3;3;4;4;4)

Если вы хотите рассчитать финансовый квартал от даты, а финансовый квартал начинается в месяце, отличном от января, вы можете использовать формулу, основанную на функции ВЫБОР.

Примечание: если вам просто нужно вычислить «нормальный» квартал на основе четвертичной системы, которая начинается в январе, вы можете использовать эту более простую формулу.

В показанном примере формула в ячейке D5:

= ВЫБОР(МЕСЯЦ (B5); 4;4;4;1;1;1;2;2;2;3;3;3)

Эта формула возвращает число 1-4, которое соответствует четвертичной системе, которая начинается в апреле и заканчивается в марте.

Функция выбора использует первый аргумент для «выбора» оставшихся элементов. Например, в схеме, где 1 = маленький, 2 = средний и 3 = большой, эта формула «отобразит» число 2 на «среднее».

= ВЫБОР (2; «маленький»; «средний»; «большой»)

В случае фискальных кварталов мы можем использовать эту же идею для сопоставления любого входящего месяца (1-12) с одним из 4 квартальных значений. Нам просто нужно использовать функцию МЕСЯЦ, чтобы получить номер месяца в качестве первого аргумента, а затем предоставить 12 номеров (по одному на каждый месяц года), которые тщательно упорядочены, чтобы отразить желаемый финансовый год:

= ВЫБОР(МЕСЯЦ (B5); 1;1;1;2;2;2;3;3;3;4;4;4) // Начало январь

= ВЫБОР(МЕСЯЦ (B5); 4;4;4;1;1;1;2;2;2;3;3;3) // Начало апрель

= ВЫБОР (МЕСЯЦ (B5); 3;3;3;4;4;4;1;1;1;2;2;2) // Начало июль

Если вы хотите, чтобы номер квартала включал «К» со значением года, вы можете объединить его:

= «К» & ВЫБОР(МЕСЯЦ (дата); 1;1;1;2;2;2;3;3;3;4;4;4) & «-» & ГОД(дата)

Возвращает значения типа: «К1 — 2016», «К2 — 2016» и т. д.

Получить процент завершения года

= ДОЛЯГОДА(ДАТА (ГОД (дата); 1;1); дата)

Если вам необходимо вычислить процент, оставшийся в году, на основе данной даты, вы делаете это с помощью формулы, основанной на функции ДОЛЯГОДА.

В примере формула в C5, которая вычисляет процент оставшегося года, выглядит так:

= ДОЛЯГОДА(ДАТА(ГОД(B5); 1;1); B5)

Функция ДОЛЯГОДА принимает две даты, дату начала и дату окончания и вычисляет время между ними в дробных годах. В этом случае мы используем конечную дату как есть, но генерируем начальную дату, равную первому дню того же года, с этим битом кода:

ДАТА (ГОД (B5); 1;1)

Это извлекает значение года из даты в B5 и вставляет его в функцию ДАТА, а также 1 для месяца и дня.

В результате функция ДОЛЯГОДА возвращает десятичное значение, соответствующее доле года между двумя датами.

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

Чтобы вычислить процент оставшегося года, просто скорректируйте формулу, чтобы вычесть дробный год из 1. В примере D5 содержит эту формулу:

= 1-ДОЛЯГОДА(ДАТА(ГОД(B5); 1;1); B5)

Получить квартал от даты

= ОКРУГЛВВЕРХ (МЕСЯЦ (дата) / 3;0)

Если вы хотите рассчитать квартал (то есть 1,2,3,4) от даты, вы можете использовать функцию ОКРУГЛВВЕРХ вместе с МЕСЯЦ.

В показанном примере формула в ячейке C5:

= ОКРУГЛВВЕРХ(МЕСЯЦ (B5) / 3;0)

В этом случае формула сначала извлекает месяц как число от 1 до 12, а затем делит это число на 3. Затем результат округляется до ближайшего целого числа с помощью функции ОКРУГЛВВЕРХ.

Функция ОКРУГЛВВЕРХ работает так же, как функция ОКРУГЛ, за исключением того, что ОКРУГЛВВЕРХ всегда округляет цифры от 1 до 9 до заданного количества цифр.

Если вы хотите, чтобы номер четверти включал «Ч», вы можете конкатенировать. Формула в D5:

= «Ч» & ОКРУГЛВВЕРХ(МЕСЯЦ (B5) / 3;0)

Результатом является буква «Ч», добавленная к номеру квартала.

Получить ту же дату в следующем месяце

= ДАТАМЕС(дата; 1)

Чтобы получить ту же дату в следующем месяце с определенной даты, вы можете использовать функцию ДАТАМЕС.

В показанном примере формула в ячейке B5:

=ДАТАМЕС (B5;1)

ДАТАМЕС может получить «ту же дату» в будущем или в прошлом, исходя из количества предоставленных месяцев. Когда 1 дается месяцами, ДАТАМЕС получает ту же дату в следующем месяце.

Чтобы получить ту же дату за предыдущий месяц, используйте -1:

= ДАТАМЕС(дата; -1) // предыдущий месяц

Получить ту же дату в следующем году

=ДАТАМЕС (дата; 12)

Чтобы получить ту же дату в следующем году с определенной даты, вы можете использовать функцию ДАТАМЕС.

В показанном примере формула в ячейке B5:

= ДАТАМЕС(B5;12)

ДАТАМЕС может получить «ту же дату» в будущем или в прошлом, исходя из количества предоставленных месяцев. Когда 12 дается на месяцы, ДАТАМЕС получает ту же дату в следующем году.

Чтобы получить ту же дату за предыдущий месяц, используйте -12:

= ДАТАМЕС(дата; -12) // предыдущий год

Получить дату начала в соответствии с установленной датой

= РАБДЕНЬ(цель; дни; праздники)

Чтобы рассчитать требуемую дату начала в соответствии с установленной датой запуска / запуска / завершения, за исключением нерабочих дней, таких как выходные и праздничные дни, вы можете использовать функцию РАБДЕНЬ.

В показанном примере формула в D6:

= РАБДЕНЬ(B6; C6; B9: B11)

Начиная с даты окончания Пт, 06-Янв-2015, функция РАБДЕНЬ отсчитывает назад 10 рабочих дней (исключая выходные и праздничные дни) и возвращает Вт, 18-дек-2014. Это необходимая дата начала активности, для которой требуется 10 рабочих дней.

Функция РАБДЕНЬ принимает три аргумента: дата начала, дни и (необязательно) праздники. Затем он добавляет дни к дате, автоматически исключая выходные и праздничные дни (если они предусмотрены).

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

При отрицательном значении дня РАБДЕНЬ вычитает дни с даты начала, принимая во внимание выходные и праздничные дни.

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

Если в вашем расписании пользовательские выходные дни (то есть что-то отличается от субботы и воскресенья), используйте функцию РАБДЕНЬ.МЕЖД вместо РАБДЕНЬ.

Получить номер недели с даты

= НОМНЕДЕЛИ (дата)

Если вам необходимо получить номер недели со дня с даты, вы можете использовать функцию НОМНЕДЕЛИ. Дата должна быть в формате, который Excel распознает как действительную дату.

Функция НОМНЕДЕЛИ принимает два аргумента, дату и, необязательно, аргумент с именем Тип возвращаемого значения, который управляет схемой, используемой для вычисления номера недели.

В этом примере формула:

= НОМНЕДЕЛИ (B5)

B4 содержит значение даты для 5 января 2016 года. Функция ДЕНЬ возвращает число 2, представляющее число вычисляемой недели.

Обратите внимание, что по умолчанию функция НОМНЕДЕЛИ использует схему, где неделя 1 начинается 1 января, а неделя 2 начинается в следующее воскресенье (когда аргумент Тип возвращаемого значения опущен или поставлен как 1).

При значении Тип возвращаемого значения 2 неделя 1 начинается 1 января, а неделя 2 начинается в следующий понедельник.

С номерами недель в неделю неделя 1 начинается в понедельник первой недели в году с четверга. Это означает, что первый день года для недель ISO — это всегда понедельник в период с 29 января по 4 января.

Начиная с Excel 2010 для Windows и Excel 2011 для Mac, вы можете сгенерировать номер недели ISO, используя 21 как Тип возвращаемого значения:

= НОМНЕДЕЛИ(дата; 21)

В Excel 2013 появилась новая функция НОМНЕДЕЛИ.ISO.