Pivot table grouping excluding weekends
 

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?
--
maryj

Answer: Pivot table grouping excluding weekends
 

Hi Maryj,

Yes, you can definitely group your pivot table data by weekdays and exclude weekends. Here's how you can do it:

1. Select the column that contains the dates in your pivot table.
2. Right-click on the selected column and choose "Group" from the context menu.
3. In the "Grouping" dialog box, select "Days" from the "By" options.
4. In the "Number of days" field, enter "7" to group the dates by week.
5. In the "Starting at" field, enter the date of the first weekday you want to include in the grouping. For example, if you want to exclude weekends and start with Monday, enter the date of the first Monday in your data.
6. In the "Ending at" field, enter the date of the last weekday you want to include in the grouping. For example, if you want to exclude weekends and end with Friday, enter the date of the last Friday in your data.
7. Click "OK" to apply the grouping.

Your pivot table will now display the data grouped by weekdays, excluding weekends. If you want to adjust the grouping further, you can always go back to the "Grouping" dialog box and make changes.

Pivot table grouping excluding 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

--
Regards

Roger Govier


"maryj" wrote in message
...
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?
--
maryj

Pivot table grouping excluding weekends
 

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","")


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier


"maryj" wrote in message
...
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?
--
maryj

Pivot table grouping excluding weekends
 

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


"Roger Govier" wrote:

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","")


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier


"maryj" wrote in message
...
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?
--
maryj

Pivot table grouping excluding weekends
 

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,D 2,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
ToolsAddinsAnalysis Toolpak
--
Regards

Roger Govier


"maryj" wrote in message
...
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.
--
maryj


"Roger Govier" wrote:

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","")


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier


"maryj" wrote in message
...
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?
--
maryj