formatting dates into weeks

  • Thread starter Thread starter joel
  • Start date Start date
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
 
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)
 
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
 
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
 
Back
Top