함수 week2monweek()

개요

함수 week2monweek

• YYYY-Www → YYYY-MM-Ww
• test set

2012-W01 → 2012-01-W1

2012-W11 → 2012-03-W3

2012-W52 → 2012-12-W4

Excel

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 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

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

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

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

같이 보기

• 함수 monweek2week()