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

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

TEXT Function

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

Excel Training for the University of Georgia's Online and Professional MBA Students

Saturday, April 5, 2025: I'll train the University of Georgia's online and professional MBA students in Microsoft Excel. Topics include PivotTables, Filtering, and even Financial Statement analysis.

Copilot in Excel - Ask Copilot Questions about Keyboard Shortcuts, Functions, and Steps

Copilot in Excel can be asked for intermediate keyboard shortcuts, how-to steps, and function explanations.

Mastering Excel Slicers: A Comprehensive Guide

Excel slicers are powerful tools that enhance data filtering in Microsoft Excel, making it easier to visualize and analyze your data. In this blog, we will explore how to create and effectively use slicers, as well as their advantages and limitations in comparison to traditional filters.

Mastering Loan Amortization: A Comprehensive Guide to Understanding and Comparing Loans

In today's ever-changing financial landscape, understanding loan amortization is crucial for anyone considering a major purchase, particularly a home. With recent changes in interest rates, it's more important than ever to grasp how loan amortization works and how to compare different loan options.

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.