Get week number for month from date

Thanks:  0
Likes:  0

# Thread: Get week number for month from date

1. ## Get week number for month from date

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 ?

2. ## Re: Get week number for month from date

Presumably the 1st of the month is always in week 1 and then week 2 starts from the first Friday after the 1st? If so then with date in A1 try this formula for the week number

=INT((13+DAY(A1)-WEEKDAY(A1-5))/7)

3. ## Re: Get week number for month from date

Thanks barry. its working correctly. would you kindly explain the logic of the formula?

4. ## Re: Get week number for month from date

I sometimes use this formula to calculate the number of Fridays between a date in B1 and a later date in C1

=INT((8+WEEKDAY(C1-5)+C1-B1)/7)

[the 5 determines the day to count 1 = Mon through to 7 = Sun]

In this case what you want equates to a count of Fridays in the period ending with your date and starting 6 days before the start of the month so

C1 = A1
B1 = A1-DAY(A1)-5

so if we do the replace we get

=INT((8+WEEKDAY(A1-5)+A1-A1+DAY(A1)+5)/7)

which becomes the formula I posted, once tidied up

5. ## Re: Get week number for month from date

Hi ! Here Suggest to you

DataSheet= Sheet1
 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
 A B C D E F Date Week'sNum Week'sNum Week'sNum One Shoot Weekday in One Year in Month/1 in His Month 2007-12-28 52 48 5 5 6 2007-12-29 52 48 5 5 7 2007-12-30 53 48 6 6 1 2007-12-31 53 48 6 6 2 2008-01-01 1 1 1 1 3 2008-01-02 1 1 1 1 4 2008-01-03 1 1 1 1 5 2008-01-04 1 1 1 1 6 2008-01-05 1 1 1 1 7 2008-01-06 2 1 2 2 1 2008-01-07 2 1 2 2 2 2008-01-27 5 1 5 5 1 2008-01-28 5 1 5 5 2 2008-01-29 5 1 5 5 3 2008-01-30 5 1 5 5 4 2008-01-31 5 1 5 5 5 2008-02-01 5 5 1 1 6
 2008-02-02 5 5 1 1 7 2008-02-03 6 5 2 2 1 2008-02-04 6 5 2 2 2 2008-02-05 6 5 2 2 3 2008-02-06 6 5 2 2 4 2008-02-22 8 5 4 4 6 2008-02-23 8 5 4 4 7 2008-02-24 9 5 5 5 1 2008-02-25 9 5 5 5 2 2008-02-26 9 5 5 5 3 2008-02-27 9 5 5 5 4 2008-02-28 9 5 5 5 5 2008-02-29 9 5 5 5 6 2008-03-01 9 9 1 1 7 2008-03-02 10 9 2 2 1 2008-03-03 10 9 2 2 2 2008-03-04 10 9 2 2 3 2008-03-05 10 9 2 2 4

 Used Formula ...(With Running MicrosoftExcel Ver 2003) No Addr' If use below Formula, You'll Get Result as Right Result Formula's 1 B3 =WEEKNUM(A3) 52 2 B3 His Formula Used This Cell -> B3:B37 3 C3 =WEEKNUM(TEXT(A3,"yyyy-mm-")&1)*1 48 4 C3 His Formula Used This Cell -> C3:C37 5 D3 =B3-C3+1 5 6 D3 His Formula Used This Cell -> D3:D37 7 E3 =WEEKNUM(A3)-(WEEKNUM(TEXT(A3,"yyyy-mm-")&1)*1)+1 5 8 E3 His Formula Used This Cell -> E3:E37 9 F3 =WEEKDAY(A3) 6 10 F3 His Formula Used This Cell -> F3:F37 How about this suggest?

6. ## Re: Get week number for month from date

Thanks to duo for the reply and clarification.

7. ## Re: Get week number for month from date

Originally Posted by barry houdini
Presumably the 1st of the month is always in week 1 and then week 2 starts from the first Friday after the 1st? If so then with date in A1 try this formula for the week number
Originally Posted by barry houdini

=INT((13+DAY(A1)-WEEKDAY(A1-5))/7)

Sorry to bring up old thread but what does 13 do here?
If 5 is for friday and 7 for the week number from 1 - 7

8. ## Re: Get week number for month from date

Originally Posted by pedie

Sorry to bring up old thread but what does 13 do here?
If 5 is for friday and 7 for the week number from 1 - 7

13 is to ensure the calculated day dived by 7 is between 1 and 2 so the INT returns 1 for the first week found.

9. ## Re: Get week number for month from date

Thanks to the original poster, but the problem I had was to deduce the week number including weeks numbers carried over from the previous month. ie If the first of the month was a Tuesday, then Tue 1st, Wed 2nd & Thu 3rd would be either week 4 or week 5 of the previous month, then Fri 4th would be week 1.

Without the original post I may not have solved this but I finally did, building on the same principle above.

=IF(INT((13+DAY(A1)-WEEKDAY(A1-5))/7)-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))=6,0,1)=0,INT((13+DAY(DATE(YEAR(A1),MONTH(A1),1)-1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)-1-5))/7)-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,1))=6,0,1),INT((13+DAY(A1)-WEEKDAY(A1-5))/7)-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))=6,0,1))

10. ## Re: Get week number for month from date

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)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•