# Solution: ISO week number in Sheet

• 09-22-2008, 09:12 AM
unix
Solution: ISO week number in Sheet
I wanted the formula for a week number for a given date, it turns out
that there are several ways to enumerate then. Here in Finland, and I
guess in most other EU countries also, the ISO standard is used. For
an exact definition see

[url]http://en.wikipedia.org/wiki/ISO_week_date[/url]

Google found me an Excel solution at

[url]http://www.cpearson.com/excel/weeknum.htm[/url]

that was developed by Evert van den Heuvel:

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

This has to be modified for Psion Sheet, since it lacks the
weekday() function.

After some playing around with the formula, I discovered that

=weekday(A1)

is equivalent to

=choose(mod(A1,7)+1,7,1,2,3,4,5,6)

which is compatible with Psion Sheet. Fully applicating the conversion
gives:

1+INT((A1-DATE(YEAR(A1+4-CHOOSE(MOD(A1+6,7)+1,7,1,2,3,4,5,6)),
1,5)+CHOOSE(MOD(DATE(YEAR(A1+4-CHOOSE(MOD(A1+6,7)+1,7,1,2,3,4,5,6)),
1,3),7)+1,7,1,2,3,4,5,6))/7)

Enjoy.

• 09-24-2008, 07:21 AM
unix
Re: Solution: ISO week number in Sheet
ts <tsand@hotmail.com> wrote:
[color=blue]
> After some playing around with the formula, I discovered that
> =weekday(A1)
> is equivalent to
> =choose(mod(A1,7)+1,7,1,2,3,4,5,6)[/color]

This is excellent, thanks! I managed to create a working weekday
function but it was a lot more clumsy than this.

--
Damian Walker
Add SAUSAGE to the subject of email replies to avoid my spam trap
--
Sent using an unregistered copy of RMRNews v1.02
Check out our website at [url]http://www.rmrsoft.com/[/url]
for other high quality software for EPOC machines.