You need to fill the 15th & last of the month. Excel MVP Bob Umlas shows you a trick from his book, Excel Outside the Box. For more information on his book, see: Excel Outside the Box
Transcript of the video:
Learn Excel from Mr Excel podcast episode 1833.
Bob Umlas 15th & Last of the Month.
Hey, welcome back to the MrExcel netcast, again I have another guest post from Bob Umlas. Bob.
Hi! This is Bob Umlas talking to you from the MVP Summit and good old Washington.
What we're looking at is the result that we want to achieve that is, I want what the 15th of the month followed by the end date of the month, then the 15 to the next month and so on.
So, this is the goal and let's take a look at some of the attempts that we do.
You might first start to use the fill handle.
So, the attempt 1.
I put in the first the 15th of the month and 31st of the month and I select them both and I drag the fill handle down I know it's going to go to row 24, but this is quite a mess, that the first one is off 2/16, 3/4 and the reason is doing that is because they're not consistent.
The end of every month is not exactly 15 or 16 days.
So, this is just a horrible mess attempt 1, we get rid of that.
Attempt 2, we might start with the fifteenth of each two months and then we select this range and if I drag that down to row 24, it's a pretty good start to give us the fifteenth of each month.
Now, I can go to blanks, Go To, Special, the F5 key, Go To, Special and I click on the blanks and I have all the blanks selected aside.
Oh! including the last one because I went to the 24th.
Okay so, in A2,, I'm going to enter the month that I'm, the month following that cell minus 15.
So, 15 days before that and if I Control+Enter, it will fill every cell with the same formula.
As if I did a fill down, let me just widen this, widen this even more, oh no, this I'm sorry.
This one there is no cell, there's nothing in A25.
So, A25-15 doesn't exist and that's why it says, "Manually enter 1231".
So, I was a lot better because it worked, but the best way is to start the way.
we originally did the 15th and the 31st, but instead of using the regular fill handle I right mouse click the fill handle and when I let go it gives me some options and one of the options is just fill months and we're done.
It's smart enough to do that.
Thank you.
Bill: All right, there you go.
Now, Bob has been a Microsoft Excel MVP, longer than any MVP.
He was there when the program started a great way to simplify that task and we'll have more podcasts from Bob over the coming weeks.
So, check it out again the link right down there and then YouTube comments.
Thank you for stopping by.
We'll see you next time for another netcast from Mr Excel.
Bob Umlas 15th & Last of the Month.
Hey, welcome back to the MrExcel netcast, again I have another guest post from Bob Umlas. Bob.
Hi! This is Bob Umlas talking to you from the MVP Summit and good old Washington.
What we're looking at is the result that we want to achieve that is, I want what the 15th of the month followed by the end date of the month, then the 15 to the next month and so on.
So, this is the goal and let's take a look at some of the attempts that we do.
You might first start to use the fill handle.
So, the attempt 1.
I put in the first the 15th of the month and 31st of the month and I select them both and I drag the fill handle down I know it's going to go to row 24, but this is quite a mess, that the first one is off 2/16, 3/4 and the reason is doing that is because they're not consistent.
The end of every month is not exactly 15 or 16 days.
So, this is just a horrible mess attempt 1, we get rid of that.
Attempt 2, we might start with the fifteenth of each two months and then we select this range and if I drag that down to row 24, it's a pretty good start to give us the fifteenth of each month.
Now, I can go to blanks, Go To, Special, the F5 key, Go To, Special and I click on the blanks and I have all the blanks selected aside.
Oh! including the last one because I went to the 24th.
Okay so, in A2,, I'm going to enter the month that I'm, the month following that cell minus 15.
So, 15 days before that and if I Control+Enter, it will fill every cell with the same formula.
As if I did a fill down, let me just widen this, widen this even more, oh no, this I'm sorry.
This one there is no cell, there's nothing in A25.
So, A25-15 doesn't exist and that's why it says, "Manually enter 1231".
So, I was a lot better because it worked, but the best way is to start the way.
we originally did the 15th and the 31st, but instead of using the regular fill handle I right mouse click the fill handle and when I let go it gives me some options and one of the options is just fill months and we're done.
It's smart enough to do that.
Thank you.
Bill: All right, there you go.
Now, Bob has been a Microsoft Excel MVP, longer than any MVP.
He was there when the program started a great way to simplify that task and we'll have more podcasts from Bob over the coming weeks.
So, check it out again the link right down there and then YouTube comments.
Thank you for stopping by.
We'll see you next time for another netcast from Mr Excel.
