vba to find the month and week number from given date

I'm not sure about the solution for the week problem, but at least for the month column, you don't need VBA at all, just a quick function.

For example, you have 4/3/2009 in the A2 cell. Here's the code for your month column.

Please Login or Register  to view this content.

That will return the "serial number" of the month, otherwise known as the number 4 to us common folk.

As for your week problem, I'm unsure. There is a function in Excel called WEEKNUM that will tell you the week of the year, but since the number of weeks in a month change, I'm not sure how to make a formula out of it. If I can think of a solution, I'll post here again.

Hope this helps!

Originally Posted by bandgeekndb

I'm not sure about the solution for the week problem, but at least for the month column, you don't need VBA at all, just a quick function.

For example, you have 4/3/2009 in the A2 cell. Here's the code for your month column.

Please Login or Register  to view this content.

That will return the "serial number" of the month, otherwise known as the number 4 to us common folk.

As for your week problem, I'm unsure. There is a function in Excel called WEEKNUM that will tell you the week of the year, but since the number of weeks in a month change, I'm not sure how to make a formula out of it. If I can think of a solution, I'll post here again.

Hope this helps!

Hi

Thank for help . i am able to get the month but problem is that i am not able to get the week no. within the month

hi

please help

Originally Posted by JBeaucaire

How about this formula in the WEEK column starting at C2, then copy down:

=CEILING((DAY(A2)+WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),2))/7,1)

HI

Thnk for giving me the formula to calculate the week no. but my problem is that

week is defined from Monday to sunday for example like in month of april09
the date 6-april-09 Week number is 2 as week start from monday. The above formula return the week no. for 6-april-09 is 1

Originally Posted by JBeaucaire

And since this is the programming forum and you originally wanted a VBA solution, here's the same approach imbedded into a macro:

Please Login or Register  to view this content.

HI

THE Above code is work but still this give the week number of any month starting from first date of the month my problem is that i need a week number 1 only if the month start date is of Monday

Like Take a eample of Month April-09

1April to 5April - Week 5 ( 'coz the week start from Wednesday)
6 Apr to 12 April - Week1 ('coz Week start from Monday)

In short the Week1 for week satrt frm Monday to Sunady
Week2 for Next Monady to sunday and so on for each month

Originally Posted by JBeaucaire

So if the first of the month starts on a Tuesday, those first 6 days AREN'T week1, they're week?

April 1 - April 5 - Week?
April 6 - April 12 - Week1
April 13 - April 19 - Week2
April 20 - April 26 - Week3
April 27- April30 - Week4

Yes you r wright they are week but that week is come under the prior month week in above case April1 - April5 is come under the Week5 of month March

Originally Posted by JBeaucaire

Ok, maybe this?

Please Login or Register  to view this content.

Hi
Thanks now with the help of above code i can get the Week no. correctly
One more question is that also possible the with the help of code i also get the correct month

example Like

1April 09 - Week5( This i easily get after running the above code)

but for month column is that possible as this week 5 is not come under the Month April. so in month it will automatically goes under the month of march i.e in month column the code show the March-09 and the same will happened for all the date

Originally Posted by JBeaucaire

Once more:

Please Login or Register  to view this content.

These are the worksheet formulas being used and then wiped:

Month:
=IF(AND(C2=5,DAY(A2)<29),MONTH(A2)-1,MONTH(A2))

Week#:
=IF(WEEKDAY(DATE(YEAR($A2),MONTH($A2),1),2)=1,LOOKUP(7+DAY($A2)-WEEKDAY($A2,2),{0,8,15,22,29},{1,2,3,4,5}),LOOKUP(7+DAY($A2)-WEEKDAY($A2,2),{0,8,15,22,29,36},{5,1,2,3,4,5}))

Thank you very much for provide me the right solution . Now with the help of above code i easily get the Week & Month

Once again thank you for your hep

Originally Posted by JBeaucaire

Month:
=IF(AND(C2=5,DAY(A2)<29),MONTH(A2)-1,MONTH(A2))

Week#:
=IF(WEEKDAY(DATE(YEAR($A2),MONTH($A2),1),2)=1,LOOKUP(7+DAY($A2)-WEEKDAY($A2,2),{0,8,15,22,29},{1,2,3,4,5}),LOOKUP(7+DAY($A2)-WEEKDAY($A2,2),{0,8,15,22,29,36},{5,1,2,3,4,5}))

Hello Anil/JB

I see potential problems for both of these fomulas......

The first one will return zero for some January dates, presumabaly that should be 12, I suggest

=MONTH(A2-WEEKDAY(A2,3))

where your date is in A2

For the week number within the month I note that the above [JB] formula returns 4 for Thursday 30th April 2009 but 5 for the next day, Friday 1st May 2009. I know this is a new month but shouldn't the week number stay the same throughout the week? I suggest this formula to get the week number in the month:

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

With this formula week 1 always starts on the first Monday of the month, all weeks run for 7 days.

regards
daddylonglegs