Выводить последнюю дату в excel

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

​Смотрите также​ikki​yavorsky90​

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

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

​ДЕНЬ;​

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

excel2.ru

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

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

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

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

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

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

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

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

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

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

excel2.ru

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

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

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

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

​А2​

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

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

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

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

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

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

excel2.ru

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

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

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

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

excelworld.ru

Примеры функций для работы с датами: ГОД, МЕСЯЦ и ДЕНЬ в 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, то​ КОНМЕСЯЦА().​ звучать примерно так:​

exceltable.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

planetaexcel.ru

​ день месяца.​

Поиск последнего вхождения (инвертированный ВПР)

Все классические функции поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH) и им подобные имеют одну важную особенность — они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение — поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.

Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний платёж, самую свежую заявку и т.д.?

Способ 1. Поиск последней строки формулой массива

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

Здесь:

• Функция ЕСЛИ (IF) проверяет по очереди все ячейки в столбце Клиент и выводит номер строки, если в ней лежит нужное нам имя. Номер строки на листе нам даёт функция СТРОКА (ROW), но поскольку нам нужен номер строки в таблице, то дополнительно приходится вычитать 1, т.к. у нас в таблице есть шапка.
• Затем функция МАКС (MAX) выбирает из сформированного набора номеров строк максимальное значение, т.е. номер самой последней строки клиента.
• Функция ИНДЕКС (INDEX) выдаёт содержимое ячейки с найденным последним номером из любого другого требуемого столбца таблицы (Код заказа).

Всё это нужно вводить как формулу массива, т.е.:

• В Office 365 с последними установленными обновлениями и поддержкой динамических массивов — можно просто жать Enter.
• Во всех остальных версиях после ввода формулы придется нажимать сочетание клавиш Ctrl+Shift+Enter, что автоматически добавит к ней фигурные скобки в строке формул.

Способ 2. Обратный поиск новой функцией ПРОСМОТРХ

Я уже писал большую статью с видео про новую функцию ПРОСМОТРХ (XLOOKUP), которая появилась в последних версиях Office на замену старушке ВПР (VLOOKUP). При помощи ПРОСМОТРХ наша задача решается совершенно элементарно, т.к. для этой функции (в отличие от ВПР) можно явно задавать направление поиска: сверху-вниз или снизу-вверх — за это отвечает её последний аргумент (-1):

Способ 3. Поиск строки с последней датой

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

Как это сделать с помощью классических функций я уже подробно разбирал, а теперь давайте попробуем использовать мощь новых функций динамических массивов. Исходную таблицу для пущей красоты и удобства тоже заранее преобразуем в «умную» с помощью сочетания клавиш Ctrl+T или команды Главная — Форматировать как таблицу (Home — Format as Table).

С их помощью этой «убойной парочки» наша задача решается весьма изящно:

Здесь:

• Сначала функция ФИЛЬТР (FILTER) отбирает только те строки из нашей таблицы, где в столбце Клиент — нужное нам имя.
• Потом функция СОРТ (SORT) сортирует отобранные строки по убыванию даты, чтобы самая последняя сделка оказалась сверху.
• Функция ИНДЕКС (INDEX) извлекает первую строку, т.е. выдает нужную нам последнюю сделку.
• И, наконец, внешняя функция ФИЛЬТР убирает из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант {0;1;0;1}, определяющий какие именно столбцы мы хотим (1) или не хотим (0) выводить.

Способ 4. Поиск последнего совпадения в Power Query

Ну, и для полноты картины, давайте рассмотрим вариант решения нашей задачи обратного поиска с помощью надстройки Power Query. С её помощью всё решается очень быстро и красиво.

1. Преобразуем нашу исходную таблицу в «умную» с помощью сочетания клавиш Ctrl+T или команды Главная — Форматировать как таблицу (Home — Format as Table).

2. Загружаем её в Power Query кнопкой Из таблицы/диапазона на вкладке Данные (Data — From Table/Range).

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

4. На вкладке Преобразование выбираем команду Группировать по (Transform — Group By) и задаем группировку по клиентам, а в качестве агрегирующей функции выбираем вариант Все строки (All rows). Назвать новый столбец можно как угодно — например Подробности.

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

5. Добавляем новый вычисляемый столбец кнопкой Настраиваемый столбец на вкладке Добавить столбец (Add column — Add custom column) и вводим следующую формулу:

Здесь Подробности — это столбец, откуда мы берем таблицы по клиентам, а {0} — это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями (Record), где каждая запись — первая строка из каждой таблицы:

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

… и удалить потом ненужный более столбец Подробности щёлкнув по его заголовку правой кнопкой мыши — Удалить столбцы (Remove columns).

После выгрузки результатов на лист через Главная — Закрыть и загрузить — Закрыть и загрузить в (Home — Close & Load — Close & Load to…) получим вот такую симпатичную таблицу со списком последних сделок, как и хотели:

При изменении исходных данных результаты нужно не забыть обновить, щёлкнув по ним правой кнопкой мыши — команда Обновить (Refresh) или сочетанием клавиш Ctrl+Alt+F5.

Ссылки по теме

• Функция ПРОСМОТРХ — наследник ВПР
• Как использовать новые функции динамических массивов СОРТ, ФИЛЬТР и УНИК
• Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР

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

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

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

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

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

=ДАТА(ГОД(A2);3;0)

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

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

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

=ДАТА(2000;3;0)

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



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

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

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

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

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

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

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

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

=КОНМЕСЯЦА(«01.01.2000»;1)

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

=КОНМЕСЯЦА(«01.03.2000»;-1)

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

=КОНМЕСЯЦА(«01.02.2000»;0)

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

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

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