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

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.

## 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 - using the Scenario Manager to track house payments with skyrocketing mortgage rates

##### 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