Ronak asks: How can I use Excel to find out how many weekedays per month for the whole year?
This video shows how to do it using SEQUENCE, TEXT and a quick pivot table in Excel.
This video shows how to do it using SEQUENCE, TEXT and a quick pivot table in Excel.
Transcript of the video:
How to count the number of weekdays per month?
Hey welcome back to the MrExcel Netcast, I am Bill Jelen.
Today, a great questions by Ronak.
He is trying to figure out how many weekdays there are in each month, let's say for the whole year.
So there's five Fridays, 5 Saturdays, 5 Sundays.
First thing, select the whole column where the dates are going to go and change that to long date.
So what we're going to do here is we're going to put the dates down the left hand side.
I'm going to create a SEQUENCE function, 365 rows, one column starting from DATE(2021,1,1).
So that's January 1st 2021.
That should generate for me the 365 days.
If it's a leap year, of course, use 366.
And then the two important functions over here we're going to ask for the TEXT of A2 in MMMM format.
That will spell out the month name for us.
And then the TEXT of A2 in DDDD format like that.
Double Click to copy that down.
All right, almost done.
Insert, Pivot table, Existing worksheet.
Right here - somewhere on a blank section of the worksheet.
On the left hand side we will put months that goes to the Rows area.
Across the top, we'll put Weekday that goes to the columns area.
And then I need to get a count in here.
I'll just take the date field and drag it to values and it will count.
So in January 5 Sundays, but only four Mondays, four Tuesdays, 4 Wednesdays.
Across the bottom, you can see there's 52 of each except 53, so you can see there's 53 Fridays, 52 of everything else.
Now if he would change the year, so we come up here and change from 2021 to 2022, there's a gotcha.
You got to be careful.
This is a pivot table.
It does not automatically recalculate how you have to come in here and on the PivotTable analyze tab, click refresh.
So you know, in 2022 there's 52 Fridays and 53 Saturdays.
Like that.
Great question from Ronak.
I appreciate him sending that in.
Check out my new book MrExcel 2022,Bboosting Excel - all kinds of great tips and tricks in there.
Click that i on the top right hand corner for more information about that.
Why I want to thank Ronak for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Hey welcome back to the MrExcel Netcast, I am Bill Jelen.
Today, a great questions by Ronak.
He is trying to figure out how many weekdays there are in each month, let's say for the whole year.
So there's five Fridays, 5 Saturdays, 5 Sundays.
First thing, select the whole column where the dates are going to go and change that to long date.
So what we're going to do here is we're going to put the dates down the left hand side.
I'm going to create a SEQUENCE function, 365 rows, one column starting from DATE(2021,1,1).
So that's January 1st 2021.
That should generate for me the 365 days.
If it's a leap year, of course, use 366.
And then the two important functions over here we're going to ask for the TEXT of A2 in MMMM format.
That will spell out the month name for us.
And then the TEXT of A2 in DDDD format like that.
Double Click to copy that down.
All right, almost done.
Insert, Pivot table, Existing worksheet.
Right here - somewhere on a blank section of the worksheet.
On the left hand side we will put months that goes to the Rows area.
Across the top, we'll put Weekday that goes to the columns area.
And then I need to get a count in here.
I'll just take the date field and drag it to values and it will count.
So in January 5 Sundays, but only four Mondays, four Tuesdays, 4 Wednesdays.
Across the bottom, you can see there's 52 of each except 53, so you can see there's 53 Fridays, 52 of everything else.
Now if he would change the year, so we come up here and change from 2021 to 2022, there's a gotcha.
You got to be careful.
This is a pivot table.
It does not automatically recalculate how you have to come in here and on the PivotTable analyze tab, click refresh.
So you know, in 2022 there's 52 Fridays and 53 Saturdays.
Like that.
Great question from Ronak.
I appreciate him sending that in.
Check out my new book MrExcel 2022,Bboosting Excel - all kinds of great tips and tricks in there.
Click that i on the top right hand corner for more information about that.
Why I want to thank Ronak for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
