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.
I will assume that all references to A1 in the formulas should be to A2 etc
Dick-
Check your formula for months where the first day falls on a Sunday (like July 2007).
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?
My solution is:
=ROUND((DAY(A1)-WEEKDAY(A1,1))/7,0)+1
Oops, if 1st starts on T, F, or S, does not work. I am ashamed, will try harder.
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
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.
One option:
=WEEKNUM(A2)-WEEKNUM(EOMONTH(A2;-1)+1)+1
At first it felt strange to see the 5th week in a month
//Ola
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.
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
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.
[...] [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(); [...]
Try This
=INT((DAY(A1)+(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2))-2)/7)+1
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
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!