Consulting

Results 1 to 8 of 8

Thread: Week No Formula - One more problem

  1. #1

    Week No Formula - One more problem

    Hi Mikey,

    i am getting jan 08 week1 as week 5 and jan 09 week 1 as week 5 and so on

    but i need week should start with monday and year start week should be week 1.

    Kindly help.

    Thanks
    Raj

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Oops!

    Greetings Raj, did you just start a new thread by accident?

    Mark

  3. #3

    Week No Formula - One More Problem

    Hi GTO, Greetings,

    It was an old thread posted by me and mikey replied with formula ="Week "&INT((DAY(A2-WEEKDAY(A2,3))+6)/7)
    ="Week "&INT((DAY(A2-WEEKDAY(A2,3))+6)/7)
    ="Week "&INT((DAY(A2-WEEKDAY(A2,3))+6)/7). here i am getting jan 08 week1 as week 5 and jan 09 week 1 as week 5 and so on

    but i need week should start with monday and year start week should be week 1.

    I met with accident so why long time no see.

    Regards
    Raj
    ="Week "&INT((DAY(A2-WEEKDAY(A2,3))+6)/7)

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    I am sorry about the accident and hope you are okay.

    I am not to slick on functions, but for the benefit of all, could you mention or better yet, insert a link to the original thread? You probably already have the workbook and/or more details there. I think this would probably help whoever gets to answer.

    Mark

  5. #5

    Week No Formula - One More Problem

    HI GTO

    please find the link of the old thread

    http://www.vbaexpress.com/forum/showthread.php?t=22367

  6. #6
    VBAX Regular
    Joined
    Aug 2008
    Posts
    18
    Location
    Hello Raj,

    I believe you wanted the weeks to continue from 1 month into the next, so that all days Monday to Friday would be in the same week. Are you saying that you want that to apply for all months except December to January?

    If we do it that way then 29th to 31st December 2008 are in week 5 but 1st and 2nd January 2009 are in week 1....and presumably week 2 for January 2009 would start on Monday 5th January 2009?

  7. #7

    Week No Formula - One More Problem

    Hi Mikey,
    You are exactly right. I want the jan 1st as week 1 and week 2 of jan 09 should start on 5th jan 09.

    Thanks
    Raj

  8. #8
    VBAX Tutor david000's Avatar
    Joined
    Mar 2007
    Location
    Chicago
    Posts
    275
    Location
    Quote Originally Posted by rajkumar
    Hi Mikey,

    i am getting jan 08 week1 as week 5 and jan 09 week 1 as week 5 and so on

    but i need week should start with monday and year start week should be week 1.

    Kindly help.
    [vba]
    Function WhichWeek(Cell As Range) As Long
    Dim Ref As Date, RefWeekDay As Long

    Ref = CDate(Year(Cell) & "/" & Month(Cell) & "/1")

    RefWeekDay = Weekday(Ref, vbMonday)

    WhichWeek = (Day(Cell) + RefWeekDay - 2) \ 7 + 1
    End Function

    [/vba]

    if A1 = 1/5/2009 then WhichWeek(A1) returns 2

    if A2 = 1/5/2009 then =INT((DAY(A2)+6)/7) returns 1

Posting Permissions

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