Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group and count a list of dates | Excel Worksheet Functions | |||
Can't Group Dates in Pivot Table | Excel Discussion (Misc queries) | |||
Group Dates from SQL Query | Excel Discussion (Misc queries) | |||
Why aren't dates always available to group in pivot tables? | Charts and Charting in Excel | |||
PivotTables - Group Dates, excluding dates | Excel Discussion (Misc queries) |