Вы, наверное, знаете, что можно использовать функцию WEEKNUM, чтобы вернуть номер недели в году для заданной даты. Что, если вы хотите сделать обратное — определить последний день конкретной недели, если все, что у вас есть, — это номер недели в году? Например, если вы укажете 37-ю неделю, вам нужно будет выяснить дату последнего дня этой недели.

Нет встроенной функции для определения желаемой даты, но есть несколько способов подойти к проблеме и выяснить ее. В этих примерах мы предполагаем, что год находится в ячейке A1, а желаемый номер недели — в ячейке B1.

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

=DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))

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

=DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))+1

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

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

=DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))



((6-WEEKDAY(DATE(A1,1,1))<0)7)+1)+((B1-1)7)

Поскольку в некоторых годах 52 недели, а в других — 53 — опять же, в зависимости от того, заканчиваются ли недели в пятницу, субботу или воскресенье — рекомендуется изменить формулы, чтобы они проверяли, соответствует ли возвращаемая дата тому же году что вы анализируете. Если вы не отметите это, то приведенные до сих пор формулы с радостью вернут даты для недели 73, недели 89 или недели 123 любого данного года — они просто изменяют дату на соответствующий будущий год.

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

=IF(YEAR(DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))



((6-WEEKDAY(DATE(A1,1,1))<0)7)+1)+((B1-1)7))=A1, DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))+((6-WEEKDAY(DATE( A1,1,1))<0)7)+1)+((B1-1)7),"")

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

=IF(YEAR(DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1)))=A1, DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1)),"")

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

=IF(YEAR(DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1))+1)=A1, DATE(A1,1,1)+B17-WEEKDAY(DATE(A1,1,1))+1,"")

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (6750) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Calculating_the_Last_Day_in_a_Week_Number [Расчет последнего дня в номере недели].