grouping dates in current week

  • Thread starter Thread starter Soe
  • Start date Start date
S

Soe

Here is data

A 3-May
B 17-Mar
C 15-Apr
D 29-Apr

I hv tried with pivot table, to group into date, however, I can't make it
group it into weeks.
Secondly, I tried to get those dates fall into this week, or 7 days from
now(), again I don't know how to put today() into the pivot table.

anybody has any suggestion?
 
Hi
I would use a helper column in your data range to calculate these group
items. e.g.
Weeknumber:
=WEEKNUM(A1)
and copy this for all rows
 
To group the dates by week:

In the pivot table, right-click the Date field button
Choose Group and Show Detail> Group
From the list, choose Days
Set the number of Days to 7
If you want the weeks to start on a Monday, enter a Monday
date in the starting date box, e.g. 3/15/04
Click OK

To see all the weeks, including those for which there is no data:

Right-click the Date field button
Choose Field Settings
Add a check mark to the 'Show items with no data '
Click OK

You can't use the TODAY function in the pivot table, but you could add a
column to your data table, and calculate the dates there. If you want to
see data with a week of today's date, use a formula to the following,
where dates are in column A, and amounts are in column D:

=IF(ABS(TODAY()-A2)<=7,D2,0)

Add that field to the pivot table's data area, and it would show a sum
for dates within seven days of the current date.
 
Back
Top