Calculating Weeks of the Month starting before the 1st

[rocksolid77]

Hi All,

I've been scouring the internet to try to find something and so far have come up short. I found a very good reference in terms of date functions at http://www.cpearson.com/excel/WeekNumbers.aspx
and
http://www.cpearson.com/excel/DateTimeWS.htm#NthDoW

but it's not quite what I'm looking for. I'm really hoping I'm not asking too much.

Here's what I'd like to do, I'd like to be able to input a date into excel and have it return which week of the month that would fall under. Here's where it gets a little tricky, to stay in line with the the rest of my organization I'd need for weeks to always begin on a Sunday. So far so good... Where it gets tricky is that according to our system Week 1 of the month could potentially begin before the 1st of the month. Ex. Dec. 1st, 2011 falls on a Thursday so week 1 of December would start the previous Sunday, ie: Nov 27th 2011. So basically, day 1 of Week 1 of the month will always be the first Sunday of the week containing the 1st of the month regardless of what day the 1st falls on.

If possible I'd like this to be an in-cell calculation but I'll use VBA if need be.

I feel like I'm asking for a lot but any help will be greatly appreciated!

 

[RonB1111]

Try this formula (assumes input date is in cell A1):

=IF(DATE(YEAR(A1),MONTH(A1)+1,0)-A1+WEEKDAY(A1)<7,-4,0)+ROUNDUP((A1-(DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),1)))/7,0)

The IF statement determines if input date falls in last partial week of a month and reduces week count by 4 if it does. The Roundup calcs # of days between the input date and the 1st Sunday of week 1 of the month & divides by 7 days and rounds up to determine week # of the date entered.
<table width="64" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="64"></colgroup><tbody><tr height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">
</td></tr></tbody></table>

 

[barry houdini]

....So basically, day 1 of Week 1 of the month will always be the first Sunday of the week containing the 1st of the month regardless of what day the 1st falls on......

If 1st of the month is a Saturday then will week 1 begin on previous Sunday.....and week 2 begin on 2nd?

If so try this formula for week number, assuming date in A2

=INT((6+DAY(A2-WEEKDAY(A2)+7))/7)

format as general

 

[rocksolid77]

If 1st of the month is a Saturday then will week 1 begin on previous Sunday.....and week 2 begin on 2nd?

Yes, that's exactly right Barry.

As you can imagine I've been off during the holidays so I haven't had the time to try these out just yet. Just wanted to take the time to thank you gentlemen for your replies. Will update soon.

 

[rocksolid77]

Your formula works perfectly! Thanks so much for the help. I just have on more small question for you. Currently I have my data set up as follows

Column A - Date - (Cell Format mm/dd/yy h:mm AM/PM)
Column B - =A1, A2 etc. - (Cell Format MMM)
Column C - = Your formula - (Cell Format General)

-- removed inline image ---


What I would like is for B to give me the Month in relation to C... ie: Although A1 is 28/11/2011 i would like for B1 to return Dec since it is the first week of Dec for C1. Is this possible? You guys have been supremely helpful thus far and I thank you for that.

Sorry i couldn't post a better example, many of these checklists are confidential.

 

[barry houdini]

Try this for B2

=TEXT(A2-WEEKDAY(A2)+7,"mmm")

 

[rocksolid77]

I sit in awe of your Excel wizadry. Barry, I just can't thank you enough, you are totally deserving of the title MVP. Thanks so much!

 

[Ver101]

is the weeknum function does 'nt give you the same result?