Week of the Month

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.

20 thoughts on “Week of the Month

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

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

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

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

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

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

  9. […] [http://www.dailydoseofexcel.com/archives/2007/07/02/week-of-the-month/] page_revision: 1, last_edited: 1234893480|%e %b %Y, %H:%M %Z (%O ago) edittags history files print site tools+ options edit sections append backlinks view source parent block rename delete help | terms of service | privacy | report a bug | flag as objectionable Hosted by Wikidot.com — get your free wiki now! Unless stated otherwise Content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License Click here to edit contents of this page. Click here to toggle editing of individual sections of the page (if possible). Watch headings for an “edit” link when available. Append content without editing the whole page source. Check out how this page has evolved in the past. If you want to discuss contents of this page – this is the easiest way to do it. View and manage file attachments for this page. A few useful tools to manage this Site. See pages that link to and include this page. Change the name (also URL address, possibly the category) of the page. View wiki source for this page without editing. View/set parent page (used for creating breadcrumbs and structured layout). Notify administrators if there is objectionable content in this page. Something does not work as expected? Find out what you can do. General Wikidot.com documentation and help section. Wikidot.com Terms of Service – what you can, what you should not etc. Wikidot.com Privacy Policy. _uff = false; _uacct = “UA-68540-5?; _udn=”wikidot.com”; urchinTracker(); […]

  10. EASY WAY….

    there is only 7days in a week, the 1st week of the month is 1st-7th of that month, so there for the 2nd week would be 8th-14th,

    Using the following formula, you find what day in the date (1,2,3,4,5,6,7,8,9,10… etc.) and divide by 7 (because there is 7 days in the week.) and the round up to a whole number therefore it tells u what week it is in that month.

    =ROUNDUP(DAY(A2)/7,0)

    For the four week cycle, are you wanting to restart the cycle on the 5th week monday if it happens to fall in that month? you need a starting date that is a monday,could try this to find the next date…

    =(ROUNDUP(((TODAY()-StartDate)/fourweeks),0)*fourweeks)+StartDate

    Where “fourweeks” = 28 Days, (7days x 4 weeks)

    Martin Kelly

  11. I am tryingto determine which month’s to give a 5th week to. My data is collected every week. I have a grid that displays Weeks as rows and Months as columns. I need to fill in the collected data into this grid. I need to only have 52 weeks. I can not fill in partial weeks. By looking at the calander and counting the extra days I have determined Apr, Jul, Sep and Dec should get these extra weeks (I add up the extra days each month beyond 28, when I get 7 that is the month to give a 5th week). How do I determine this programmatically? Thanks!

  12. I was wondering if you could help please. I need a formula to highlight cells that fall on the 1st to 7th , 8th to 15th, 16th to 23rd and 24th to last day of month, for all the dates that gets entered in a cell. I am trying to create a reminder system for patients. The way it works is that when they collect medicines, I enter the date of collection and prepare the next lot 1 week before they come to the pharmacy to collect medications. I want to highlight the cells for patients medicines that are due for collection next week. Thanks

  13. Hi Steve, I haven’t tested on anything but December 2011. But it works exactly like JM’s. I’m new to understanding (old to attempting to understand)excel formula’s. I think I get everything except the +0.4999 part. Could you explain?

  14. Help..

    How can i calculate the week and if its the 8 days it will round up to 2weeks

    (Start Date) May 1, 2008 – May 7, 2008 = 1 week

    (Start Date) May 1, 2008 – May 16, 2008 = 3 weeks

    I’m just a newbie in excel pls.. Help..

  15. Just thought I would leave this here.

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

    Steve’s formula worked really well until I hit Feb 2014, and then it would always start on week 2 instead of 1.
    Changing the 0.49999 to 0.3 dropped the rounding to 1 instead of 2.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.