Excel: номер недели в месяце с критериями

Я пытаюсь сделать формулу для расчета номера недели в течение недели, но с определенными критериями. Неделя всегда начинается в понедельник. Но если четверг этой недели приходится на следующий месяц, то этот номер недели является частью следующего месяца.

введите описание изображения здесь[введите описание изображения здесь][2]

Сейчас у меня есть следующая формула:

=INTEGER((6+DAY(A4+1-WEEKDAY(A4-1)))/7) where A4 is the date.

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

Заранее спасибо!


excel excel-formula week-number
person Innerv1sion    schedule 24.08.2017    source источник
comment
Предоставьте снимок экрана или образец данных со сценарием, соответствующим критериям вашего вопроса. Введите желаемый результат вручную. Теперь, как новый пользователь здесь, пожалуйста, убедитесь, что вы понимаете это: отредактируйте свой вопрос, чтобы предоставить запрашиваемые детали. Не используйте для этого комментарии. После того, как вы отредактировали свой вопрос, опубликуйте комментарий и используйте @teylyn, чтобы предупредить меня о ваших изменениях.   -  person teylyn    schedule 24.08.2017
comment
@teylyn Я добавил скриншот образца Excel. Спасибо.   -  person Innerv1sion    schedule 24.08.2017
comment
Я думаю, что функция EOMONTH может вам помочь. К сожалению, я не могу помочь вам больше atm.   -  person Egan Wolf    schedule 24.08.2017

Ответы (3)


arrow_upward
1
arrow_downward

Рассмотрим вычисление номера недели по номеру предыдущей недели:

  • If it is Monday then
    • If following Thursday goes into the next month, reset to 1
    • В противном случае увеличьте на 1
  • В противном случае используйте то же значение

Итак, начиная с 1 в E4

=IF(WEEKDAY(A5)=2,IF(MONTH(A5+3)>MONTH(A5-4),1,E4+1),E4)

введен в E5 и скопирован вниз.

введите здесь описание изображения

ИЗМЕНИТЬ

Выше работает для 2017 года, но для работы в 2018 году и далее потребуются небольшие изменения, потому что месяц уменьшается с 12 до 1 через границу года:

=IF(WEEKDAY(A5)=2,IF(MONTH(A5+3)<>MONTH(A5-4),1,E4+1),E4)
person Tom Sharpe    schedule 25.08.2017
comment
Я пришел к аналогичному выводу @TomSharpe, но я никогда не выходил на сцену, если дата начиналась в середине месяца. Данные примера OP не охватывают этот случай, и OP оставил первую дату пустой, что, как я предполагаю, должно быть действительно 4. Хороший ответ, хотя и дает именно тот результат, который запросил OP. - person Forward Ed; 25.08.2017
comment
Спасибо @Forward Ed, так проще. Такое ощущение, что в противном случае вам пришлось бы работать в обратном направлении. Но, честно говоря, я так и не понял, как оригинальная формула OP оказалась так близко к решению, может быть, мне следует сделать это дальше. - person Tom Sharpe; 25.08.2017
comment
В конце концов я придумал решение для определения номера недели по дате. Идея заключалась в том, чтобы определить «отчетный месяц» для каждого календарного месяца, например. для января 2017 года он будет идти со 2-го понедельника по 29-е воскресенье, тогда не так уж сложно получить номер недели, но для этого потребуется несколько вспомогательных столбцов. - person Tom Sharpe; 29.08.2017
comment
Я думал, что это будет немного сложнее, но мне всегда нравится хороший вызов. - person Forward Ed; 29.08.2017
comment
Большое спасибо за это простое решение. Тем временем я смог решить эту проблему, используя различные вспомогательные столбцы, но это все еще не было надежным. Когда я изменил год на 2018, были некоторые неправильные значения. Первоначальная формула была основана на формуле, опубликованной во вступительном посте. Но методом проб и ошибок изменил его на: =INTEGER((1+DAY(B4+3-WEEKDAY(B4-1)))/7)+1 - person Innerv1sion; 30.08.2017
comment
Без проблем. Моя формула работает для 2017 года, но для работы в 2018 году и далее потребуются небольшие изменения — я добавлю их в пост. - person Tom Sharpe; 31.08.2017

arrow_upward
0
arrow_downward

Я не могу загрузить книгу, но вот решение:

Строка 2 — заголовки. Строка 3 — пустая. B4 — дата (с 01.01.2017 по 12.01.2017).

C4 =WEEKDAY(B4,2)

D4 =VLOOKUP($C4,$L$3:$M$9,2,0)

E4 =MONTH(B4)

F4 до J4 пусты

F5 =IF(C5=1,F4+1,F4)

G5 =IF(C5=4,11,0)

H5 =IF(MAX(E5:E11)-MIN(E5:E11)<>0,22,0)

I5 =IF(AND(C5=4,SUM(G5:G11)+SUM(H5:H11)>22),I4+1,0)

J5 =IF(H6-H5<0,1,IF(H6=22,J5,IF(OR(C6=1,I6<>0),J5+1,J5)))

L3 - M9 - это vlookups

1   Monday
2   Tuesday
3   Wednesday
4   Thursday
5   Friday
6   Saturday
7   Sunday

Это работает нормально для вас?

person Tejkaran Samra    schedule 24.08.2017
comment
Благодарю вас! Я думаю, что понимаю ваше решение, но последняя функция в J5 кажется странной, потому что у нее есть циклическая ссылка? Как это работает тогда? - person Innerv1sion; 24.08.2017
comment
Я не уверен, почему будет круговая ссылка. Ты правильно скопировал мой код? - person Tejkaran Samra; 25.08.2017
comment
Ваша функция в ячейке J5 содержит J5, поэтому она ссылается на себя? J5 =ЕСЛИ(H6-H5‹0,1,ЕСЛИ(H6=22,J5,ЕСЛИ(ИЛИ(C6=1,I6‹›0),J5+1,J5))) - person Innerv1sion; 25.08.2017
comment
А, поменяй на J4. Если это не сработает, я проверю это завтра как следует - person Tejkaran Samra; 26.08.2017

arrow_upward
0
arrow_downward

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

=INT((6+DAY(B4+4-WEEKDAY(B4-1)))/7)

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

=INT((6+DAY(B4+3.75-WEEKDAY(B4-1.25)))/7)

введите здесь описание изображения

person barry houdini    schedule 08.09.2017