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.

Disney Event with Chris Menard in September 2022

Recent Excel articles

Excel Status Bar - Tips Every User Should Know

The Status Bar in Excel is the area at the bottom of the windows that shows your zoom control in the right corner and Ready in the left corner.

How to SORT Data in Excel | 7 Tips

Excel has a powerful SORTING engine. SORT is the most popular function in Excel, and it can sort by many types of data, many attributes, and even custom sorting configurations.

Excel - Data from Picture - Microsoft 365 New Feature

Have you ever had a printed page and needed to put the data in Excel? Typing the data in can be a pain. Excel now allows you to put the data in Excel using Data from Picture.

Excel - using the Scenario Manager to track house payments with skyrocketing mortgage rates

For potential homeowners, 1 year makes a HUGE difference. In 2021, we saw some of the lowest mortgage rates in the last 30 years. In July 2021, the average rate on 30-Year Fixed-Rate Mortgages was 2.87% (source FreddieMac). Today, July 2022, we are looking at rates around 5.8%.

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 over 800 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 12 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube