Get week number for month from date

[sujittalukde]

I want to calculate week number for a month based on a date and the weekday will start from Friday.
Suppose the date is 19/08/08 then the week will be 3
If date is 22/08/08 then week shall be 4
If date is 04/09/2008 then week shall be 1
If date is 11/09/2008 then week shall be 2

I tried to get the result by googling but not found any appropriate result.

Can some expert provided the formula ?
Thanks in advance.

 

[scot66]

Thanks for the quick reply. I knew there had to be an easier formula, and tried with =INT((6+DAY(A1)-Weekday(A1-5))/7) but it always did week 0 for first few days of month before a Friday. Not capturing the Weekday part within the DAY() was my undoing and your genius.

Thanks a lot.

 

[Bob Walker]

You can do that more easily with this formula =INT((6+DAY(A1+1-WEEKDAY(A1-5)))/7) The 5 indicates the week start day, 5 = Friday in this case - change to 1 to 7 for (Monday to Sunday)

Hello, Sorry to reply to such an old thread but this thread is the closest thing Ive come to seeing something that I need. I need a formula that will calculated the number of Thursdays in a month, and then tell me which one it is out of how many there are in the month. For example, if there are 5 Thursdays, in this month (5/7/2014) then tomorrow (Thursday) the equations should return 2/5. Can you help me with that? Thank you!

 

[ukmikeb]

Bob

Welcome to MrExcel Forum.

Try :-

=INT((6+DAY(A1+1-WEEKDAY(A1-4)))/7)&"/"&SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH(A1,-1)+1&":"&EOMONTH(A1,0))),2)=4))

hth

 

[barry houdini]

This formula will work for any date to show x/y where x is the number of instances of that day of the week so far in the month and y is the total number of instances of that day of week in the month

=INT((DAY(A1)+6)/7)&"/"&4+(DAY(A1+35-FLOOR(DAY(A1)+6,7))>7)

 

[Ashwini Bhaskar]

Hi,
I used this code to get the week number but still i am not getting how i expected =INT((6+DAY(A1+1-WEEKDAY(A1-6)))/7)

in my week calculation, week starts from Saturday and ends on Friday.

for example October 3 rd 2016 should be considered as September 5 week but using this calculation i am getting as oct 1 week.
Is there a way to includes dates to previous months