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

have a look in the help index for WEEKNUM

--
Don Guillett
SalesAid Software

"Kurt" wrote in message
...
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

Look at WEEKNUM function.

"Kurt" wrote:

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

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)+1


--
Special-K


------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=563275

Oh, but that formula exists and it is called WEEK.
Write your date in A1 and be sure to have the format so that is
interpreted as a date. If you want to be sure just write 38871 which is
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 remaining
of the week containing January 1'st, so week 1 is normalkly shorter
than 7 days.
In Europe week 1 is the first week in a year if January 1'st is
Thursday or earlier; else it is called week 53.
This makes the counts differ in America and the rest of the world and
Excel uses the american way.


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=563275

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

"nsv" wrote:


Oh, but that formula exists and it is called WEEK.
Write your date in A1 and be sure to have the format so that is
interpreted as a date. If you want to be sure just write 38871 which is
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 remaining
of the week containing January 1'st, so week 1 is normalkly shorter
than 7 days.
In Europe week 1 is the first week in a year if January 1'st is
Thursday or earlier; else it is called week 53.
This makes the counts differ in America and the rest of the world and
Excel uses the american way.


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=563275

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

"Kurt" wrote:

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

"nsv" wrote:


Oh, but that formula exists and it is called WEEK.
Write your date in A1 and be sure to have the format so that is
interpreted as a date. If you want to be sure just write 38871 which is
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 remaining
of the week containing January 1'st, so week 1 is normalkly shorter
than 7 days.
In Europe week 1 is the first week in a year if January 1'st is
Thursday or earlier; else it is called week 53.
This makes the counts differ in America and the rest of the world and
Excel uses the american way.


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=563275

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

--
Don Guillett
SalesAid Software

"Kurt" wrote in message
...
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

"nsv" wrote:


Oh, but that formula exists and it is called WEEK.
Write your date in A1 and be sure to have the format so that is
interpreted as a date. If you want to be sure just write 38871 which is
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 remaining
of the week containing January 1'st, so week 1 is normalkly shorter
than 7 days.
In Europe week 1 is the first week in a year if January 1'st is
Thursday or earlier; else it is called week 53.
This makes the counts differ in America and the rest of the world and
Excel uses the american way.


--
nsv
------------------------------------------------------------------------
nsv's Profile:
http://www.excelforum.com/member.php...o&userid=26500
View this thread:
http://www.excelforum.com/showthread...hreadid=563275

Yes. each month to start at week 1.

Kurt

"Toppers" wrote:

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

"Kurt" wrote:

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

"nsv" wrote:


Oh, but that formula exists and it is called WEEK.
Write your date in A1 and be sure to have the format so that is
interpreted as a date. If you want to be sure just write 38871 which is
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 remaining
of the week containing January 1'st, so week 1 is normalkly shorter
than 7 days.
In Europe week 1 is the first week in a year if January 1'st is
Thursday or earlier; else it is called week 53.
This makes the counts differ in America and the rest of the world and
Excel uses the american way.


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=563275

"Kurt" wrote in message
...
"nsv" wrote:


Oh, but that formula exists and it is called WEEK.
Write your date in A1 and be sure to have the format so that is
interpreted as a date. If you want to be sure just write 38871 which is
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 remaining
of the week containing January 1'st, so week 1 is normalkly shorter
than 7 days.
In Europe week 1 is the first week in a year if January 1'st is
Thursday or earlier; else it is called week 53.
This makes the counts differ in America and the rest of the world and
Excel uses the american way.

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
--
David Biddulph

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


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=563275