A question from Foxy on YouTube: What Excel formula will convert a date to the Week Number of the Month? It should never show more than a max of 5 weeks. Download the practice file: 2350_Practice_File.xlsx
Transcript of the video:
Learn Excel from MrExcel podcast episode 2350. Week number of the month.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question sent in via YouTube from Foxy the Pirate Fox.
What formula will determine a week of the month?
I love this question. I've never seen this one.
We want a number for each week within the month. So, from 1 to 5.
While i came up with an answer really quickly, I am not sure exactly how we're going to number the weeks.
There are two different ways that I can see.
So, is it that we start the week number on monday so here in july 2027 where the first of the month starts on a thursday this four day week becomes week one week two week three week four and then week five is whatever is left. That was my first reaction.
Or, do you mean that if the month starts on Thursday then everything from Thursday to Wednesday is week one.
And then week two and then week three and then week four and then week five. I am going to solve it both ways.
The first way to solve it.
Where the weeks start on Monday and run through Sunday.
The first thing I want to know is from these dates over here what's the week number? The ISOWEEKNUMBER.
That tells me the week of the year.
And then to get back to the first of the month. For today, July 13th, it's week number 28.
To get back to the first of the month we're taking the end of month of that day. Going back to the previous month.
So going back to June 30 2027. Then adding 1 to get to July 1st.
Once we know the first of the month, we get the week number of that.
So here, the current day the week number is 26.
Here the week number of the first month is 26.
But for laters dates, you know this is week 30 and this is week 26.
And then to get to the week of the month, we just subtract B2 minus D2. And add one because it's never week zero.
Wrap that whole thing together.
The ISOWEEKNUMBER of your date minus the ISOWEEKNUMBER of the end of the previous month plus one. And then add one.
So that way you never have a week zero. i felt really good about this.
Until i went to the next month. I scrolled through here to check.
We go up to week five in July.
August starts on a Sunday so you have a week 1 that lasts one day.
And then I realized that we are going to be in trouble because you're going to have a week six.
And Foxy says. "it should only show five weeks max ".
The only way to only show five weeks max is we must want this second style.
The great news is that style is a much easier calculation.
We have our date. We ask for the day of the date. Divide that by 7. B2 divided by 7.
And then use the ROUNDUP function.
Round up to 0 decimal places.
And then that formula just simply becomes the ROUNDUP of the DAY of A2, divided by seven comma 0.
And we make sure that down here in August of 2027.
It ends up at week five. So we pass the test.
I want to thank foxy for sending that question in. I am going to ask you to subscribe.
I am trying to get better merchandise than this stuff down here.
I am not a fan of these shirts.
There's a company that has much better shirts. Higher quality shirts.
But they say that I need a hundred thousand subscribers. And I am currently in the low 90 thousands. So please subscribe.
Don't even ring the bell. I don't want to bother you.
I don't want notifications on your phone.
Just subscribe so we can have better shirts. Please. The book, MrExcel 2020 Seeing Excel Clearly.
Click that 'i" up there in the top right hand corner.
For more information about that book.
I want to thank foxy for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another Netcast from MrExcel [ ♫♫ ] Tips and tricks. Get your spreadsheet fix from MrExcel.com [ ♫ ]
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's question sent in via YouTube from Foxy the Pirate Fox.
What formula will determine a week of the month?
I love this question. I've never seen this one.
We want a number for each week within the month. So, from 1 to 5.
While i came up with an answer really quickly, I am not sure exactly how we're going to number the weeks.
There are two different ways that I can see.
So, is it that we start the week number on monday so here in july 2027 where the first of the month starts on a thursday this four day week becomes week one week two week three week four and then week five is whatever is left. That was my first reaction.
Or, do you mean that if the month starts on Thursday then everything from Thursday to Wednesday is week one.
And then week two and then week three and then week four and then week five. I am going to solve it both ways.
The first way to solve it.
Where the weeks start on Monday and run through Sunday.
The first thing I want to know is from these dates over here what's the week number? The ISOWEEKNUMBER.
That tells me the week of the year.
And then to get back to the first of the month. For today, July 13th, it's week number 28.
To get back to the first of the month we're taking the end of month of that day. Going back to the previous month.
So going back to June 30 2027. Then adding 1 to get to July 1st.
Once we know the first of the month, we get the week number of that.
So here, the current day the week number is 26.
Here the week number of the first month is 26.
But for laters dates, you know this is week 30 and this is week 26.
And then to get to the week of the month, we just subtract B2 minus D2. And add one because it's never week zero.
Wrap that whole thing together.
The ISOWEEKNUMBER of your date minus the ISOWEEKNUMBER of the end of the previous month plus one. And then add one.
So that way you never have a week zero. i felt really good about this.
Until i went to the next month. I scrolled through here to check.
We go up to week five in July.
August starts on a Sunday so you have a week 1 that lasts one day.
And then I realized that we are going to be in trouble because you're going to have a week six.
And Foxy says. "it should only show five weeks max ".
The only way to only show five weeks max is we must want this second style.
The great news is that style is a much easier calculation.
We have our date. We ask for the day of the date. Divide that by 7. B2 divided by 7.
And then use the ROUNDUP function.
Round up to 0 decimal places.
And then that formula just simply becomes the ROUNDUP of the DAY of A2, divided by seven comma 0.
And we make sure that down here in August of 2027.
It ends up at week five. So we pass the test.
I want to thank foxy for sending that question in. I am going to ask you to subscribe.
I am trying to get better merchandise than this stuff down here.
I am not a fan of these shirts.
There's a company that has much better shirts. Higher quality shirts.
But they say that I need a hundred thousand subscribers. And I am currently in the low 90 thousands. So please subscribe.
Don't even ring the bell. I don't want to bother you.
I don't want notifications on your phone.
Just subscribe so we can have better shirts. Please. The book, MrExcel 2020 Seeing Excel Clearly.
Click that 'i" up there in the top right hand corner.
For more information about that book.
I want to thank foxy for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another Netcast from MrExcel [ ♫♫ ] Tips and tricks. Get your spreadsheet fix from MrExcel.com [ ♫ ]
