+ Reply to Thread
Results 1 to 11 of 11

Calculate week numbers from custom date

  1. #1
    Registered User
    Join Date
    01-04-2007
    Posts
    7

    Calculate week numbers from custom date

    Hi.

    I want to calculate a week number from a date entered on a staff roster. The week numbering needs to start from the beginning of the Australian financial year (1 July) until the end of the financial year (30 June) in the following calendar year.

    I could type the number in but was using the below formula to save a few key strokes.

    The formula was:

    =IF(ISBLANK(X3),"",INT((X3-DATE(YEAR(X3-31),7,1)-WEEKDAY(X3,2))/7)+2)

    where I entered the date at X3 and the week begins on a Monday.

    Formula worked fine from 1 July to end of 2010 but from beginning of 2011 the formula calculates the week numbers as negatives e.g. Week Ending: 13 March 2011 calculates as Week Number -15, Week Ending: 20 March 2011 as Week Number -14 etc.

    Sorry but I struggle with anything too complicated in Excel so any help would be greatly appreciated.

    Thanks
    David
    Last edited by Lord Tink; 03-02-2011 at 03:39 PM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Calculate week numbers from custom date

    Hello,

    this formula

    =WEEKNUM(X3)+IF(MONTH(X3)<=6,26,-26)

    delivers the same result as yours, except for week 1, which I guess is due to the fact that the start of week 1 is still in June. Would that be correct?

    I'm sure DaddyLongLegs will swing by soon and deliver one of his date gems.

  3. #3
    Registered User
    Join Date
    01-04-2007
    Posts
    7

    Re: Calculate week numbers from custom date

    Thanks Teylen
    I copied & pasted your formula but it calculated a date of 26/01/1900 not a week number.
    What did I do wrong??
    David

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Calculate week numbers from custom date

    Format the cell as number, not date.

  5. #5
    Registered User
    Join Date
    01-04-2007
    Posts
    7

    Re: Calculate week numbers from custom date

    Thanks Paul. Reformating worked.

    And thanks again Teylyn for the formula. It now works great. But as you pointed out, June 1 was on Monday and is technically in June of the previous financial year. My weeks are out by one e.g. the week ending 13 March 2011 is Week 37 according to my company, not Week 38 as calculated by the formula.

    How would I correct that in the formula?

    Again, thanks in advance.

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Calculate week numbers from custom date

    the week ending 13 March 2011 is Week 37 according to my company
    I don't understand. In your initial question you say

    I entered the date at X3 and the week begins on a Monday.
    The formula I provided will deliver the result 37 for the week that starts with Monday 7-march 2011 (which is the week that ends on 13 March).

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculate week numbers from custom date

    Assuming that the week numbers are defined a little like ISO week numbers, i.e. week 1 starts with the first Monday on or after 28th June then you could use this formula

    =IF(X3="","",INT((X3-WEEKDAY(X3,2)-DATE(YEAR(X3+188-WEEKDAY(X3,2))-1,6,20))/7))

    format result cell as general, works for any year.....
    Audere est facere

  8. #8
    Registered User
    Join Date
    01-04-2007
    Posts
    7

    Re: Calculate week numbers from custom date

    Teylyn
    Thanks again for your help but it just doesn't seem to work for me. I can't see where we differ.
    daddylonglegs's formula works.
    Appreciate your help.
    David

  9. #9
    Registered User
    Join Date
    01-04-2007
    Posts
    7

    Re: Calculate week numbers from custom date

    Thanks daddylonglegs
    Your formula works brilliantly with every combination I have tried so far. You are the "date guru".
    Really appreciate your help (and the help of all other posters).
    Best wishes
    David

  10. #10
    Registered User
    Join Date
    06-06-2012
    Location
    Hamilton, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Calculate week numbers from custom date

    Quote Originally Posted by daddylonglegs View Post
    Assuming that the week numbers are defined a little like ISO week numbers, i.e. week 1 starts with the first Monday on or after 28th June then you could use this formula

    =IF(X3="","",INT((X3-WEEKDAY(X3,2)-DATE(YEAR(X3+188-WEEKDAY(X3,2))-1,6,20))/7))

    format result cell as general, works for any year.....
    Hi daddylonglegs, how would I modify this, in the case of the first day of the company year being the first Mon on or after 27th Feb 2012? Sorry, but I'm not very good with excel date logic. Would you be able to describe to me what the key parts of the formula are doing? I can understand that the first part is establishing what day of the week the date in X3 is, but after that, I'm stumped. I thought that the X3+188 might relate to the number of days after Jan 1st, the fiscal date (July 1st in your example) might be, in conjunction with the 6,20 being 20th June, but when I change these values, the formula breaks. Any help would be appreciated. Thanks

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculate week numbers from custom date

    Hello raahl,

    "hijacking" old questions is against the forum rules, can you please post this as a new thread, with a link back to here? Thanks

+ 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