group of dates
 

Hi can anyone help?
i have a spreadsheet with a group of dates in one column and
corresponding values in the next column. some of the dates may be
duplicated if more than one entry is keyed the same day. is there a
way to make excel categorize the dates as dates of a particular week
and retrieve the corresponding information from the other column
including the duplicated dates?

Example : This is one week starting on a monday finishing on a sunday.
a b
29/12/2008 45
30/12/2008 6
31/12/2008 6
01/01/2009 12
01/01/2009 23
01/01/2009 34
02/01/2009 45
03/01/2009 56
04/01/2009 67


thanks

group of dates
 

Enter this in C1
=WEEKNUM(A1)
and copy down

This will give you the weeknum for a given date...in a given year.
You can then use SUMIF or COUNTIF etc to do what you want.

If year is improtant then you can have
=Year(A1) to get the year part

Or
=Year(A1) & WEEKNUM(A1)
to get numbers like
20091
200852
etc.

You will need to take care of 53rd week...

"Savio" wrote:

Hi can anyone help?
i have a spreadsheet with a group of dates in one column and
corresponding values in the next column. some of the dates may be
duplicated if more than one entry is keyed the same day. is there a
way to make excel categorize the dates as dates of a particular week
and retrieve the corresponding information from the other column
including the duplicated dates?

Example : This is one week starting on a monday finishing on a sunday.
a b
29/12/2008 45
30/12/2008 6
31/12/2008 6
01/01/2009 12
01/01/2009 23
01/01/2009 34
02/01/2009 45
03/01/2009 56
04/01/2009 67


thanks