안녕하세요

오빠두에 올라와 있는 월별 주차 공식 다 보고 하는데도 뭔가 제가 원하는 답이 없달까요?

위와 같이 주차에 표기가 되게 하고 싶은데

①=WEEKNUM(DATE(YEAR(A2),1,DAY(A2-WEEKDAY(A2,1)+1)),WEEKDAY(DATE(YEAR(A2),1,1),11)+10)

②=IF(WEEKDAY(DATE(YEAR(S2),MONTH(S2),1),11)<=5,IF(AND(WEEKDAY(DATE(YEAR(S2),MONTH(S2)+1,1),11)<=5,WEEKNUM(S2,11)=WEEKNUM(DATE(YEAR(S2),MONTH(S2)+1,1),11)),1,WEEKNUM(S2,11)-WEEKNUM(EOMONTH(S2,-1)+1,11)+1),IF(AND(DAY(S2)<8,WEEKNUM(DATE(YEAR(S2),1,DAY(S2-WEEKDAY(S2,11)+1)),WEEKDAY(DATE(YEAR(S2),1,1),11)+10)>2,WEEKDAY(DATE(YEAR(S2),MONTH(S2)-1,1),2)<=5),WEEKNUM(DATE(YEAR(S2),1,DAY(S2-WEEKDAY(S2,11)+1)),WEEKDAY(DATE(YEAR(S2),1,1),11)+10)+1,IF(AND(WEEKDAY(DATE(YEAR(S2),MONTH(S2)+1,1),11)<=5,OR(WEEKNUM(S2,11)=WEEKNUM(DATE(YEAR(S2),MONTH(S2)+1,1),11),WEEKNUM(DATE(YEAR(S2),MONTH(S2),DAY(S2-WEEKDAY(S2,11)+1))+7)<WEEKNUM(DATE(YEAR(S2),MONTH(S2),DAY(S2-WEEKDAY(S2,11)+1))))),1,WEEKNUM(DATE(YEAR(S2),1,DAY(S2-WEEKDAY(S2,11)+1)),WEEKDAY(DATE(YEAR(S2),1,1),11)+10))))

 

상기 공식을 써도 8월1일~8월3일 을 예로 들면 7월 4주차 혹은 8월 1주차로 나와요 달력으로 보면

7월 5주차인데 .... 다른 방법 없을까요?

조건)

ⓐ월~일 기준

ⓑ과반수 (목) 포함 시 주차 계산 [ex 4월28일은 5월 1주차 같이 계산]

=MONTH((A2-WEEKDAY(A2)+2-IF(WEEKDAY(A2)<2,7,0))+5-2) & "월" &
( ( ( (A2-WEEKDAY(A2)+2-IF(WEEKDAY(A2)<2,7,0))+5-2)
   -( ((EOMONTH(((A2-WEEKDAY(A2)+2-IF(WEEKDAY(A2)<2,7,0))+5-2),-1)+1)
       -WEEKDAY(EOMONTH(((A2-WEEKDAY(A2)+2-IF(WEEKDAY(A2)<2,7,0))+5-2),-1)+1)+5)
       +IF(TEXT(((EOMONTH(((A2-WEEKDAY(A2)+2-IF(WEEKDAY(A2)<2,7,0))+5-2),-1)+1)
                 -WEEKDAY(EOMONTH(((A2-WEEKDAY(A2)+2-IF(WEEKDAY(A2)<2,7,0))+5-2),-1)+1)+5),"yymm")
           <TEXT(((A2-WEEKDAY(A2)+2-IF(WEEKDAY(A2)<2,7,0))+5-2),"yymm"),7,0)
     )
   ) / 7 + 1) & "주차"

=LET(D, A2, WS, 2, WO, 5,
WSD, D-WEEKDAY(D,1)+WS-IF(WEEKDAY(D)<WS,7,0),
WOD, WSD+WO-WS,
M, EOMONTH(WOD,-1)+1,
MO, M-WEEKDAY(M)+WO,
MOD, MO+IF(TEXT(MO,"yymm")<TEXT(WOD,"yymm"),7,0),
CONCAT(MONTH(WOD),"월",(WOD-MOD)/7+1,"주차"))