+ Reply to Thread
Results 1 to 3 of 3

Weeknum

  1. #1
    Registered User
    Join Date
    03-02-2007
    Location
    Warrington, Cheshire, UK
    Posts
    7

    Weeknum

    Was given following formula which worked fine until the year changed -
    =WEEKNUM(A2)-WEEKNUM(DATE(2005,3,1))+1

    My spreadsheet runs timestamps from 1 mar 2005 thru to 1 mar 2007. 1 Mar 2005 was week 1 and worked great thru to 31 dec 2005 which was week 44.
    As soon as the date changed to 1 Jan 2006 - formula returned - 8 !!!!
    I have tried fiddling around but still have no success in January 2006 carrying on from 2005 increasing from 44 onwards.
    Spreadsheet shows

    Date (A2 etc) ........ Week number
    01/03/2005 ...................1
    thru to
    31/12/2005 ...................44

    then !
    01/01/2006 .................. -8

    Would appreciate any help again - thanks in advance
    Chaz

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,717
    See my reply to your original thread.....

    http://www.excelforum.com/showthread...50#post1760550

    ...if you want to start again at week 1 every 1st March then try

    =INT((INT(A2)-DATE(YEAR(EDATE(A2,-2)),3,1))/7)+1

    of course this will mean that you'll have a week 53 every year that lasts for only 1 day (or 2 days in a leap year)
    Last edited by daddylonglegs; 03-11-2007 at 12:22 PM.

  3. #3
    Registered User
    Join Date
    03-02-2007
    Location
    Warrington, Cheshire, UK
    Posts
    7

    Weeknum

    Yes, got that now !! Works fine - thanks a lot for your assistance.

    Excellent forum - great guys and gals helping out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1