Week

J

Juran

How can I change the day of the week to week.
Ex.

01/02/06 - week 1
02/10/06 - week 5

Thank you

Juran
 
P

Peo Sjoblom

02/10/06 will not return week 5 regardless using ISO or absolute,

with 02/10/06 in A1

absolute weeknumber

=WEEKNUM(A1) returns 6

The non ATP version


=INT(((A1-DATE(YEAR(A1),1,0))+6)/7)

returns 6


ISO weeknumber

=1+INT(MIN(MOD(A1-DATE(YEAR(A1)+{-1;0;1},1,5)+WEEKDAY(DATE(YEAR(A1)+{-1;0;1},1,3)),734))/7)

returns 6

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
R

Ron de Bruin

See also
http://www.rondebruin.nl/weeknumber.htm

And
http://www.rondebruin.nl/isodate.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


Peo Sjoblom said:
02/10/06 will not return week 5 regardless using ISO or absolute,

with 02/10/06 in A1

absolute weeknumber

=WEEKNUM(A1) returns 6

The non ATP version


=INT(((A1-DATE(YEAR(A1),1,0))+6)/7)

returns 6


ISO weeknumber

=1+INT(MIN(MOD(A1-DATE(YEAR(A1)+{-1;0;1},1,5)+WEEKDAY(DATE(YEAR(A1)+{-1;0;1},1,3)),734))/7)

returns 6

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
Click to expand...
 
Top