Week No Formula - One more problem

[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.

Thanks
Raj

[GTO]

Oops!

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

Mark

[rajkumar]

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)

[GTO]

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

[rajkumar]

HI GTO

please find the link of the old thread

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

[Mikey]

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?

[rajkumar]

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

[david000]

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