# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Number of the Week in Month

## ROBERTGRAHAM01

Hi, 

I want to use a formula to calculate what week number in the month it is (i.e 1 to 6) from a particular date.

I know how to calculate this on an annual basis (i.e. 1 to 52) but not within the month.

This is what I have so far...


=IF(OR(D58=1, D58>=D57), ROUNDUP(DAY(D61)/7,0),ROUNDUP(DAY(D61)/7,0)+1)

d58 is a Weeday formula looking at d61 which is the date i want to look at. d57 is the weekday number of the first day of the month in cell d61.

I hope this makes sense to you all. Please help, this is driving me insane!

Thanks

----------


## darkyam

=WEEKNUM(D61)-WEEKNUM(D61-DAY(D61)-6)
No other cells should be necessary for this calculation.

----------


## NBVC

How about?

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

----------


## ROBERTGRAHAM01

Hi Both, 

Unfortunately, neither of those work properly.  The Weeknum suggestion seems to return the 30th March as Week 6.

The second response, the INT function, seems to return Week 4 for the 24th March..

Thanks anyway though!

Cheers

Rob

----------


## darkyam

As March begins on Saturday, the 30th is in the sixth week.  If you want the week of a particular month to start on the first day of the month, then you would never need week 6, as your first post states, but could just use =ROUNDUP(DAY(D61)/7),0)

----------


## ROBERTGRAHAM01

I have W/C Monday 31st March as Week 6...

----------


## darkyam

Pardon my ignorance, but what's W/C?  I have never heard initials refer to anything except water closet.

----------


## ROBERTGRAHAM01

I'm thinking of the business week as Monday-Sunday. Rather than a week being just 7 consequtive days from the first day of the month. I see what you mean. Eg, 1 and 2 March would be Week 1, then Week 2 would begin on 3rd and so on with Monday remaining as the first day of the week.

----------


## ROBERTGRAHAM01

Week Commencing

----------


## darkyam

=weeknum(c31-1)-weeknum(c31-day(c31)-6)

----------


## ROBERTGRAHAM01

Hey, that's great thanks! That seems to work perfectly so far

Cheers

Rob

----------


## daddylonglegs

> =weeknum(c31-1)-weeknum(c31-day(c31)-6)



OK, I'm late, I know..... :Smilie:  

I don't believe that formula will work for most January dates, it gives a negative number......also if 1st of the month is a Sunday, e.g. June 1st 2008, it gives a zero.

Assuming week 1 always starts on 1st of the month and week 2 begins on the first Monday after that try this formula to give week number

=INT((13-WEEKDAY(A1-1)+DAY(A1))/7)

where A1 contains your date

----------


## atrapp

> OK, I'm late, I know..... 
> 
> I don't believe that formula will work for most January dates, it gives a negative number......also if 1st of the month is a Sunday, e.g. June 1st 2008, it gives a zero.
> 
> Assuming week 1 always starts on 1st of the month and week 2 begins on the first Monday after that try this formula to give week number
> 
> =INT((13-WEEKDAY(A1-1)+DAY(A1))/7)
> 
> where A1 contains your date



This doesn't work for November 2008!

Actually, I have a spreadsheet in which I'm trying to determine how many days are in each week of the month for the month. So if the 1st of the month is on a Sat, there is only one day in the week for the month. If the 30th is on a Mon and there are 30 days in the month, there are only 2 days in that week for the month.

Any help is greatly appreciated!!!!

----------


## daddylonglegs

> This doesn't work for November 2008!



I believe it works in that it does what I said it would. The assumption, as I described, is that week 1 would start on the 1st of the month and week 2 will start on the following *Monday*

You seem to want a Sunday start in which case remove the -1 and use

=INT((13-WEEKDAY(A1)+DAY(A1))/7)

Of course to count the days in each week for a specific month you'd have to list all the days and then use that formula and count how many days in each week......

To do that without listing all the days you could do the following:


Put the 1st of the month of interest in A1, e.g. 1-Nov-2008 (you can format as just mmm-yy)

In A3 type "week number" and then in A4 to A9 list the week numbers 1 to 6

In B3 type "days" and then in B4 use this formula
=MIN(31-DAY(A$1+31),A4*7-WEEKDAY(A$1))+1-SUM(B$3:B3)

Format B4 as general and then copy formula down to B9

This will then give you a count of days in each week for any month. Just change the date in A1. If you always want the current month then use this formula in A1

=TODAY()-DAY(TODAY())+1

Note: of course weeks 2, 3 and 4 will always have 7 days in any month...

----------


## allana13

Hi

I was looking for a formula that automaticly calculates the no. of weeks in a period

For example if Cell A1 has 5 for period 5 Cell A2 should display 4 for no. of weeks

Any help on the above will be highly appreciated.

Many thanks

----------


## NBVC

Welcome to the forum,

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------


## allana13

Sorry about it

----------


## saintslayer

add an if statement should work:
=IF(MONTH(A2)=1,WEEKNUM(A2),WEEKNUM(A2-1)-WEEKNUM(A2-DAY(A2)-6))

----------


## ben_says

if your date is B2, in excel i believe a correct version is 

=IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))),1,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*1,2,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*2,3,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*3,4,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*4,5,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*5,6))))))

----------


## daddylonglegs

Wouldn't my suggestion give the same results?





> =INT((13-WEEKDAY(A1)+DAY(A1))/7)

----------


## data-geek

Simplicity is marvelous and often overlooked.
This formula works well, especially rolling over a new year!

THANK YOU!!!






> How about?
> 
> =INT((DAY(D61)-1)/7)+1

----------

