Consulting

Results 1 to 16 of 16

Thread: Solved: UDF to emulate Weeknum()

  1. #1
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location

    Solved: UDF to emulate Weeknum()

    Hi

    Does anyone know of such a UDF (or formula)? The analysis toolpack is not available on all PC's here, and they're locked down so that I can't install it.

    I'm after a UDF/Formula that will look at a date and return the week number (Mondays being the beginning of the new week)

    Thanks

    BD
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    =1+INT((F1-(DATE(YEAR(F1),1,2)-WEEKDAY(DATE(YEAR(F1),1,0))))/7)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    =1+INT(MOD(INT((A1-2)/7)+0.6,52+5/28))
    SHAZAM!

  4. #4
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location
    Thanks both - that fixes my problem nicely (my formula problem, not my mental problem)
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

  5. #5
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Quote Originally Posted by xld
    =1+INT((F1-(DATE(YEAR(F1),1,2)-WEEKDAY(DATE(YEAR(F1),1,0))))/7)
    XLD,

    Since your private msg box is full, I'm uploading here. I believe your algorithm isn't quite right, yet. First year (2007) begins w/ week 1 and ends with week 53 (a 2 day week?), year 2 begins with week 2 and ends with week 53 as do years 3 and 4.


    Ron
    Orlando, FL

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Well as I see it, mine gives exactly the same answer as MS' WEEKNUM, which is what was asked for.

    I think you are missing the fact the BD said the week starts on Monday.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    Quote Originally Posted by RonMcK
    XLD,

    Since your private msg box is full, I'm uploading here. I believe your algorithm isn't quite right, yet. First year (2007) begins w/ week 1 and ends with week 53 (a 2 day week?), year 2 begins with week 2 and ends with week 53 as do years 3 and 4.
    Ron
    Orlando, FL

    Sir Babydum GBE is runing off the european calendar not the united states calendar.
    SHAZAM!

  8. #8
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    XLD,

    I'll have to test my worksheet when I get home; I'm on a Mac (2004), here at work, and it doesn't have Analyisis Pack loaded (if that's even available for Excel Mac).

    Shazam,

    How does an European calendar differ from a not European calendar?

    Thanks,

    Ron
    Orlando, FL

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    AFAIAA, there is no difference in a European and a US calendar. Each month has the same nuumber of days, the day/dates are the same, et al.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by xld
    AFAIAA, there is no difference in a European and a US calendar. Each month has the same nuumber of days, the day/dates are the same, et al.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    Quote Originally Posted by RonMcK
    Shazam,

    How does an European calendar differ from a not European calendar?

    Thanks,

    Ron
    Orlando, FL
    Try testing this link below.

    http://www.onlineconversion.com/day_week_number.htm
    SHAZAM!

  12. #12
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Quote Originally Posted by Shazam
    Quote Originally Posted by XLD
    Quote Originally Posted by RonMcK
    Shazam,

    How does an European calendar differ from a not European calendar?
    AFAIAA, there is no difference in a European and a US calendar. Each month has the same nuumber of days, the day/dates are the same, et al.
    Try testing this link below.

    http://www.onlineconversion.com/day_week_number.htm
    Apparently, XLD, a week is not a week in Europe or, rather, it is not necessarily a period of 7 contiguous days. The months and days are identical, as you noted, however the "European/ISO" calendar can divide a 'real' week (7 day span, Sun - Sat) into as many as 3 'weeks' when the week spans the year-end. Using Shazam's ul reference, I found that a week can be as short as 1 day. So, the European calendar can have more than one 53 week year in the four year cycle. Strange by true.

    Ron
    Orlando, FL

  13. #13
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    If you like to use the united states calendar week.

    Then try.


    =ROUNDDOWN((A2-DATE(YEAR(A2),1,1))/7,0)+1


    Hope it helps!
    SHAZAM!

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by RonMcK
    Apparently, XLD, a week is not a week in Europe or, rather, it is not necessarily a period of 7 contiguous days. The months and days are identical, as you noted, however the "European/ISO" calendar can divide a 'real' week (7 day span, Sun - Sat) into as many as 3 'weeks' when the week spans the year-end. Using Shazam's ul reference, I found that a week can be as short as 1 day. So, the European calendar can have more than one 53 week year in the four year cycle. Strange by true.

    Ron
    Orlando, FL
    A week is a week wherever you are. What may be different is how you define where week 1 starts, whiuch is where the ISO week number definition is much tighter than MS's.

    But BD wanted a WEEKNUM replacement, as found in Excel, using Monday as the start of the week. That is exactly what I gave him.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Quote Originally Posted by xld
    A week is a week wherever you are. What may be different is how you define where week 1 starts, whiuch is where the ISO week number definition is much tighter than MS's.

    But BD wanted a WEEKNUM replacement, as found in Excel, using Monday as the start of the week. That is exactly what I gave him.
    XLD,

    My apologies, I meant to acknowledge that point several msgs back. I discovered how to enable Analysis Pack (for Excel and VBA) on Mac, so, I saw that your formula matched MS'.

    In my last post, I only wanted to point out that ISO ends a year with a short week and begins the next year with another short week rather then have days outside a year included in the last/first week of either year.


    Shazam,

    Thanks, I'll try that formula when I get home.


    Have a good evening/good night.

    Ron
    Orlando, FL

  16. #16
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location
    Well XLD's formula works perfectly for me - but thanks all for your input.

    My head hurts after reading all that.

    P.S. On planet BeeDee there are 8 days a week, 1 is a working day, and seven are rest days. Oddly, the planet's rotation around its sun is such that the rest days are 30 hours long, and the work days are 30 minutes long.

    If I ever have to do a preadsheet there - I'll ask.
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •