Number of the Week in Month

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

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

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

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

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

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.

Week Commencing

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

Cheers

Rob

Originally Posted by darkyam

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

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

Originally Posted by daddylonglegs

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

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

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

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

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

THANK YOU!!!

Originally Posted by NBVC

How about?

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