Rod has a formula in column B that calculates the earliest month in a data set. He then wants formulas in C, D, E, and so on that show the first of the next month. Although you can right-click the fill handle to do this, Episode 942 will show you how to solve this using the DATE function.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, a question from Rod. Now, Rod has a very specific problem. He has a formula.
That formula is returning the very first date in a data set.
I'm suspecting it’s probably a MIN formula or something that.
So, he ends up with his cell up here in B2 and he wants formulas going across the worksheet that will give him the first of the next month, the next month, the next month, the next month, and so on, and Rod was pretty clever.
He initially said, okay, well, let's just try and add +30.5 and have it go across, and you see, in many cases, that would work out, especially if you had formatted everything to just show the month and the year.
Most months, that's going to work, but right here, in the very first one, January 1st +30.5 gives you January 31st, and so he says, there's a couple of times where I get the same month twice.
So, let's delete that and I'm going to try a different way here.
I'm going to use a very robust function called the DATE function.
=DATE.
DATE wants three things.
It wants a year, a month, and a day, and so, to get the year, I'm going to take the year of the cell to the left, [ unintelligible – 01:23 ] B2, to get the month, I'm going to use the month of B2 but I'm going to add 1 -- so, in other words, go to the next month -- and then, for the day, I'm going to hard-code that I always want the number 1, and we’ll copy this across here, and you see that no matter when we start, whether we start in March, April, May, it automatically goes across and gives me the first of the next month.
[ =DATE(YEAR(B2),MONTH(B2)+1,1) ] So, very safe way to go, and then, even if this date would change and he would get data for 12/1/2008, then those months going across will automatically update, and of course, again, I suspect that Rod was using the date that shows only the month and the year, and then everything just looks beautiful that way.
Now, I want to just point out here that this is assuming that Rod needs a formula.
If we really just wanted to get the months going across, you would right click the fill handle, right click the fill handle, and drag, and then choose FILL MONTHS from the menu that pops up, and then you can get the first of each month -- so, a great way to go.
Actually, if you would do that on, let's say, February 5th and drag across, it will give you March 5th, April 5th, May 5th, and so on.
So, if you didn't need a formula, right clicking and dragging the fill handle is the way to go, but using the formula, the DATE function, great way to go.
Thanks to Rod for sending the question in and thanks to you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Today, a question from Rod. Now, Rod has a very specific problem. He has a formula.
That formula is returning the very first date in a data set.
I'm suspecting it’s probably a MIN formula or something that.
So, he ends up with his cell up here in B2 and he wants formulas going across the worksheet that will give him the first of the next month, the next month, the next month, the next month, and so on, and Rod was pretty clever.
He initially said, okay, well, let's just try and add +30.5 and have it go across, and you see, in many cases, that would work out, especially if you had formatted everything to just show the month and the year.
Most months, that's going to work, but right here, in the very first one, January 1st +30.5 gives you January 31st, and so he says, there's a couple of times where I get the same month twice.
So, let's delete that and I'm going to try a different way here.
I'm going to use a very robust function called the DATE function.
=DATE.
DATE wants three things.
It wants a year, a month, and a day, and so, to get the year, I'm going to take the year of the cell to the left, [ unintelligible – 01:23 ] B2, to get the month, I'm going to use the month of B2 but I'm going to add 1 -- so, in other words, go to the next month -- and then, for the day, I'm going to hard-code that I always want the number 1, and we’ll copy this across here, and you see that no matter when we start, whether we start in March, April, May, it automatically goes across and gives me the first of the next month.
[ =DATE(YEAR(B2),MONTH(B2)+1,1) ] So, very safe way to go, and then, even if this date would change and he would get data for 12/1/2008, then those months going across will automatically update, and of course, again, I suspect that Rod was using the date that shows only the month and the year, and then everything just looks beautiful that way.
Now, I want to just point out here that this is assuming that Rod needs a formula.
If we really just wanted to get the months going across, you would right click the fill handle, right click the fill handle, and drag, and then choose FILL MONTHS from the menu that pops up, and then you can get the first of each month -- so, a great way to go.
Actually, if you would do that on, let's say, February 5th and drag across, it will give you March 5th, April 5th, May 5th, and so on.
So, if you didn't need a formula, right clicking and dragging the fill handle is the way to go, but using the formula, the DATE function, great way to go.
Thanks to Rod for sending the question in and thanks to you for stopping by.
We’ll see you next time for another netcast from MrExcel.
