group of dates

S

Savio

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
 
S

Sheeloo

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF or vlookup formula 8
Week Number Format 4
SUMIF Formula Help 8
FORMULA NEEDED FOR MONTHS 1
Filtering by newest date 2
Duplicate Dates 1
Conditional Formatting Quartely Months 2
Calculating NetworkHours 5

Top