Вычисление нужной даты

4402 24.10.2012 Скачать пример

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

  • первый понедельник января 2007 года - самый тяжелый понедельник года
  • второе воскресенье апреля 2011 года - день ПВО
  • первое воскресенье октября 2012 года - день Учителя
  • и т.д.

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

need_date2.png

=ДАТА(B1;B2;B4*7-6)+ОСТАТ(B3-ДАТА(B1;B2;);7)

в англоязычной версии это будет

=DATE(B1;B2;B4*7-6)+MOD(B3-DATE(B1;B2;);7)

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

  • B1 - год (число)
  • B2 - номер месяца (число)
  • B3 - номер дня недели (Пн=1, Вт=2 и т.д.)
  • B4 - порядковый номер дня недели, который вам нужен 

За существенное упрощение и улучшение формулы большое спасибо уважаемому МСН с нашего Форума.

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


Михаил Ч.
24.10.2012 20:19:23
формулу можно сократить:
=ДАТА(B1;B2;)+ОСТАТ(B3-ДЕНЬНЕД(ДАТА(B1;B2;1);2);7)+B4*7-6
Ссылка
Николай Павлов
24.10.2012 20:21:41
Класс! Спасибо! Нет предела совершенству.
Родитель Ссылка
Татьяна
24.10.2012 20:20:29
Здравствуйте,Николай, использовала Ваш пример в 7-ой версии,ввела формулу , которую Вы предложили, однако в ячейке b5 у меня получилось 40675,почему?.
Ссылка
Николай Павлов
24.10.2012 20:22:03
Татьяна, поменяйте формат этой ячейки на дату - вы видите код даты, т.к. формат общий или числовой, скорее всего.
Родитель Ссылка
Муса
24.10.2012 20:21:03
Большое спасибо создателям!
Очень хороший ресурс..
Ссылка
MCH
22.04.2013 02:04:03
=ДАТА(B1;B2;B4*7-6)+ОСТАТ(B3-ДАТА(B1;B2;);7)
Ссылка
Николай Павлов
31.05.2013 20:04:59
Класс, спасибо! Век живи, век учись!
Родитель Ссылка
Елена Ленусик
29.08.2013 16:42:56
Добрый день!
Бегу к Вам за помощью.
Вопрос:
наимен.дата развозасумма
молокоср,сб
сырвскр.


Мне нужно,чтобы в колонке дата развоза подставлялась определенная дата, в чем суть, молоко развозят по ср и сб, а сыр по вскр.,т.е. когда открываю эксель,нужно чтобы автоматически подставлялась ближайшая дата развоза,например сегодня 29.08, а молоко привезут 31.08 и сыр 01.09,т.е. эти даты уже автоматом становятся,а когда эти даты пройдут,то подставляются следущие ближайшие.Как это сделать?
Ссылка
Жек Анатолий
10.09.2013 12:07:53
Добрый день, подскажите пожалуйста в ячейках дата с временем, требутется для вычисления без времени. Есть 23.08.2013  23:25:12, а нужно 23.08.2013
Ссылка
Николай Павлов
14.05.2014 14:52:02
Использовать =ЦЕЛОЕ(А1), где А1 - ячейка с датой и временем. Посмотрите вот эту статью - сразу станет понятно, как Excel на самом деле работает с датами и временем.
Родитель Ссылка
Сергей С
08.08.2014 09:13:48
Николай, добрый день! помогите пожалуйста решить следующую задачу. в работе на практике зачастую приходится вычислить дату приходящуюся на 30 рабочий день. Например дата отсчета 15.07.2014 необходимо вычислить дату приходящуюся на 30 рабочий день с учетом праздников и выходных суббота и воскресенье. Спасибо.
Ссылка
Serik Seitkazin
13.09.2014 11:09:53
Добрый день!
каким образом можно обнулить дату, например
15.08.2014 15.55 на 15.08.2014 00.00
Заранее благодарю!
Ссылка
Сергей Сбродов
21.09.2014 16:23:45
Выделяем нужный диапазон и жмем Ctrl+H
Найти: * (пробел и звездочка)
Заменить все
Родитель Ссылка
Николай Павлов
22.09.2014 23:39:43
См.выше мой ответ на вопрос Анатолия.
Родитель Ссылка
Сергей Сбродов
23.09.2014 09:56:52
есть ли возможность в дате со временем (одна ячейка) обнулить только секунды?
необходимо для суммирования по критерию даты.
секунды мешают :)
=ЦЕЛОЕ(А1) не подойдет.
решил
=ВРЕМЗНАЧ(СЦЕПИТЬ(ЧАС(A1);":";МИНУТЫ(A1)))
Родитель Ссылка
Миха Миха
30.03.2015 08:47:40
Добрый день. Формула хороша. Вот только не понимаю вторую часть. ОСТАТ(B3-ДАТА(B1;B2;);7). Получается мы из 6 вычисляем 31, т.к. 5-й месяц 2013 - май, там 31 день, получается -25, и дальше -25 делим на 7. НО, получается в остатке 4, т.к. 3*7=21. 25-21=4. Как так получается, что в этой части формулы - 3?
Ссылка
Scorpion S
23.06.2015 18:14:32
Миха Миха, а причем здесь "31"?
Значение выражения ДАТА(B1;B2;) равно 30.04.2013 или 41394 если в числовом значении.
Но мне все же не понятен алгоритм вычисления Excel' ем этой части:
6 - 41394 = - 41388, а -41388 / 7 = -5912,5714
Откуда Excel берет 3?
Ссылка
Александр
27.08.2015 08:48:44
Добрый день!
Нужна помощь специалистов.
Имеем дату начала события вида 21.08.2015  11:42:00.
Нужно вычислить дату и время, которое наступит ровно через 18 рабочих часов. За рабочие часы принимаем длительность рабочего дня включая перерыв на обед, т.е. 9 часов. Субботу и воскресенье не считаем.
Рабочий день начинается в 09:00, заканчивается в 18:00.
Буду очень признателен за помощь.
Ссылка
Lenochka
14.02.2017 08:41:29
Добрый день! Поясните пожалуйста, почему у функции Дата только 2 аргумента. В справке Excel указано, что 3 аргумента обязательны.
Ссылка
Нина Фомина
14.04.2017 15:38:45
Спасибо большое, мне очень помогла эта статья!
Ссылка
Евгений
19.09.2022 10:57:21
Добрый день!
Как преобразовать номер недели + день недели= число даты месяца
39 неделя 1 день недели = 18
18.09.22
Ссылка
Павел \Ʌ/
10.01.2023 10:04:07
=ДАТА(B1;1;B2+B3×7-3)-ДЕНЬНЕД(ДАТА(B1;1;3))
B1 - год
B2 - день недели
B3 - № недели ИСО
Родитель Ссылка
Павел \Ʌ/
09.01.2023 17:03:05
=ДАТА(B1;B2;B4*7)-ОСТАТ(ДАТА(B1;B2;6-B3);7)
На один символ короче
Ссылка
Павел \Ʌ/
21.01.2023 04:00:07
Есть еще идея...
Ссылка
Павел \Ʌ/
22.01.2023 06:33:06
Держите:
=ОКРВВЕРХ(ДАТА(B1;B2;B4*7)-B3;7)+B3-6
Ссылка
Павел \Ʌ/
24.01.2023 10:26:17
Вывел универсальную формулу:
=ОКРВВЕРХ(ДАТА(B1;B2;B4*7-6)-D1-B3;7)+D1+B3
где D1 - начало недели (0 - с воскресенья, 1 - с понедельника,... 6 - с субботы)
Альтернатива:
=ОКРВНИЗ(ДАТА(B1;B2;B4*7)-D1-B3;7)+D1+B3
Ссылка
Наверх