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

+ Reply to Thread
Results 1 to 2 of 2

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.

+ Reply to Thread