PDA

View Full Version : Solved: Week No Formula



rajkumar
09-20-2008, 03:52 PM
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
09-20-2008, 04:34 PM
If you are going to use a crude algorithm like that, then just

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

rajkumar
09-20-2008, 05:03 PM
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
09-20-2008, 08:52 PM
Do you mean the word "Week" and the number?
="Week"& " "&WEEKNUM(A2)

Bob Phillips
09-21-2008, 12:46 AM
="Week "&INT((DAY(A2)+6)/7)

rajkumar
09-21-2008, 03:10 AM
Thx Xld,:friends:

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

Cheers! :beerchug:

Raj

rajkumar
09-21-2008, 03:35 AM
one more question,

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

also H1,H2 (Half year = 6 months)

Raj

Bob Phillips
09-21-2008, 08:53 AM
="Q"&INT((MONTH(A2)+2)/3)

rajkumar
09-21-2008, 10:48 AM
Hip Hip Hurrey !:thumb
Raj

MaximS
09-21-2008, 02:54 PM
="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
09-22-2008, 09:00 AM
="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
11-04-2008, 03:35 AM
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 :think:

Mikey
11-04-2008, 06:32 AM
Hello Raj, try this formula:

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

rajkumar
11-04-2008, 11:50 PM
Hi Mikey,

U r Amazing THX
Raj :friends: