Number of weeks from a date

[Chaz1945]

Hi, I have a column of dates starting at March 1st 2005 which I want to call 'Week 1', March 8th -15th will be week 2, etc..
As the dates progress to the end of the year, can I use a formula to work out what is the week number for each date - the result must be a whole number ?? To complicate it a bit, the date also contains a time e.g. 01-Mar-2005 04:00 etc
This is how I would want it to look !

Time Stamp Week Number
01-Mar-2005 04:00 1
01-Mar-2005 07:00 1
etc...
10-Mar-2005 03:00 2
etc...

I've tried various date/time functions but don't have the skill, so any help would be much appreciated.

[oldchippy]

Try this in B2

=WEEKNUM(A2)-9

[robert111]

on a spare worksheet make a table with date time values in column 1 and week numbers in column2

1/1/06 00.01____________week1
8/1/06 00:01____________week2

(clearly a2=a1+7)

drag down as far as you require.

name this table "weeks"

now in your main spreadsheet, let us say A1 = 7/5/06 04:30

in B1 put =vlookup(A1,weeks,2)

this will return the week number.

Bob

[NBVC]

Try:

=WEEKNUM(A2)-WEEKNUM(DATE(2005,3,1))+1

where A2 houses first date.

Weeknum is an Analysis-Toolpak addin function.....Tools|Addins >Analysis Toolpak

[Chaz1945]

hi - it just comes up as #Name?

Chaz

[NBVC]

hi - it just comes up as #Name?

Chaz

As I mentioned...WEEKNUM() is an analysis toolpak addin function...


Go to Tools|Addins and select Analysis Toolpak....it should install (may ask for original CD)..

[Chaz1945]

Try:

=WEEKNUM(A2)-WEEKNUM(DATE(2005,3,1))+1

where A2 houses first date.

Weeknum is an Analysis-Toolpak addin function.....Tools|Addins >Analysis Toolpak

---- yes works fine , thanks a lot - I was thrown a bit at first as week 2 started on 6th/mar/2005 -only 5 days from start - but see that 6/3/2005 was a sunday - 1st day of the new week 2 !!

Cheers to all who helped me!!

[daddylonglegs]

Hi, I have a column of dates starting at March 1st 2005 which I want to call 'Week 1', March 8th -15th will be week 2, etc..
As the dates progress to the end of the year

What happens after the end of the year, do you want the week numbers to continue? Do they need to go back to 1 at some point?

If you want week 1 to start on 1st March 2005, as stated, then you possibly don't need WEEKNUM function, try

=INT((INT(A2)-DATE(2005,3,1))/7)+1

This formula will just keep incrementing the weeks, it never goes back to 1