# Excel PivotTable - Summarize data by Month or Day of the week

Posted on:  03/05/2021

If you need to find out what day of the week is the most profitable or what day of the week do we receive the most support calls, Excel's PivotTables can handle it. PivotTables will group dates automatically. If you have any date field - invoice dates, order dates, hire dates - Excel will group by year, quarter, and month for you. No formula's needed.

Image 1 is Excel starting on January 1. The data went to December 31. In Image 2, is Excel's PivotTable automatically grouping the months in column A.

If you want to group by day of the week, we can use the TEXT function in Excel and group by the day of the week. In this example, I use PivotTable and the Text function to find out what day of the week do my YouTube videos get the most watch time in hours.

## TEXT Function in Excel

The TEXT function returns a number or date in a given number format, as text. I use the text function to get the date formatted as a three-letter date in the video. Example: 1/1/2020, which is a Wednesday, I showed as Wed.

## TEXT Function Arguments

The text function has two arguments. Both arguments are required.

=TEXT(Value,Format Text)

• value - The number to convert.
• format_text - The number format to use.

## TEXT Function Examples

Below are some TEXT function examples for dates. Month is the letter "M", day is the letter "D", and year is the letter "Y".

We are going  to use the month of September 2021 in our example

### The letter D

Date d dd ddd dddd
9/1/2021 =Text(A2,"d") would return 1 =Text(A2,"dd") would return 01 =Text(A2,"ddd") would return Wed =Text(A2,"dddd") would return Wednesday

### The letter M

Date d dd ddd dddd
9/1/2021 =Text(A2,"m") would return 1 =Text(A2,"mm") would return 01 =Text(A2,"mmm") would return Sep =Text(A2,"mmmm") would return September

## Create a PivotTable

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

1. Click inside your data. Make sure your data doesn't have any blank rows or columns and only a single header row.
2. Click the Insert tab and select PivotTable.
3. Your Table/Range will be selected. Make sure it is correct. You can make the PivotTable in a new worksheet or existing worksheet. To create the PivotTable on a new worksheet click OK.

The PivotTable Fields pane appears on the left. There are four areas:

• Filters
• Columns
• Rows
• Values