Excel PivotTable - Calculate the number of days of each week for every month of the year
Posted on: 05/05/2022
If you want to calculate how many days are in each month for a year, it is easy to do with a PivotTable in Excel. I want to know how Sundays through Saturdays there are for each month of the year.

Excel PivotTable - Show days per month for the year
We should end up with 365 days unless the year is a leap year. We are counting weekdays and weekends. This video will show the functions WEEKDAY, MONTH, TEXT, and FORMULATEXT. We will create a PivotTable and Sort by Month in a PivotTable.
YouTube Video
Video Chapters
-
00:00 Introduction
-
00:53 Preparing the worksheet
-
01:51 GO TO
-
02:27 SELECT GO TO
-
02:45 FILL DOWN
-
03:53 WEEKDAY
-
05:07 Long date
-
05:51 MONTH
-
06:45 Number to day of week
-
08:15 Number to month
-
09:11 Create PivotTable
-
10:34 Sort by month
-
11:30 Closing remarks
WEEKDAY Function

WEEKDAY Function in Excel
The WEEKDAY Function return the number of the day. Sunday is 1, Monday is 2, etc.
TEXT Function

Text function in Excel
The TEXT function in Excel has two arguments. Both are required. The TEXT function lets you change the way dates or numbers appear. In our example, we wanted to see the day of the week.
-
One d would be a 1 for January 1, 2024
-
Two d's would be 01 for January 1, 2024.
-
Three d's would be Mon for January 1, 2024 since it was a Monday.
-
Four d's would be Monday for January 1, 2024.
Recent Excel articles
Chris Menard
Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with other 750 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 10 million viewers have appreciated.
Categories