Solved: Week No Formula

[rajkumar]

Hi Experts,

I want to get week no from a given date. For Example : In Jan 08 there are 5 weeks, so the formula should return week 1 or week 2 or week 5 etc from the given date.

Currently i am vlooking from a table which i created manually.(attachment)

Can anyone help on this please?

Raj

[Bob Phillips]

If you are going to use a crude algorithm like that, then just

=INT((A2-DATE(YEAR(A2),1,0)+6)/7)

[rajkumar]

Let me explain further, there are week 1 and week 2 so on in every month.

The formula sought should return like " Week 1 " from a given date.

Raj

[david000]

Do you mean the word "Week" and the number?
="Week"& " "&WEEKNUM(A2)

[Bob Phillips]

="Week "&INT((DAY(A2)+6)/7)

[rajkumar]

Thx Xld,

cudos to you. that's exactly what i was looking for.

Cheers!

Raj

[rajkumar]

one more question,

Q1,Q2,Q3,Q4 - Can it be achived similarly? (Quarter = 3months).

also H1,H2 (Half year = 6 months)

Raj

[Bob Phillips]

="Q"&INT((MONTH(A2)+2)/3)

[rajkumar]

Hip Hip Hurrey !
Raj

[MaximS]

="H"&INT((MONTH(A2)+5)/6)

for two halfs but anyway that's 100% xld solution
I glad to see that thread because I've learnt something new :

[Mikey]

="Week "&INT((DAY(A2)+6)/7)

This formula will make the first 7 days of each month week 1, if you want week 1 to start on 1st of the month and then week 2 on the subsequent Sunday, which is more like your example, then try this formula:

="Week "&INT((13+DAY(A2)-WEEKDAY(A2))/7)

although I note that you have 1st March as week 5, is that correct?

[rajkumar]

Hi,

This formula is making the first 7 days of each month week 1, but i don't want week 1 to start on 1st of the month and then week 2 on the subsequent Sunday.

="Week "&INT((13+DAY(A2)-WEEKDAY(A2))/7)

If a month is ending between a weekday then the start days of the subsequent month for that week also to be considered as last week of the month. Ex : 30-jan-08 is wednesday and 01-feb-08 is thursday. What I want is 2nd feb, 3rd feb 4th Feb are also to be considered as week 5 of january.

Monday should be considered as a start day of a week.

Please help me with a formula
Regards
Raj

[Mikey]

Hello Raj, try this formula:

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

[rajkumar]

Hi Mikey,

U r Amazing THX
Raj