date classification

[cyberuser]

hello !


I'm able to classify the weeks from certain date as 1st week of JAN or 2nd week of DEC or 3rd week of APR.. etc ?

date format day/month/year

example:

15/10/2010 3rd week of OCT
30/08/2010 5th week of AUG ...

[ConneXionLost]

This depends on several things.

What day does your week start? i.e. Sunday? Monday?

What do you consider the first week of the month? i.e. If 01 April is a Thursday, is it in the first week of April?

If might help to start here:

Week numbers

Cheers,

[Richard Buttrey]

Hi,

What's the rule for determining the 1st day of the week. i.e. why is 15th Oct the 3rd week and 30 Aug the 5th week. Is Monday the first day you count from?

Regards

[squiggler47]

perhaps :-

=(INT((A1-(DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),3))-1)/7)+1)&MID("stndrdthth",(INT((A1-(DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),3))-1)/7))*2+1,2)&" week of "&TEXT(A1,"mmm")

Assuming Monday is the start of the week!

(you also need analasys toopack installed if using excel other than 2007/2010)

it would help if you put your excel version in your profile!

[daddylonglegs]

I agree with the previous comments, how do you define the weeks? December 2010 started on a Wednesday, when does week 1 start? When does week 2 start?

Assuming that the first 7 days are always in week 1 and the next 7 are in week 2 etc. then this formula would give you the week number for a date in A1

=INT((DAY(A1)+6)/7)

Darren, your formula gives week 1 for 6th December (Monday) but week 2 for the next day so I don't think that's right........

[squiggler47]

@daddylonglegs

Yes you are correct, I MODified the formula ;-)

=(INT((A1-(DATE(YEAR(A1),MONTH(A1),1)-MOD(DATE(YEAR(A1),MONTH(A1),1)-1,7))-1)/7)+1)&MID("stndrdththth",(INT((A1-(DATE(YEAR(A1),MONTH(A1),1)-MOD(DATE(YEAR(A1),MONTH(A1),1)-1,7))-1)/7))*2+1,2)&" week of "&TEXT(A1,"mmm")

It should be correct now!