Диапазон дат в excel
Диапазон дат в одной ячейке (в текстовом формате) в MS EXCEL
Смотрите такжеGElenka то .файлик приложите,гдеесли в столбце
: Формула массива решить САМОстоятельно. СергейvispateresaПерейдите на вкладку Если день недели об ошибке.. зеленым цветом, а которому ячейка будет
пользователь будет вводить=ЕСЛИ(B19=КОНМЕСЯЦА(B19;0);"Последний день месяца";ТЕКСТ(B19;"дд")&"-"&ТЕКСТ(B19+МИН(B20;КОНМЕСЯЦА(B19;0)-B19);"дд.ММ.гггг"))
: не вижу проблем: Вот на этомInput Message не равен 1Примечание:На вкладке не красным, т.к. выделяться если дата данные;В формуле предполагается, что
у начальной даты - конечная дата)Застряла на формулеBAG числами дней 01.02.2014: Еще раз Спасибо
Диапазон с указанием месяца и года
начальная дата введена будет дополнительно выводиться в одной ячейке Если.: ну вот. и так далее всем большоеV
Диапазон в пределах 1 месяца
датам и условие количество да за или равен 7 (суббота), при вводе или(Данные) нажмите кнопку и =СЕГОДНЯ()+14) у двух недель от Стили/ Условное форматирование/ в ячейку месяц. в формате 21-25.10.2012.Подскажите, пожалуйста, как
200?'200px':''+(this.scrollHeight+5)+'px');">=СУММЕСЛИ(G:G;"01.02.2014";AE:AE)Извините за глупое: вариант да или нет апрель 2015Error Allert дата допускается (<> текст для оповещения
Информирование пользователя MS EXCEL о принадлежности ДАТЫ к определенному диапазону
Data Validation нас идет первым сегодняшнего числа, используйте Управление правилами). ОткроетсяB19
Более простым случаем являетсяПусть задана начальная дата задать диапазон дат: Кажется разобрался, виной- возвращает результат
окно Диспетчер правил, а длительность периода введена вывод начальной даты (ячейка с помощью формулы
в данном случае:) два варианта то реальный пример чтоvispateresa всплывающую при вводе вторники, среды, четверги
- Input Message
- Allow отображения поменяем порядок
- ВНИМАНИЕ!нажмите Создать правило;
- B20 и года (что
- ) и длительность периода таблице, что бы
(Тип данных) выберите
критериев, используя соответствующиеКогда к диапазонувыберите Форматировать только ячейки,. позволяет корректно учесть (ячейка в расчете использовалась вас формула стоит не работают: Зато и самая подходит. с указанием вручную очень помогли оповещения об ошибке. Воскресенья и субботы или пункт стрелочки. ячеек применяются два которые содержат;Используем Условное форматирование для случай, когда даты
B8 цена 1 или в том жекитин тормозная. Если такаяNic70y результата вычисления формулvispateresaУрок подготовлен для Вас – нет. Поскольку,Error AllertDateВ результате получим вот или более правилв выпадающем списке выберите
подачи сигнала пользователю принадлежат разным годам).
), выведем диапазон дат
2 периода соответственно. столбце,где и считает.о: попробуйте так формула всего одна
Как отбросить недопустимые даты в Excel
- MS EXCEL о принадлежности
- В этом случае
в одной ячейке_Boroda_ циклической ссылке не200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ((МЕСЯЦ(G:G)=2)*AE:AE)
Вне диапазона дат
- СОВЕТ: обработки определяется порядкомвведите формулу =СЕГОДНЯ(); даты к определенному нужно использовать формулу в формате 21-25.10.2012
- : Так нужно? предупреждал Эксель?BAG страшно. Если их новее 2003 можно
- сводной таблицей сделать задача должна решатьсяПеревел: Антон Андронов мы выделили диапазонAllowDataЧтобы найти все их перечисления внажав кнопку Формат выберите, диапазону. =ТЕКСТ(B13;"дд.ММ.гггг")&"-"&ТЕКСТ(B13+B14;"дд.ММ.гггг") (см. файл примера).200?'200px':''+(this.scrollHeight+5)+'px');">=H3*ЕСЛИ((H13>=$D14)*(H13<>=$F14)*(H13
- BAG: не выходит - под сотню, и попробовать СУММЕСЛИМН
и поставить группировку не только для
Автор: Антон АндроновA2:A4(Тип данных) выберите(Значение) выберите пункт ячейки на листе, Диспетчере правил условного например, красный шрифт;Предположим, что пользователь вводитВ формуле предполагается, чтоЭто можно сделать сУж коли нужно
Воскресенья и субботы
начальная дата введена
помощью формулы =ТЕКСТ(B7;"дд")&"-"&ТЕКСТ(B7+B8;"дд.ММ.гггг")
GElenka оригинале файла какойjakim то почувствуете.: Спасибо большое за месяцам (см. в также для предыдущих: Используется формула СЧЁТЕСЛИМН формулу во всеCustom(Между). правила Условного форматирования необходимо: в списке выше, в Диспетчер правил Требуется, чтобы EXCEL в ячейкуСовет: то глюк, буду:FreeWed помощь, примере) и последующих с двумя условиями.
По одному изЧтобы проверить это, выделитеВ поле дату, как показано группе Редактирование щелкните приоритет, чем правило,Теперь создадим правило, по следующим образом:, а длительность периода введена дат можно прочитатьСпасибо большое
Получается так чтокитин
Михаил С.,
давно уже ничего
Как в условие формулы подставить диапазон дат?
: в моем примере условий необходимо из ячейку
F на рисунке ниже, стрелку рядом с расположенное в списке которому ячейка будеткрасным, если дата совпадает в ячейку в статье Пользовательский
reventon9 формулы нельзя расположить: не верю.©
Михаил подскажите пожалуйста, не делал вПрошу помощи в последняя формула считает столбца с конкретнымиA3
ormula и нажмите командой Найти и ниже. Новые правила выделяться, если дата
с сегодняшним днем;B14
формат ДАТЫ и: Описание внутри в одном столбце
jakim в конструкции той Экселе вот и решении простенькой задачки. апрель не зависимо датами выбирать ячейки,и нажмите кнопку(Формула) пропишите формулу,
ОК выделить; всегда добавляются в находится в пределахоранжевым, если вводимая дата
. ВРЕМЕНИ в MS
По сути вопрос с расчетными цифрами,: Опоздал. BAG проверьте формулы, которую вы отказывается мозг работать. Есть таблица в от года
относящиеся к определенномуData Validation показанную ниже, и.
выберите в списке пункт начало списка и 1 недели от находится в пределахИногда требуется, чтобы начальная
EXCEL сводится к поискудаты должны начинаться формат столбца G. написали, я раньше Тоже думал про
которой указаны впс не задавайте месяцу и году.(Проверка данных). нажмитеПояснение: Условное форматирование;
Выбор значение из диапазона между датами
поэтому обладают более сегодняшнего числа:
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 (суббота) в
Пояснение: допустимы. Даты вне
Воскресенья и субботы кнопок со стрелками
как в формуле указать диапазон дат для конкретного месяца? (Формулы)
Между; сегодняшнего числа. выходит ли конечная случае формула может
китининаче ошибка формулычто может влиять понять не могу!
форуме уже давно, диапазон попадает дата, которая будет считатьСергей ячейкуФункция
этого диапазона недопустимы.Этот пример объясняет, как Вверх и Вниз.
введите формулы =СЕГОДНЯ()-7 иСначала создадим правило, по дата за границы
вернуть, например 21-05.10.2012,: И вам здравствуйте .
на это, объединениеМихаил С.
но из за тот тариф и количество для конкретного
.чисто по вашемуОт этого страдает ячеек в шапке,: Пояснения в файле
смены платформы мой необходимо брать.
месяца конкретного года. пример можно было.
(ДЕНЬНЕД) возвращает числоА2 чтобы отбросить недопустимые как показано нанажав кнопку Формат выберите, выделяться, если дата дата выходит за
Как задать диапазон дат формулой ЕСЛИ (Формулы/Formulas)
пришлось еще разZ
для апреля 2015 так пишите в
об ошибке. до 7 (суббота), мая 2013 года.Выделите диапазон при вводе сегодняшнейПроделайте аналогичные шаги для днем (см.Файл примера): она заменяется последней
Теперь. если начальная иreventon9
но ладно.
китин
диапазон дат для регистрироваться.: И ни одной
или количество "да"
Формула поиска даты в диапазоне (Формулы/Formulas)
конечная даты принадлежат: Спасибо за оперативность. Спасибо за помощь: а чё гадать конкретного месяца 02.2014,
Одна из типичных задач для пользователя Microsoft Excel. Имеем два диапазона дат вида «начало-конец». Задача состоит в том, чтобы определить пересекаются ли эти диапазоны и, если да, то на сколько дней.
Пересекаются или нет?
Начнем с решения вопроса о том, есть ли пересечение интервалов в принципе? Предположим, что у нас есть таблица рабочих смен сотрудников вот такого вида:
Хорошо видно, что рабочие смены Ярослава и Елены пересекаются, но как это вычислить, не прибегая к построению календарного графика и визуальному контролю? Нам поможет функция СУММПРОИЗВ (SUMPRODUCT) .
Вставим в нашу таблицу еще один столбец с формулой, которая выдает логическое значение ИСТИНА в случае пересечения дат:
На сколько дней пересечение?
Если принципиально не просто понимать - пересекаются наши интервалы или нет, а точно знать сколько именно дней попадает в пересечение, то задача усложняется. Рассуждая логически, необходимо "прокачать" аж 3 разных ситуации в одной формуле:
- интервалы не пересекаются
- один из интервалов полностью поглощает другой
- интервалы пересекаются частично
На самом деле все можно сделать красиво с помощью функции МЕДИАНА (MEDIAN) из категории Статистические.
Если условно обозначить начало первого интервала за Н1 , а конец за К1 , и начало второго за Н2 и конец за К2 , то в общем виде наша формула может быть записана как:
=МЕДИАНА( Н1 ;К1+1; К2 +1)-МЕДИАНА( Н1 ;К1+1; Н2 )
Пусть задана начальная дата (ячейка B7 ) и длительность периода (ячейка B8 ), выведем диапазон дат в одной ячейке в формате 21-25.10.2012 (см. файл примера ).
Это можно сделать с помощью формулы = ТЕКСТ(B7;"дд")&"-"&ТЕКСТ(B7+B8;"дд.ММ.гггг")
Совет : О пользовательском формате дат можно прочитать в статье Пользовательский формат ДАТЫ и ВРЕМЕНИ в MS EXCEL
Это решение, однако, не учитывает, что начальная и конечная дата могут принадлежать разным месяцам. В этом случае формула может вернуть, например 21-05.10.2012, что довольно сложно для понимания. Поэтому, изменим формулу: =ТЕКСТ(B7;ЕСЛИ(МЕСЯЦ(B7)-МЕСЯЦ(B7+B8);"дд.ММ";"дд"))&"-"&ТЕКСТ(B7+B8;"дд.ММ.гггг")
Теперь. если начальная и конечная даты принадлежат разным месяцам, то у начальной даты будет дополнительно выводиться месяц.
Диапазон с указанием месяца и года
Более простым случаем является вывод начальной даты с указанием месяца и года (что позволяет корректно учесть случай, когда даты принадлежат разным годам). В этом случае нужно использовать формулу = ТЕКСТ(B13;"дд.ММ.гггг")&"-"&ТЕКСТ(B13+B14;"дд.ММ.гггг")
В формуле предполагается, что начальная дата введена в ячейку B13 , а длительность периода введена в ячейку B14 .
Диапазон в пределах 1 месяца
Иногда требуется, чтобы начальная и конечная даты принадлежали одному месяцу. В этом случае в формуле необходимо провести проверку: не выходит ли конечная дата за границы месяца. Если конечная дата выходит за границы месяца, то она заменяется последней датой месяца.
= ЕСЛИ(B19=КОНМЕСЯЦА(B19;0);"Последний день месяца";ТЕКСТ(B19;"дд")&"-"&ТЕКСТ(B19+МИН(B20;КОНМЕСЯЦА(B19;0)-B19);"дд.ММ.гггг"))
В формуле предполагается, что начальная дата введена в ячейку B19 , а длительность периода введена в ячейку B20 .
Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье Поиск ЧИСЛА ближайшего к заданному .
Пусть в диапазоне A4:A12 имеется список дат. Будем в нем искать дату из ячейки С4 . Если диапазон не содержит искомого значения, то будет возвращено ближайшее значение.
Искомая дата необязательно должна совпадать с какой-нибудь датой или даже находиться в диапазоне поиска (см. Файл примера ):
Решение
Результат поиска
Примечание
если столбец не отсортирован по возрастанию, то результат непредсказуем
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое расположено выше (номер строки меньше))
столбец м.б. не отсортирован
столбец м.б. не отсортирован
= ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ЕСЛИ(A4:A12>=C4;A4:A12;"")); $A$4:$A$12;0);1)
столбец м.б. не отсортирован
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое больше
столбец м.б. не отсортирован
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое меньше
столбец м.б. не отсортирован
Последние 5 решений реализованы с использованием формул массива . Для пошагового просмотра хода вычислений используйте клавишу F9 .
Хорошим решением является формула массива =ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ABS(A4:A12-C4));ABS(A4:A12-C4);0))
свободная от всех указанных недостатков, но имеющая свои: формула относительно сложная и является формулой массива .
Совет : т.к. дата в MS EXCEL хранится в числовом виде (см. статью Как Excel хранит дату и время ), то формулы для поиска ближайшего числа также будут работать для дат (см. раздел Ближайшее ЧИСЛО ).
Т.к. каждому значению даты соответствует определенное число (см. статью Как EXCEL хранит дату и время ), то подходы для формирования последовательностей, изложенные в статье Числовые последовательности , применимы и для дат. Однако, имеются и свои особенности.
Последовательность 01.01.09, 01.02.09, 01.03.09 (первые дни месяцев) можно сформировать формулой =ДАТАМЕС(B2;СТРОКА(A1)) , в ячейке B2 должна находиться дата - первый элемент последовательности ( 01.01.09 ).
Эту же последовательность можно сформировать используя правую клавишу мыши. Пусть в ячейку A2 введено значение 01.01.09 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши, появится контекстное меню, в котором нужно выбрать пункт Заполнить по месяцам .
Изменив формат ячеек, содержащих последовательность 01.01.09, 01.02.09, 01.03.09, на МММ (см. статью Пользовательский формат дат ) получим последовательность янв, фев, мар, .. .
Эту же последовательность можно ввести используя список автозаполения Кнопка Офис/ Параметры Excel/ Основные/ Основные параметры работы с Excel/ Изменить списки (введите янв , затем Маркером заполнения скопируйте вниз).
Ячейки будут содержать не даты, как в предыдущем случае, а текстовые значения.
Аналогичным образом можно сформировать последовательность дней недели пн, вт, ср, .
Последовательность кварталов 1 кв., 2 кв. можно сформировать используя идеи из статьи Текстовые последовательности .
Используя инструмент Прогрессия , можно создать последовательности только рабочих дней. А также с шагом месяц и год.
Последовательность первых месяцев кварталов янв, апр, июл, окт, янв, . можно создать введя в две ячейки первые два элемента последовательности ( янв, апр ), затем (предварительно выделив их) скопировать вниз маркером заполнения . Ячейки будут содержать текстовые значения. Чтобы ячейки содержали даты, используйте формулу =ДАТАМЕС($G$16;(СТРОКА(A2)-СТРОКА($A$1))*3) Предполагается, что последовательность начинается с ячейки G16 , формулу нужно ввести в ячейку G17 (см. файл примера ).
Временную последовательность 09:00, 10:00, 11:00, . можно сформировать используя Маркер заполнения . Пусть в ячейку A2 введено значение 09 : 00 . Выделим ячейку A2 . Скопируем Маркером заполнения , значение из A2 в ячейки ниже. Последовательность будет сформирована.
Если требуется сформировать временную последовательность с шагом 15 минут ( 09:00, 09:15, 09:30, . ), то можно использовать формулу =B15+1/24/60*15 (Предполагается, что последовательность начинается с ячейки B15 , формулу нужно ввести в B16 ). Формула вернет результат в формате даты.
Другая формула =ТЕКСТ(B15+1/24/60*15;"чч:мм") вернет результат в текстовом формате.
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О числовых последовательностях вида 1, 2, 3, . 1, 3, 5, 7, . I, II, III, IV, . можно прочитать в статье Числовые последовательности .
Читайте также: