# Solution: ISO week number in Sheet - Scion

This is a discussion on Solution: ISO week number in Sheet - Scion ; 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. ...

# Thread: Solution: ISO week number in Sheet

1. ## 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

http://en.wikipedia.org/wiki/ISO_week_date

Google found me an Excel solution at

http://www.cpearson.com/excel/weeknum.htm

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.

2. ## Re: Solution: ISO week number in Sheet

ts wrote:

> 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)

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 http://www.rmrsoft.com/
for other high quality software for EPOC machines.