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.

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.