Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 8
Question Group Dates by Week?

I have the below list of days, and numbers corresponding to each day. I am trying to group the dates into weeks, with a total number for each week instead of each day. Is there any way to accomplish this?

03-Jan-12 Count 168
04-Jan-12 Count 193
05-Jan-12 Count 175
06-Jan-12 Count 101
09-Jan-12 Count 119
10-Jan-12 Count 64
11-Jan-12 Count 82
12-Jan-12 Count 68
13-Jan-12 Count 45
16-Jan-12 Count 115
17-Jan-12 Count 33
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by AuditorLisa View Post
I have the below list of days, and numbers corresponding to each day. I am trying to group the dates into weeks, with a total number for each week instead of each day. Is there any way to accomplish this?

03-Jan-12 Count 168
04-Jan-12 Count 193
05-Jan-12 Count 175
06-Jan-12 Count 101
09-Jan-12 Count 119
10-Jan-12 Count 64
11-Jan-12 Count 82
12-Jan-12 Count 68
13-Jan-12 Count 45
16-Jan-12 Count 115
17-Jan-12 Count 33
Hi,

Have a look at the attached for one way of doing what I think you need.

You didn't stipulate which version of Excel you're using so I've included a SUMPRODUCT approach that will work on any version of Excel and a SUMIFS version that will work in 2007 or later.

Let me know if you need any of it explaining further.
Attached Files
File Type: zip AuditorLisa Example.zip (4.3 KB, 46 views)
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default Group Dates by Week?

On Fri, 8 Feb 2013 16:43:58 +0000, AuditorLisa wrote:


I have the below list of days, and numbers corresponding to each day. I
am trying to group the dates into weeks, with a total number for each
week instead of each day. Is there any way to accomplish this?

03-Jan-12 Count 168
04-Jan-12 Count 193
05-Jan-12 Count 175
06-Jan-12 Count 101
09-Jan-12 Count 119
10-Jan-12 Count 64
11-Jan-12 Count 82
12-Jan-12 Count 68
13-Jan-12 Count 45
16-Jan-12 Count 115
17-Jan-12 Count 33


You could use a pivot table.

Put headers in the row above your data (e.g. Dates | Counts)

Insert/Pivot table.

Drag dates to the row labels area
Drag Counts to the Values area.

Right click on some date and select "group"
Group by days and select '7' for the number of days.
Format to taste

--------------------
Row Labels Sum of Count
1/3/2012 - 1/9/2012 756
1/10/2012 - 1/16/2012 374
1/17/2012 - 1/18/2012 33
Grand Total 1163
  #4   Report Post  
Junior Member
 
Posts: 8
Unhappy

Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
On Fri, 8 Feb 2013 16:43:58 +0000, AuditorLisa wrote:


I have the below list of days, and numbers corresponding to each day. I
am trying to group the dates into weeks, with a total number for each
week instead of each day. Is there any way to accomplish this?

03-Jan-12 Count 168
04-Jan-12 Count 193
05-Jan-12 Count 175
06-Jan-12 Count 101
09-Jan-12 Count 119
10-Jan-12 Count 64
11-Jan-12 Count 82
12-Jan-12 Count 68
13-Jan-12 Count 45
16-Jan-12 Count 115
17-Jan-12 Count 33


You could use a pivot table.

Put headers in the row above your data (e.g. Dates | Counts)

Insert/Pivot table.

Drag dates to the row labels area
Drag Counts to the Values area.

Right click on some date and select "group"
Group by days and select '7' for the number of days.
Format to taste

--------------------
Row Labels Sum of Count
1/3/2012 - 1/9/2012 756
1/10/2012 - 1/16/2012 374
1/17/2012 - 1/18/2012 33
Grand Total 1163

I'm not sure why but it is not allowing me to "Group" when I right click a date in the pivot table. I had tried the approach before and ran into the same problem. I'm wondering if it is because the date is followed by "Count" since I previously subtotaled a larger sheet to extract this data? Would you have any suggestions as to how to make the grouping function work?
  #5   Report Post  
Junior Member
 
Posts: 8
Default

Quote:
Originally Posted by Spencer101 View Post
Hi,

Have a look at the attached for one way of doing what I think you need.

You didn't stipulate which version of Excel you're using so I've included a SUMPRODUCT approach that will work on any version of Excel and a SUMIFS version that will work in 2007 or later.

Let me know if you need any of it explaining further.
Thank you Spencer for your help! I'm afraid I'm not super experienced with SUMIF functions, but your approach seems good and I love a reason to learn something new! Thanks again for taking time to respond to my problem! :)


  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default Group Dates by Week?

On Mon, 11 Feb 2013 14:50:38 +0000, AuditorLisa wrote:

I'm not sure why but it is not allowing me to "Group" when I right click
a date in the pivot table. I had tried the approach before and ran into
the same problem. I'm wondering if it is because the date is followed by
"Count" since I previously subtotaled a larger sheet to extract this
data? Would you have any suggestions as to how to make the grouping
function work?


In order to group a column of dates in a pivot table, all of the entries in that column must be dates. If there are any text entries in the column, or dates that happen to be text rather than dates, you will not be able to group them.

You will need to clean up your data; perhaps you could just remove the subtotals, or filter them out.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert regular Date to Week Ending or Week Beginning Dates Sam H Excel Discussion (Misc queries) 5 April 3rd 23 04:39 PM
Pivot Table Group by Week? DTTODGG New Users to Excel 5 September 6th 07 01:40 AM
group sales by week and week number Wanna Learn Excel Discussion (Misc queries) 7 November 7th 06 12:44 PM
need to convert list of dates to count no. of dates by week neowok Excel Worksheet Functions 13 January 30th 06 04:54 PM
Pivot table - group dates per week or month digicat Excel Discussion (Misc queries) 1 January 8th 06 09:46 PM


All times are GMT +1. The time now is 12:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"