« Having a number and a formula “co-exist” in a cell
The Opportunity Cost of Fireworks »

Week of the Month

2nd July 2007, 10:00 pm by Dick Kusleika

James shares his formula for determining which week of the month a particular date is in. He has a list of Sundays, each of which is the first day of the week. His formula:

ROUNDUP(((DAY(A1)-1)/7)+1,0)

James’ formula only works if A1 contains the first day of the week. If you want to calculate the week for any date, you’ll need a slightly longer formula. Here’s one that I created:

=ROUNDUP((DAY(A1)+WEEKDAY(DATE(YEAR(A1),MONTH(A1),0)))/7,0)

Both formulas assume that Sunday is the first day of the week.

Thanks for the formula James.

Category: Date Functions  |  Comment (RSS)  |  Trackback

11 Comments

  1. Hui...:

    I will assume that all references to A1 in the formulas should be to A2 etc

    3 July 2007, 2:02 am

  2. JM:

    Dick-
    Check your formula for months where the first day falls on a Sunday (like July 2007).

    3 July 2007, 7:30 am

  3. Dick Kusleika:

    Hui: Yep.

    JM: Dang it! =ROUNDUP((DAY(A1)+WEEKDAY(DATE(YEAR(A1),MONTH(A1),0)))/7,0)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),0))=7)

    How’s that for a kludge?

    3 July 2007, 8:41 am

  4. Steve:

    My solution is:
    =ROUND((DAY(A1)-WEEKDAY(A1,1))/7,0)+1

    3 July 2007, 10:59 am

  5. Steve:

    Oops, if 1st starts on T, F, or S, does not work. I am ashamed, will try harder.

    3 July 2007, 11:10 am

  6. Steve:

    OK, this has been tested for every day of the week, sorry for the false start.

    Still only uses 3 functions!

    =ROUND((DAY(M7)-WEEKDAY(M7,1))/7+0.4999,0)+1

    3 July 2007, 11:25 am

  7. Tushar Mehta:

    I don’t know how to articulate this better so here goes nothing.

    This kind of manipulation is something I’ve been doing for a long time and I suspect there is a mathematical basis for why it works but I don’t know what that might be.

    The key to mapping data from one measurement system, as it were, to another is to adjust the “start” of the first system as zero, use the DIV or INT (or multiplier or whatever) operator and then add 1 to the result (assuming the new system starts measuring at 1).

    So, in this case, to measure week-of-the-month such that the first 7 days are week 1, the next 7 week 2, etc., use

    =INT((DAY(A2)-1)/7)+1

    Beyond this when it comes to measuring time I am very leery about generalizations since modern time is based on what comes across as a rather irrational schema. But, the following seems to work:

    Applying the same principles to measuring weeks based on Sundays, use
    =INT((DAY(A2)-1+WEEKDAY(B2)-1)/7)+1
    where A2 is the day-of-interest and B2 is the 1st of the month. Of course, one can fold B2 into the formula with
    =INT((DAY(A2)-1+WEEKDAY(DATE(YEAR(A2),MONTH(A2),1))-1)/7)+1

    There may be shorter formulas but frankly I prefer those that better reflect the underlying principles on which they are based.

    For more on this subject see
    Managing data that include logical blocks
    http://www.tushar-mehta.com/excel/tips/data_in_blocks/
    I suspect the presentation can be improved but that will have to wait.

    4 July 2007, 8:38 am

  8. Ola Sandström:

    One option:
    =WEEKNUM(A2)-WEEKNUM(EOMONTH(A2;-1)+1)+1
    At first it felt strange to see the 5th week in a month
    //Ola

    11 July 2007, 6:31 pm

  9. TheDesigner:

    If you extend your dates to about July 4th you will see your solution skips the 1st week of July and (due to rounding errors) makes it week 2 of the month.

    18 July 2007, 12:47 am

  10. Richard:

    Thanks for the advise above all work wonderfully, but as is life I’d like more.

    I have a planning system that requires the same pattern every four weeks (no 5th week)Is there a formula that can give me a 4 week cycle within a month ,a cycle that will not suddenly start again at the month start.

    Product A will be planned on the 1st and 3rd Monday
    Product B will be planned on the 2nd and 4th Monday

    A five week month would throw this pattern out.

    Please help.

    Thanks Richard

    8 October 2007, 4:06 pm

  11. Tullio:

    I am looking for something similar. I have my dates and I have my week numbers.
    Now I am trying to have a formula that converts the week number to the first day of that week with monday being the first day of the week.

    EX. Today is 7/21/2008 and the week is week 30. Week of should be 7/21/2008.

    Tomorrow is 7/22/2008 and the week is week 30. Week of should be 7/21/2008.

    Even if it was 7/21/2008-07/27/2008 would work.

    Any help would be greatly appreciated.

    21 July 2008, 9:50 am

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment