Get week number for month from date

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Get week number for month from date

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Location
    India
    Posts
    520
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 ?
    Thanks in advance.

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,671
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Jun 2007
    Location
    India
    Posts
    520
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get week number for month from date

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

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,671
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Jun 2007
    Location
    SEOUL. KOREA
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    ABCDEF
    DateWeek'sNumWeek'sNumWeek'sNumOne
    Shoot
    Weekday
    in One Yearin Month/1in His Month
    2007-12-285248556
    2007-12-295248557
    2007-12-305348661
    2007-12-315348662
    2008-01-0111113
    2008-01-0211114
    2008-01-0311115
    2008-01-0411116
    2008-01-0511117
    2008-01-0621221
    2008-01-0721222
    2008-01-2751551
    2008-01-2851552
    2008-01-2951553
    2008-01-3051554
    2008-01-3151555
    2008-02-0155116
    2008-02-0255117
    2008-02-0365221
    2008-02-0465222
    2008-02-0565223
    2008-02-0665224
    2008-02-2285446
    2008-02-2385447
    2008-02-2495551
    2008-02-2595552
    2008-02-2695553
    2008-02-2795554
    2008-02-2895555
    2008-02-2995556
    2008-03-0199117
    2008-03-02109221
    2008-03-03109222
    2008-03-04109223
    2008-03-05109224






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

    How about this suggest?
    How to use well the Excel
    I want know...
    ps. Sheet Formula to WEB DownLoad Sheet2Web can use

  6. #6
    Board Regular
    Join Date
    Jun 2007
    Location
    India
    Posts
    520
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get week number for month from date

    Thanks to duo for the reply and clarification.

  7. #7
    Board Regular pedie's Avatar
    Join Date
    Apr 2010
    Location
    INDIA
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get week number for month from date

    Quote Originally Posted by barry houdini View Post
    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
    Quote Originally Posted by barry houdini View Post

    =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

    Please advice.
    Regards,
    Pedie
    MS OFFICE 2016/EXCEL 2016: Window7: 64BIT
    Visit YouTube.com/VBAa2z


  8. #8
    New Member
    Join Date
    Sep 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: Get week number for month from date

    Quote Originally Posted by pedie View Post

    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

    Please advice.
    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. #9
    New Member
    Join Date
    Sep 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb 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. #10
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,671
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com