Converting Dates to Weeks

[Guest]

Hey All,

I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.
Hope someone can help.


Kurt

 

[Don Guillett]

have a look in the help index for WEEKNUM

 

[Guest]

Look at WEEKNUM function.

 

[Special-K]

Decide what you consider your start date to be

Heres what i did

enter a start date in A1 (01/07/2006)

in B1 enter =A1 then format it as a number (38899)

so to turn that into week numbers
I just retyped into B1
=INT((A2-38899)/7)+

 

[nsv]

Oh, but that formula exists and it is called WEEK.
Write your date in A1 and be sure to have the format so that i
interpreted as a date. If you want to be sure just write 38871 which i
the value for 20-07-2006
Then try this in B1: ="Week "&WEEK(A1)

Just one important thing: Americans regard week 1 to be the remainin
of the week containing January 1'st, so week 1 is normalkly shorte
than 7 days.
In Europe week 1 is the first week in a year if January 1'st i
Thursday or earlier; else it is called week 53.
This makes the counts differ in America and the rest of the world an
Excel uses the american way

 

[Guest]

Each of the suggestions worked. What if I wanted to limit to the week number
for a particular month. For example if 1/1/06 will be in week1 in Jan-06 and
28/1/06 will be week4 in Jan-06? How can I achieve this.

Kurt

 

[Guest]

Do you mean that each month will start at week 1?

 

[Don Guillett]

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

 

[Guest]

Yes. each month to start at week 1.

Kurt

Do you mean that each month will start at week 1?

 

[David Biddulph]

Each of the suggestions worked. What if I wanted to limit to the week
number
for a particular month. For example if 1/1/06 will be in week1 in Jan-06
and
28/1/06 will be week4 in Jan-06? How can I achieve this.

I don't know where nsv finds his WEEK() function, as it doesn't seem to be
an Excel function, but for your revised question, try
=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1))+1

 

[nsv]

It is WEEKNUM allright, not WEEK - I was wrong there. My version of
Excel is in my national language (very annoying, but they will not give
me an english version), so I cannot always check the syntax

NSV