PDA

View Full Version : Week No Formula - One more problem



rajkumar
11-25-2008, 12:20 AM
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 :doh:

GTO
11-25-2008, 12:26 AM
Oops!

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

Mark

rajkumar
11-25-2008, 12:49 AM
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
11-25-2008, 01:15 AM
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
11-26-2008, 06:16 AM
HI GTO

please find the link of the old thread

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

Mikey
11-26-2008, 09:24 AM
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
11-27-2008, 01:41 AM
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
11-27-2008, 12:49 PM
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.

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



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

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