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. Hui... says:

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

2. JM says:

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

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

4. Steve says:

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

5. Steve says:

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

6. Steve says:

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

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

8. Ola Sandström says:

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

9. TheDesigner says:

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.

10. Richard says:

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.

Thanks Richard

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

13. Try This

=INT((DAY(A1)+(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2))-2)/7)+1

14. Martin Kelly says:

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

15. Brian West says:

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!

16. dhivagar says:

Thanks its working.

great job guys

17. Mo says:

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

18. Vince says:

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?

19. Doron Tangarorang says:

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

20. Peter says:

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.