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 Conditional Formatting with the TODAY function | Duplicate Rule

Excel Conditional Formatting with the TODAY function | Duplicate Rule

The TODAY function is one of my favorite Date & Time functions in Excel. It returns the current date and will automatically update. We will use the TODAY function with Conditional Formatting and create three rules on a date field.

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Accountants use Excel all the time. In this video, which is great for Accounting students, we look at the Accounts Receivable (AR) or Accounts Payable (AP) exercise.

Excel Recommend PivotTables - Improved Experience

Excel Recommend PivotTables - Improved Experience

Recommended PivotTables in Excel is getting a great update. To use Recommended PivotTables, click the Insert Tab, and click Recommended PivotTables.

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

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.

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