Выбрать даты в диапазоне excel

Диапазон дат в одной ячейке (в текстовом формате) в MS EXCEL

​Смотрите также​GElenka​ то .файлик приложите,где​если в столбце​

​: Формула массива​ решить САМОстоятельно?!.​​Сергей​​vispateresa​Перейдите на вкладку​​ Если день недели​​ об ошибке.​.​ зеленым цветом, а​ которому ячейка будет​

​ пользователь будет вводить​=ЕСЛИ(B19=КОНМЕСЯЦА(B19;0);»Последний день месяца»;ТЕКСТ(B19;»дд»)&»-«&ТЕКСТ(B19+МИН(B20;КОНМЕСЯЦА(B19;0)-B19);»дд.ММ.гггг»))​

​ разным месяцам, то​​Выведем диапазон дат (начальная​: Добрый день!​ #ЗНАЧ​ указанны даты с​FreeWed​Логика-то вам понятна:​

​: не вижу проблем​: Вот на этом​Input Message​ не равен 1​Примечание:​На вкладке​ не красным, т.к.​ выделяться если дата​ данные;​В формуле предполагается, что​

​ у начальной даты​ — конечная дата)​Застряла на формуле​BAG​ числами дней 01.02.2014​: Еще раз Спасибо​

Диапазон с указанием месяца и года

​ если >= И​ меняйте условия по​ примере нужно посчитать​(Сообщение для ввода)​ (воскресенье) и не​Чтобы указать подсказку​Data​ правило (Между =СЕГОДНЯ()-14​ находится в пределах​вызовите инструмент Условное форматирование (Главная/​

​ начальная дата введена​ будет дополнительно выводиться​ в одной ячейке​​ Если.​​: ну вот…​ и так далее​​ всем большое​​V​

Диапазон в пределах 1 месяца

​ датам и условие​ количество да за​ или​ равен 7 (суббота),​ при вводе или​(Данные) нажмите кнопку​ и =СЕГОДНЯ()+14) у​ двух недель от​ Стили/ Условное форматирование/​ в ячейку​ месяц.​ в формате 21-25.10.2012.​Подскажите, пожалуйста, как​

​BAG​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИ(G:G;»01.02.2014″;AE:AE)​Извините за глупое​: вариант​​ да или нет​​ апрель 2015​Error Allert​​ дата допускается (<>​​ текст для оповещения​

excel2.ru

Информирование пользователя MS EXCEL о принадлежности ДАТЫ к определенному диапазону

​Data Validation​ нас идет первым​ сегодняшнего числа, используйте​ Управление правилами). Откроется​B19​

​Более простым случаем является​Пусть задана начальная дата​ задать диапазон дат​: Кажется разобрался, виной​- возвращает результат​

• ​ сообщение, формула Суммпроизв​=СУММПРОИЗВ(($A$2:$A$12=$J13)*($C$2:$C$12=K$12)*$D$2:$D$12)​
• ​если что то​Сергей​(Сообщение об ошибке),​ означает не равно).​
• ​ об ошибке, перейдите​(Проверка данных).​ и имеет наивысший​ формулы =СЕГОДНЯ()-14 и​

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

• ​ спец символы #​ только для указанной​ реально самая простая​
• ​если случайно подойдет​ не то тогда​: =СЧЁТЕСЛИМН(C[-2];»нет»;C[-3];»>=»&—«01.04.2015»;C[-3];»8 прально​ чтобы прописать подсказку,​ Другими словами, понедельники,​
• ​ на вкладку​
• ​Из выпадающего списка​ приоритет. Для правильного​
• ​ =СЕГОДНЯ()+14.​ условного форматирования;​
• ​ в ячейку​
• ​ с указанием месяца​B7​

• ​ ЕСЛИ в нижней​китин​ даты, а постановочные​

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

• ​Input Message​
• ​Allow​ отображения поменяем порядок​
• ​ВНИМАНИЕ!​нажмите Создать правило;​
• ​B20​ и года (что​
• ​) и длительность периода​ таблице, что бы​

​: ога.да еще у​ символы * ?​Михаил С.​ эта формула не​ есть что хотите​: спасибо огромное! Вы​ или текст для​ и пятницы допустимы.​(Сообщение для ввода)​

​(Тип данных) выберите​
​ критериев, используя соответствующие​Когда к диапазону​выберите Форматировать только ячейки,​.​ позволяет корректно учесть​ (ячейка​ в расчете использовалась​ вас формула стоит​ не работают​: Зато и самая​ подходит.​ с указанием вручную​ очень помогли​ оповещения об ошибке.​ Воскресенья и субботы​ или​ пункт​ стрелочки.​ ячеек применяются два​ которые содержат;​Используем Условное форматирование для​ случай, когда даты​

​B8​ цена 1 или​ в том же​китин​ тормозная. Если такая​Nic70y​ результата вычисления формул​vispateresa​Урок подготовлен для Вас​ – нет. Поскольку,​Error Allert​Date​В результате получим вот​ или более правил​в выпадающем списке выберите​

​ подачи сигнала пользователю​ принадлежат разным годам).​

​), выведем диапазон дат​
​ 2 периода соответственно.​ столбце,где и считает.о​: попробуйте так​ формула всего одна​

• ​: Формула массива​МВТ​: не углядела один​ командой сайта office-guru.ru​ прежде чем открыть​
• ​(Сообщение об ошибке).​(Дата).​
• ​ такую картину.​ Условного форматирования, приоритет​ Равно;​

excel2.ru

Как отбросить недопустимые даты в Excel

• ​ MS EXCEL о принадлежности​
• ​ В этом случае​

​ в одной ячейке​_Boroda_​ циклической ссылке не​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((МЕСЯЦ(G:G)=2)*AE:AE)​

1. ​ — то не​​=МИН(ЕСЛИ(K$12=$C$2:$C$12;ЕСЛИ($J13>=$A$2:$A$12;ЕСЛИ($J13 В Excel​​: Мне кажется, проще​
2. ​ момент ((( -​​Источник: http://www.excel-easy.com/examples/reject-invalid-dates.html​​ окно проверки данных,​​Из выпадающего списка​​Из выпадающего списка​

Вне диапазона дат

1. ​СОВЕТ:​​ обработки определяется порядком​​введите формулу =СЕГОДНЯ();​ даты к определенному​​ нужно использовать формулу​​ в формате 21-25.10.2012​
2. ​: Так нужно?​​ предупреждал Эксель?​​BAG​​ страшно. Если их​​ новее 2003 можно​
3. ​ сводной таблицей сделать​ задача должна решаться​Перевел: Антон Андронов​ мы выделили диапазон​​Allow​​Data​​Чтобы найти все​​ их перечисления в​нажав кнопку Формат выберите,​ диапазону.​ =ТЕКСТ(B13;»дд.ММ.гггг»)&»-«&ТЕКСТ(B13+B14;»дд.ММ.гггг»)​ (см. файл примера).​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=H3*ЕСЛИ((H13>=$D14)*(H13<>=$F14)*(H13​
4. ​BAG​​: не выходит -​​ под сотню, и​ попробовать СУММЕСЛИМН​

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

​Автор: Антон Андронов​​A2:A4​(Тип данных) выберите​(Значение) выберите пункт​ ячейки на листе,​ Диспетчере правил условного​​ например, красный шрифт;​​Предположим, что пользователь вводит​В формуле предполагается, что​​Это можно сделать с​​Уж коли нужно​

Воскресенья и субботы

1. ​: тоталитарная лажа, в​​ ошибочка #ЗНАЧ!​​ диапазоны большие -​FreeWed​​ по годам и​​ 2015 года, а​
2. ​vispateresa​​, Excel автоматически вставил​​ пункт​​Between​ к которым применены​ форматирования. Правило, расположенное​​нажмите ОК и вернитесь​​ некие даты событий.​

   ​ начальная дата введена​
​ помощью формулы =ТЕКСТ(B7;"дд")&"-"&ТЕКСТ(B7+B8;"дд.ММ.гггг")​

   

   ​GElenka​​ оригинале файла какой​​jakim​​ то почувствуете.​: Спасибо большое за​ месяцам (см. в​ также для предыдущих​: Используется формула СЧЁТЕСЛИМН​ формулу во все​Custom​(Между).​ правила Условного форматирования необходимо:​ в списке выше,​ в Диспетчер правил​ Требуется, чтобы EXCEL​ в ячейку​Совет​:​ то глюк, буду​:​FreeWed​​ помощь,​​ примере)​ и последующих​ с двумя условиями.​

3. ​ ячейки этого диапазона.​(Другой).​​Введите начальную и конечную​​на вкладке Главная в​​ имеет более высокий​​ условного форматирования;​​ автоматически выделял ячейки​B13​: О пользовательском формате​
4. ​ура, все работает!​ разбираться.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT((MONTH(A:A)=2)*B:B)​​:​​Я Самостоятельно пробовал,​

​FreeWed​Сергей​

​По одному из​​Чтобы проверить это, выделите​​В поле​​ дату, как показано​ группе Редактирование щелкните​​ приоритет, чем правило,​​Теперь создадим правило, по​ следующим образом:​, а длительность периода введена​ дат можно прочитать​Спасибо большое​

​Получается так что​китин​
​Михаил С.,​
​ давно уже ничего​

​: Добрый день, Уважаемые​

office-guru.ru

Как в условие формулы подставить диапазон дат?

​: в моем примере​​ условий необходимо из​ ячейку​
​F​ на рисунке ниже,​ стрелку рядом с​ расположенное в списке​ которому ячейка будет​красным, если дата совпадает​ в ячейку​ в статье Пользовательский​
​reventon9​ формулы нельзя расположить​: не верю.©​

​Михаил подскажите пожалуйста,​​ не делал в​Прошу помощи в​ последняя формула считает​ столбца с конкретными​A3​

​ormula​​ и нажмите​ командой Найти и​ ниже. Новые правила​ выделяться, если дата​

​ с сегодняшним днем;​​B14​

​ формат ДАТЫ и​​: Описание внутри​ в одном столбце​

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

​ОК​​ выделить;​ всегда добавляются в​ находится в пределах​оранжевым, если вводимая дата​
​.​ ВРЕМЕНИ в MS​

​По сути вопрос​​ с расчетными цифрами,​: Опоздал. BAG проверьте​ формулы, которую вы​ отказывается мозг работать.​ Есть таблица в​ от года​
​ относящиеся к определенному​Data Validation​ показанную ниже, и​.​

​выберите в списке пункт​​ начало списка и​ 1 недели от​ находится в пределах​Иногда требуется, чтобы начальная​
​ EXCEL​ сводится к поиску​даты должны начинаться​ формат столбца G.​ написали, я раньше​ Тоже думал про​

​ которой указаны в​​пс не задавайте​ месяцу и году.​(Проверка данных).​ нажмите​Пояснение:​ Условное форматирование;​

planetaexcel.ru

Выбор значение из диапазона между датами

​ поэтому обладают более​​ сегодняшнего числа:​
​ 1 недели от​ и конечная даты​Это решение, однако, не​ определенной даты в​ с самого верха​BAG​ такого ПОИСКПОЗ($J13;ЕСЛИ($C$2:$C$12=K$12;$A$2:$A$12))) не​ Суммпроизв — но​ каком промежутке действовал​ диапазон полность столбцом​ Например, февраль 2014​Как видно на рисунке,​
​ОК​

​Даты между 20​​будут выделены все ячейки,​ высоким приоритетом, однако​нажмите Создать правило;​
​ сегодняшнего числа;​ принадлежали одному месяцу.​

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

​vispateresa​​ или апрель 2015.​
​ эта ячейка тоже​.​ мая 2013 года​

​ которым применены правила​​ порядок правил можно​выберите Форматировать только ячейки,​
​зеленым, если вводимая дата​ В этом случае​ и конечная дата​ в этом диапазоне​ не должны содержать​ столбец G как​ формула ЕСЛИ в​ и не дописал.​ заполнить значения в​
​: Столбец дат может​Как задать в​ содержит формулу.​=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)​ и сегодняшней датой​ Условного форматирования.​ изменить в диалоговом​ которые содержат;​

​ находится в пределах​​ в формуле необходимо​

​ могут принадлежать разным​​ соответствующих значений.​ ни чего кроме​
​ даты , но​ ПОИСКПОЗ.​Спасибо большое за​ желтых ячейках, то​

​ быть абсолютно в​​ формуле такой диапазон​Введите дату 24 августа​=И(ДЕНЬНЕД(A2)<>1;ДЕНЬНЕД(A2)<>7)​ + 5 дней​Вне диапазона дат​ окне при помощи​в выпадающем списке выберите​ 2 недель от​

​ провести проверку: не​​ месяцам. В этом​​Заранее спасибо​
​ цифр,​ результат тот же.​Считает правильно, а​ помощь, на данном​ есть в какой​ разнобой. Нужна формула,​ дат?​ 2013 (суббота) в​
​Пояснение:​ допустимы. Даты вне​

​Воскресенья и субботы​​ кнопок со стрелками​

planetaexcel.ru

как в формуле указать диапазон дат для конкретного месяца? (Формулы)

​ Между;​​ сегодняшнего числа.​ выходит ли конечная​ случае формула может​
​китин​иначе ошибка формулы​что может влиять​ понять не могу!​
​ форуме уже давно,​​ диапазон попадает дата,​ которая будет считать​Сергей​ ячейку​Функция​

​ этого диапазона недопустимы.​​Этот пример объясняет, как​​ Вверх и Вниз.​

​введите формулы =СЕГОДНЯ()-7 и​​Сначала создадим правило, по​ дата за границы​

​ вернуть, например 21-05.10.2012,​​: И вам здравствуйте​​ .​

​ на это, объединение​​Михаил С.​

​ но из за​​ тот тариф и​ количество для конкретного​

​: если б показали​​A2​WEEKDAY​Введите в ячейку​ использовать проверку данных,​
​Если мы расположим правила​ =СЕГОДНЯ()+7;​ которому ячейка будет​ месяца. Если конечная​ что довольно сложно​

​ .чисто по вашему​​От этого страдает​ ячеек в шапке,​: Пояснения в файле​

​ смены платформы мой​​ необходимо брать.​

​ месяца конкретного года.​​ пример можно было​.​

​(ДЕНЬНЕД) возвращает число​​А2​ чтобы отбросить недопустимые​ как показано на​нажав кнопку Формат выберите,​ выделяться, если дата​ дата выходит за​

​ для понимания. Поэтому,​​ файлу примерно так​ шапка файла с​ группировка строк или​BAG​
​ логин отказался работать,​Заранее всем спасибо.​Например, количество «нет»​ сказать точно а​
​Результат: Excel выдаёт сообщение​ от 1 (воскресенье)​дату – 19​ даты.​ предыдущем рисунке, то​ например, оранжевый шрифт;​
​ совпадает с сегодняшним​ границы месяца, то​
​ изменим формулу: =ТЕКСТ(B7;ЕСЛИ(МЕСЯЦ(B7)-МЕСЯЦ(B7+B8);»дд.ММ»;»дд»))&»-«&ТЕКСТ(B7+B8;»дд.ММ.гггг»)​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(И($AL$3<>=$G$2:$AK$2);$E$3/(($AM$3-$AL$3)+1);»»)​ описанием столбцов, досадно,​ фильтрация !?​
​: Подскажите как указать​

excelworld.ru

Как задать диапазон дат формулой ЕСЛИ (Формулы/Formulas)

​ пришлось еще раз​​Z​
​ для апреля 2015​ так пишите в​
​ об ошибке.​ до 7 (суббота),​ мая 2013 года.​Выделите диапазон​ при вводе сегодняшней​Проделайте аналогичные шаги для​ днем (см.Файл примера):​ она заменяется последней​

​Теперь. если начальная и​​reventon9​
​ но ладно.​
​китин​

​ диапазон дат для​​ регистрироваться.​​: И ни одной​
​ или количество "да"​

Формула поиска даты в диапазоне (Формулы/Formulas)

​ условиях >=дата​​Примечание:​
​ представляющее день недели.​Результат: Excel выдаёт сообщение​A2:A4​ даты она выделится​ создания правила, по​выделите диапазон, в который​
​ датой месяца.​

​ конечная даты принадлежат​​: Спасибо за оперативность!!!​Спасибо за помощь​: а чё гадать​​ конкретного месяца 02.2014,​

​Михаил С.​​ вашей попытки ее​

excelworld.ru

​ для марта 2014​

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

Поиск даты в диапазоне дат Excel

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

1. Выделите диапазон ячеек B2:B10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».



2. Выберите: «Использовать формулу для определения форматируемых ячеек».



3. Чтобы выбрать дни вторников в Excel для поля ввода введите формулу: =ДЕНЬНЕД(B2;2)=2 и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.

Результат выделения цветом каждого вторника в списке дат поставок товаров:

В формуле мы использовали функцию =ДЕНЬНЕД(), которая возвращает номер дня недели для исходной даты. В первом аргументе функции указываем ссылку на исходную дату. Ссылка относительная так как будет проверятся каждая дата в столбце B. Во втором аргументе обязательно следует указать число 2, чтобы функция правильно выдавала нам очередности дней недели (1-понедельник, 2-вторник и т.д.). Если не указать второй аргумент, то функция будет считать дни в английском формате: 1-sanday (неделя), 2-Monday (понедельник) и т.д.

Дальше идет оператор сравнения с числом 2. Если возвращаемое число функцией = 2 значит формула возвращает значение ИСТИНА и к текущей ячейке применяется пользовательский формат (красная заливка).



Выделения дат цветом только текущего месяца

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

Нам необходимо из целой таблицы выбрать дату в Excel на текущий месяц:

1. Выделите диапазон ячеек B2:B11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».



2. Снова выбираем: «Использовать формулу для определения форматируемых ячеек».



3. В поле ввода введите формулу: =МЕСЯЦ(B2)=МЕСЯЦ($C$2) и нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. Нажмите ОК на всех открытых окнах.

В результате визуально отчетливо видно дни текущего месяца:

В представленной формуле для условного форматирования главную роль играет функция =МЕСЯЦ(), которая определяет номер месяца исходной даты. Ссылка на ячейку в аргументе первого раза использования функции МЕСЯЦ – относительная, так как проверятся будут несколько ячеек в столбце C. После функции стоит оператор сравнения с номером текущего месяца. Там уже используется абсолютная ссылка (значение аргумента неизменяемое). Если результаты вычислений по обе стороны оператора сравнения совпадают, тогда формула возвращает значение ИСТИНА и к текущей ячейке присваивается пользовательский формат (заливка зеленым цветом).

Теперь усложним задачу. Допустим у нас список проектов охватывает не полугодие, а несколько лет:

Тогда возникает сложность ведь в разные года номера месяцев будут совпадать и условное форматирование будет выделять лишние даты. Поэтому необходимо модернизировать формулу:

Готово:

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

1. =ГОД() – функция работает по тому же принципу что и =МЕСЯЦ(). Разница только в том, что возвращает из даты номер года.
2. =И() – данное имя функции говорит само за себя. То есть должны совпадать и номера месяцев и их номера годов.

Вся формула достаточно просто читается и ее легко использовать для решения похожих задач с помощью условного форматирования.

Читайте так же: как посчитать разницу между датами в днях.

Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье

.

Пусть в диапазоне

A4:A12

имеется список дат. Будем в нем искать дату из ячейки

С4

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

Искомая дата необязательно должна совпадать с какой-нибудь датой или даже находиться в диапазоне поиска (см.

Файл примера

):

Последние 5 решений реализованы с использованием

формул массива

. Для пошагового просмотра хода вычислений используйте клавишу

F9

.

Как видно из таблицы, применение функции

ВПР()

со значением аргумента

интервальный_просмотр

равным ИСТИНА, имеет недостатки. Во-первых, найденное значение м.б. далеко не ближайшим (задав в качестве критерия дату 06.02.2009 получим не ближайшую дату 07.02.2009, а наибольшее значение, которое меньше, чем искомое значение, т.е. 05.01.2009). Во-вторых, если искомая дата меньше, чем минимальное значение из диапазона, то будет возвращена ошибка #Н/Д. В-третьих, требуется

сортировка

списка, что не всегда удобно.

Хорошим решением является

формула массива

=ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ABS(A4:A12-C4));ABS(A4:A12-C4);0))

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

формулой массива

.

Совет

: т.к. дата в MS EXCEL хранится в числовом виде (см. статью

Как Excel хранит дату и время

), то формулы для поиска ближайшего числа также будут работать для дат (см. раздел

Ближайшее ЧИСЛО

).