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 ...
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 ...
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,
Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.
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
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
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!
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
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........
Audere est facere
@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!
Last edited by squiggler47; 12-22-2010 at 04:47 PM. Reason: aded th at the end may 2010 had 6 weeks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks