WEEKNUM()

C

Ciara

im using the WEEKNUM function in my excel but when other users in my office
open my files it doesn't work?

All the other date functions work.

any ideas?
 
J

JulieD

Hi Ciara

from help
"If this function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in."

you must have the Analysis ToolPak add-in enabled on your machines, the
others in the office will need to do the same (tools / add-in, tick analysis
toolpak)
 
A

Amedee Van Gasse

JulieD shared this with us in microsoft.public.excel.misc:
Hi Ciara

from help
"If this function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in."

you must have the Analysis ToolPak add-in enabled on your machines,
the others in the office will need to do the same (tools / add-in,
tick analysis toolpak)
Click to expand...

You should know that the results of the ATP-weeknum function are WRONG.

A correct formula for an ISO weeknum in the default 1904 based date
system is:

=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),
{1E+99,7})*{1,-1})+5)/7)

This one is also good, and also works in the 1900 based date system:

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

Read this page for more info:
http://www.cpearson.com/excel/weeknum.htm

An advantage of this formula is that you don't need to install the ATP
any more!

--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way

How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoynePollard/FGA/questions-with-yes-or-n
o-answers.html
 
G

GD

Hi Amedee,
As often shared in microsoft.public.fr.excel (originaly by Christophe
Pejout)
here is a formula for ISOWeekNum :
=INT(MOD(INT((A1-2)/7)+0.6,52+5/28))
this works only in the 1900 based date system
and is related to the solar cycle : same days of the week return every 28
years

beware : this formula will not be valid after year 2104 !!
;o)))
HTH
 
A

Amedee Van Gasse

GD shared this with us in microsoft.public.excel.misc:
Hi Amedee,
As often shared in microsoft.public.fr.excel (originaly by Christophe
Pejout)
here is a formula for ISOWeekNum :
=INT(MOD(INT((A1-2)/7)+0.6,52+5/28))
this works only in the 1900 based date system
and is related to the solar cycle : same days of the week return
every 28 years
Click to expand...

Nice formula, but I prefer formulae that reflect the ruleset of the ISO
standard, like second formula I quoted (the one that works for both
date systems 1904/1900)
This solar cycle formula is apparently constructed from deduction and
is based on an emergent feature of the calendar system. So from an
intellectual point of view I admire it, but from a practical point of
view I won't use it.
beware : this formula will not be valid after year 2104 !!
Click to expand...

And this is the reason why I won't use it. ;-)
;o)))
HTH
Click to expand...


--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way

How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoynePollard/FGA/questions-with-yes-or-n
o-answers.html
 
G

GD

Hi Amedee
;o)))
I totaly respect your choice ...
But
;o)))
the change of the celestial parameters is however much more improbable than
the ISO rules !!!
Even if the case ariseeed, much other temporal rules ISO or others should be
revised.
;o)))

@+
 
A

Amedee Van Gasse

GD shared this with us in microsoft.public.excel.misc:
Hi Amedee
;o)))
I totaly respect your choice ...
But
;o)))
the change of the celestial parameters is however much more
improbable than the ISO rules !!!
Even if the case ariseeed, much other temporal rules ISO or others
should be revised.
;o)))
Click to expand...

ROTFLMAO
ACK, ACK, ACK.


--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way

How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoynePollard/FGA/questions-with-yes-or-n
o-answers.html
 
Top