Count the days per week in a month using Excel
Count the days per week in a month using Excel. If you want to know how many days there are in each week of a month but don’t know how to do it, don’t worry. In this article, I will guide you on how to count the number of days in each week of a month in Excel very quickly. Let’s find out!
Step-by-Step Count the days per week in a month using Excel
How to count the number of days in a week for each month?
To be able to display the number of days in a week for each month, of course, you will have to prepare a column containing all long dates, a column containing month names, and a column containing the names of the day of the week.
To calculate and display long dates, you will do as follows:
First, you will select the column name that you want to display the long date.
Next, you will click on the arrow next to the Number Format box and choose the format you want – long date.
Count the days per week in a month using Excel
Next, in the first cell displaying the long date, you will enter the following formula:
=SEQUENCE(365,1,DATE(2022,1,1))
In which: 365 represents the number of days in a non-leap year, specifically 2022.
1: Display 1 column
Starting from January 1, 2022, use the Date function to display.
Count the days per week in a month using Excel
Finally, press Enter to display 365 days in 2022.
Note: If the year you want to count the number of days in each week is a leap year, you will replace it with 366 days!
To extract the month name (Month column) from the Date column cell, you will use the TEXT function to display it!
In the first cell displaying the month name, you enter the following formula: =TEXT(B2,”MMMM”)
Count the days per week in a month using Excel
Similarly, to extract the weekday name (Weekday column) from the Date column, you also use the TEXT function to display it!
In the first cell displaying the weekday name, you enter the following formula: =TEXT(B2,”DDDD”)
Count the days per week in a month using Excel
After calculating and displaying the three columns Date, Month (month name), Weekday (weekday name). You will proceed to calculate the number of days in each week of each month as follows:
Step 1: Select any cell in the data range.
Step 2: Go to the Insert tab => in the Tables group, select PivotTable.
Count the days per week in a month using Excel
Step 3: Display the PivotTable from the table or range. In the Table/Range box: contains the data range, in the Choose where you want the PivotTable to be placed: If you want to display the PivotTable on a different sheet, select New Worksheet or if you want to display it in the current sheet, select Existing Worksheet and select the location to display the PivotTable.
Count the days per week in a month using Excel
Step 4: Display the PivotTable, you drag the Month field to Row, the Weekday field to Column, and the Date field to Values to calculate the number of days.
Count the days per week in a month using Excel
That’s it, now you can view the number of days in each week of each month in the PivotTable.
Note: In case you want to count the number of days in a different year, you will replace the formula with the year you want to count the number of days. As for the PivotTable, it will update itself. In the PivotTable Analyze tab, you select the Refresh tool.
Count the days per week in a month using Excel
So I have finished guiding you on how to count the number of days in each week of a month in Excel. I hope this article will be useful for you!
