위키
포럼
도구
바뀐글
랜덤

"함수 week2monweek()"의 두 판 사이의 차이

잔글 (봇: 자동으로 텍스트 교체 (-source +syntaxhighlight))
 
11번째 줄: 11번째 줄:
[[category: Excel]]
[[category: Excel]]
===1 cell===
===1 cell===
<source lang='text'>
<syntaxhighlight lang='text'>
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-DATE(YEAR(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-WEEKDAY(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-1)+4),1,3)+WEEKDAY(DATE(YEAR(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-WEEKDAY(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-1)+4),1,3))+5)/7)+1
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-DATE(YEAR(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-WEEKDAY(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-1)+4),1,3)+WEEKDAY(DATE(YEAR(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-WEEKDAY(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-1)+4),1,3))+5)/7)+1
</source>
</syntaxhighlight>


===2 cells===
===2 cells===
*B1
*B1
<source lang='text'>
<syntaxhighlight lang='text'>
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
</source>
</syntaxhighlight>
*C1
*C1
<source lang='text'>
<syntaxhighlight lang='text'>
=B1&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(B1,4),RIGHT(B1,2),4)-DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3)+WEEKDAY(DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3))+5)/7)+1
=B1&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(B1,4),RIGHT(B1,2),4)-DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3)+WEEKDAY(DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3))+5)/7)+1
</source>
</syntaxhighlight>
===3 cells===
===3 cells===
*B1
*B1
<source lang='text'>
<syntaxhighlight lang='text'>
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
</source>
</syntaxhighlight>
*C1
*C1
<source lang='text'>
<syntaxhighlight lang='text'>
=DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3)
=DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3)
</source>
</syntaxhighlight>
*D1
*D1
<source lang='text'>
<syntaxhighlight lang='text'>
=B1&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(B1,4),RIGHT(B1,2),4)-C1+WEEKDAY(C1)+5)/7)+1
=B1&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(B1,4),RIGHT(B1,2),4)-C1+WEEKDAY(C1)+5)/7)+1
</source>
</syntaxhighlight>
===4 cells===
===4 cells===
*B1
*B1
<source lang='text'>
<syntaxhighlight lang='text'>
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
</source>
</syntaxhighlight>
*C1
*C1
<source lang='text'>
<syntaxhighlight lang='text'>
=DATE(LEFT(B1,4),RIGHT(B1,2),4)
=DATE(LEFT(B1,4),RIGHT(B1,2),4)
</source>
</syntaxhighlight>
*D1
*D1
<source lang='text'>
<syntaxhighlight lang='text'>
=DATE(YEAR(C1-WEEKDAY(C1-1)+4),1,3)
=DATE(YEAR(C1-WEEKDAY(C1-1)+4),1,3)
</source>
</syntaxhighlight>
*E1
*E1
<source lang='text'>
<syntaxhighlight lang='text'>
=B1&"-W"&RIGHT(A1,2)-INT((C1-D1+WEEKDAY(D1)+5)/7)+1
=B1&"-W"&RIGHT(A1,2)-INT((C1-D1+WEEKDAY(D1)+5)/7)+1
</source>
</syntaxhighlight>


==PHP==
==PHP==
[[category: PHP]]
[[category: PHP]]
<source lang='php'>
<syntaxhighlight lang='php'>
function week2monweek($week) {
function week2monweek($week) {
   $ym = date('Y-m', strtotime("$week-4"));
   $ym = date('Y-m', strtotime("$week-4"));
66번째 줄: 66번째 줄:
echo week2monweek('2012-W52');
echo week2monweek('2012-W52');
# 2012-12-W4
# 2012-12-W4
</source>
</syntaxhighlight>


==같이 보기==
==같이 보기==
*[[함수 monweek2week()]]
*[[함수 monweek2week()]]

2020년 11월 2일 (월) 02:36 기준 최신판

1 개요[ | ]

함수 week2monweek
  • YYYY-Www → YYYY-MM-Ww
  • test set
2012-W01 → 2012-01-W1
2012-W11 → 2012-03-W3
2012-W52 → 2012-12-W4

2 Excel[ | ]

2.1 1 cell[ | ]

=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-DATE(YEAR(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-WEEKDAY(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-1)+4),1,3)+WEEKDAY(DATE(YEAR(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-WEEKDAY(DATE(LEFT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),4),RIGHT(TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM"),2),4)-1)+4),1,3))+5)/7)+1

2.2 2 cells[ | ]

  • B1
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
  • C1
=B1&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(B1,4),RIGHT(B1,2),4)-DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3)+WEEKDAY(DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3))+5)/7)+1

2.3 3 cells[ | ]

  • B1
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
  • C1
=DATE(YEAR(DATE(LEFT(B1,4),RIGHT(B1,2),4)-WEEKDAY(DATE(LEFT(B1,4),RIGHT(B1,2),4)-1)+4),1,3)
  • D1
=B1&"-W"&RIGHT(A1,2)-INT((DATE(LEFT(B1,4),RIGHT(B1,2),4)-C1+WEEKDAY(C1)+5)/7)+1

2.4 4 cells[ | ]

  • B1
=TEXT(DATE(LEFT(A1,4),1,1)-WEEKDAY(DATE(LEFT(A1,4),1,1)-1)+7*(RIGHT(A1,2)-INT((8-WEEKDAY(DATE(LEFT(A1,4),1,1),2))/4))+4,"YYYY-MM")
  • C1
=DATE(LEFT(B1,4),RIGHT(B1,2),4)
  • D1
=DATE(YEAR(C1-WEEKDAY(C1-1)+4),1,3)
  • E1
=B1&"-W"&RIGHT(A1,2)-INT((C1-D1+WEEKDAY(D1)+5)/7)+1

3 PHP[ | ]

function week2monweek($week) {
  $ym = date('Y-m', strtotime("$week-4"));
  $weeknum = date('W', strtotime("$week-4")) - date('W', strtotime("$ym-4")) + 1;
  return "$ym-W$weeknum";
}

echo week2monweek('2012-W52');
# 2012-12-W4

4 같이 보기[ | ]

원본 주소 "https://zetawiki.com/w/index.php?title=함수_week2monweek()&oldid=605929"
문서 댓글 ({{ doc_comments.length }})
{{ comment.name }} {{ comment.created | snstime }}