Thread: Need to find the week number of the month, first of the month not always the 1st week

I need a formula that will find the week number of a month. Sometimes, there are 5 weeks in a month and I need the formula to be able to adapt to that. I want to take the data out of one cell that has a date in it. My workplace has Excel 2003, and I can't use any macros or add-ins. The formula needs to be made from existing formulas in Excel 2003.

For example: The date I am referencing for the formula is January 1, 2012. I need the formula to reflect that this is week 1, but just the number, not the word week. If I were to reference the same cell that originally had January 1st in it with a different date, say February 29, 2012, then I need the formula to display that this is week 5.

I define the first or last week of a month by whatever month has the most days in the week. For January of this year - the 29th, 30th, and 31st are in the first week of February because February has 4 out of 7 days in the week.

Hi Jerse.. Welcome to the forum.
This is a formula by Daddylonglegs & appears to do exactly what you're after

=IF(A1="","",INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4))/7)+2)

If the formula's in B1 & you enter a date in A1, B1 will show the week number
Have fun

hey philb1,

thanks for the response and the welcome! however, i'm looking for a formula that will only display the numbers 1-5 for the week numbers. having the week number of a particular month, not the year, is what i'm trying to get at. i'd also like the formula to understand that the first day of the week is sunday.

another example: february 1st, 2012 - which falls on a wednesday - is in week 1 of february, while march 1st, 2012 - which falls on a thursday - is considered to be in week 5 of february because the last days of february take up 4/7 days of the week while the first days of march only take up 3/7 days of the same week.

Perhaps this link could be of help?

http://office.microsoft.com/en-us/ex...005209337.aspx

Alf

her alf,

thanks, but the weeknum function in excel 2003 is only available by using the add-in.

ive literally been thinking about how to do this for the past few hours but can't get around it without creating an additional table with all the dates and week numbers all ready inside of it...

Thanks, this worked great.

For those of you who use this afterwards, I used the A1 value on the Wednesday of the week so that the formula knows which month has the greater number of days in the either week 5/ week 1

Originally Posted by daddylonglegs

Try this formula

=INT((6+DAY(A1-WEEKDAY(A1)+4))/7)