Pivot table grouping excluding weekends

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The data has a field called "Date". In the pivot table, the grouping is set
to display the dates in 3 day groups. Is there any way to do this type of
grouping but only use the weekdays and exclude the weekends?
 
Hi Mary

Add an extra column to your source data, titled Weekday
In this column enter
=IF(WEEKDAY(A2)<6,"Y","")
Change A2 to the column containing your dates and copy down

Extend the data range in your PT to include this extra column.
Drag the Weekday field to the Page area and from the dropdown Select Y
 
Hi Mary

I'm sorry, I meant to make the base of 1 = Monday for Weekday.
Change the formula to
=IF(WEEKDAY(A2,2)<6,"Y","")
 
All of the dates in the raw data are weekdays - no weekend dates. However,
when grouping, you have to select the number of days to group by. For
example, the group by is set to every 3 days. It creates a group for Sept
4-6, then the next group would be Sept 7-9 - the 9th is a Saturday and there
is actually no data for Saturday but the grouping just uses the calendar
days, it doesn't look to see if there is data for a date or not. We don't
think there really is any way around this but just wondering if we missed
anything.
 
Hi Mary

Because there are only 5 weekdays, then grouping into 3's is always
going to give a problem.
Your extra column, could be just set to =DAY(A2) and you could group by
that, but sometimes there will be 3 days in a group and sometimes 2.

Maybe if you input a 1 in cell D2 and in cell D3
=IF(A3=$A$2,D2,IF(MOD(NETWORKDAYS($A$2,A3),3)>=1,D2,D2+1))
and copy down
This will place a group number in column D which will be *mainly* 3 day
periods.
Drag this to the Row area of your PT and do not Group it.

For Networkdays to work, you must have the Analysis Toolpak installed
Tools>Addins>Analysis Toolpak
--
Regards

Roger Govier


maryj said:
All of the dates in the raw data are weekdays - no weekend dates.
However,
when grouping, you have to select the number of days to group by. For
example, the group by is set to every 3 days. It creates a group for
Sept
4-6, then the next group would be Sept 7-9 - the 9th is a Saturday and
there
is actually no data for Saturday but the grouping just uses the
calendar
days, it doesn't look to see if there is data for a date or not. We
don't
think there really is any way around this but just wondering if we
missed
anything.
Click to expand...
 
Back
Top