Converting Dates to Weeks

K

Kurt

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
 
S

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

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
 
K

Kurt

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
 
D

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.
Click to expand...

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
 
N

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
 
Top