Excel PivotTable - Calculate the number of days of each week for every month of the year

Posted on:  05/05/2022 Excel PivotTable - Calculate the number of days of each week for every month of the year

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

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

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

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

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

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

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

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

Mastering Excel Slicers: A Comprehensive Guide

Mastering Excel Slicers: A Comprehensive Guide

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

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

Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and Microsoft Most Valuable Professional (MVP). Chris works as a Senior Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel featuring over 900 technology videos that cover various apps, including Excel, Word, PowerPoint, Zoom, Teams, Coilot, and Outlook. To date, the channel has had over 25 million views. Menard also participates in 2 to 3 public speaking events annually, 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.

Categories