- weeks start on monday
- the first week of the year is the one containing the fist Thursday of the year
The starting point for the formula below can be found here. It's pretty complex, and I suggest that first you should build some helper calculated functions for the most used values: day of week (DOW) of the current date, DOW of the first day of the current year, current day/month/year.
And here it is (replace "the_date" with the name of your date column):
=INT(
IF(AND(MONTH([the_date])=1,
5 < WEEKDAY(DATE(YEAR([the_date]),1,1)),
WEEKDAY(DATE(YEAR([the_date]),1,1)) < 9 - (DAY([the_date])-1)),
(IF(WEEKDAY(DATE(YEAR([the_date]),1,1)) < 6,1,0)) +
44 +
(59+WEEKDAY(DATE(YEAR([the_date])-1,1,1))-WEEKDAY(DATE(YEAR([the_date]),1,1)))*9 / 64,
IF(AND(MONTH([the_date])=12,
30-(DAY([the_date])-1) < WEEKDAY(DATE(YEAR([the_date])+1, 1, 1)) - 2,
WEEKDAY(DATE(YEAR([the_date])+1,1,1)) < 5),
1,
(IF(WEEKDAY(DATE(YEAR([the_date]),1,1)) < 6,1,0)) +
4*(MONTH([the_date])-1) +
(2*(MONTH([the_date])-1)+(DAY([the_date])-1)+WEEKDAY(DATE(YEAR([the_date]),1,1))-WEEKDAY([the_date])+6)*36/256)))
... I know, it's horrible... if you have time, make it pretty...
3 comentarii:
Thanks very much - I have looked at many weeknumber calculations, but none of them works. This one does however!! :-) I have modified it for weeks starting in monday as we have in Denmark. I think your version is for weeks starting on sundays? My version:
=HELTAL(HVIS(OG(MÅNED(Dato)=1;4<UGEDAG(DATO(ÅR(Dato);1;1);2);UGEDAG(DATO(ÅR(Dato);1;1);2)<8-(DAG(Dato)-1));(HVIS(UGEDAG(DATO(ÅR(Dato);1;1);2)<5;1;0))+44+(59+UGEDAG(DATO(ÅR(Dato)-1;1;1);2)-UGEDAG(DATO(ÅR(Dato);1;1);2))*9/64;HVIS(OG(MÅNED(Dato)=12;29-(DAG(Dato)-1)<UGEDAG(DATO(ÅR(Dato)+1;1;1);2)-2;UGEDAG(DATO(ÅR(Dato)+1;1;1);2)<4);1;(HVIS(UGEDAG(DATO(ÅR(Dato);1;1);2)<5;1;0))+4*(MÅNED(Dato)-1)+(2*(MÅNED(Dato)-1)+(DAG(Dato)-1)+UGEDAG(DATO(ÅR(Dato);1;1);2)-UGEDAG(Dato;2)+5)*36/256)))
The formula used by Poul doesn't work for 2010. You can use this one
=1+INT((INT(The_Date)-DATE(YEAR(INT(The_Date)+4-WEEKDAY(INT(The_Date)+6));1;5)+WEEKDAY(DATE(YEAR(INT(The_Date)+4-WEEKDAY(INT(The_Date)+6));1;3)))/7)
Anonim spunea - I made it for SP 2010 - and it works. What problems are you experiencing?
Trimiteți un comentariu