formatting dates into weeks

J

joel

do somebody knows how to translate a date into a week nbr ?
I tried something like (date - 01jan) / 7 but it seems
hazardous
thanks for help
joel
 
A

Anon

joel said:
do somebody knows how to translate a date into a week nbr ?
I tried something like (date - 01jan) / 7 but it seems
hazardous
thanks for help
joel
Click to expand...

Calculating a week number from a date is not trivial as weeks do not fit
cleanly onto years.

There is a WEEKNUM function in the Analysis ToolPak Add-In. Type WEEKNUM
into Help for details. This uses a simple (Microsoft) definition of when
week 1 starts.

If you want the internationally-recognised ISO week number, use one of these
formulas:

=INT((A1-(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)-WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1
-1)+4),1,3)))+5)/7)

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY
(A1+6)),1,3)))/7)
 
R

Ron Rosenfeld

do somebody knows how to translate a date into a week nbr ?
I tried something like (date - 01jan) / 7 but it seems
hazardous
thanks for help
joel
Click to expand...

Did you look at the WEEKNUM function?


--ron
 
A

Arvi Laanemets

Hi

Anon said:
Calculating a week number from a date is not trivial as weeks do not fit
cleanly onto years.

There is a WEEKNUM function in the Analysis ToolPak Add-In. Type WEEKNUM
into Help for details. This uses a simple (Microsoft) definition of when
week 1 starts.

If you want the internationally-recognised ISO week number, use one of these
=INT((A1-(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)-WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY
(A1+6)),1,3)))/7)
Click to expand...


And in different countryes are different definitions for ISO weeknumber.
P.e. in many Eurpean countries the 1st week of year the one with 1st
Thursday in it.


Arvi Laanemets
 
Top