A dueling podcast...how to find the first week of the month. Mike and Bill show you various ways in Episode 1129.
This blog is the video 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 blog is the video 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:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, it's another Dueling Excel podcast, I'm Bill Jelen, I’ll be joined later by Mike Girvin from ExcelIsFun. Great question sent in today, someone said “You know, I enter a date here, and I need to back that date up to the first week of the month.
But the way they define the first week is driving me crazy, because they want to go back to the first of the month, that's easy enough to do. But then figure out where the Monday is!” Alright so, we're going to build this here, I'm going to just put in a few dates. So =DATE, let's go with 2009,1,1 and then just add +RANDBETWEEN(1,350) so we get some random dates in there, and we'll copy that down. Alright, so we have our dates, and I'm actually going to format those, so I can see what day the week it is, just so that way I can do a few tests here, so I'll go to the long date.
Alright, so this is our original date, and first thing I want to do is I want to figure out where the first of the month is. I'm going to use, since I'm in Excel 2007, a great function called EOMONTH, and I'm going to ask for the first of not this month, but the previous month, -1. Check that out, great way to get back to the previous month, (Alt) E S T, alright.
So what we have, that takes us back from July 10th to Tuesday June 30th. I'm going to add one to that, which will get me to the first of this month reliably, copy that down, let's see here, make sure that we have at least a few of them to fall on Monday, perfect, we do.
OK, next I need to do the weekday thing, and I can never remember how the weekday works, so I'm just going to use the three possibilities(?) up here, 1-2-3. I'm going to ask for =WEEKDAY of this date, press F4 3 times, using that code 2 times, and we'll see how these work out here. What I'm looking for is something that, basically, on a Tuesday is going to return a 1, OK, so that looks like that's it, the 3 code, and even better, on a Monday it's returning a 0. So I want to then subtract the weekday, so I'm going to come up here with my Adjustment factor. Adjustment is =WEEKDAY of this original date, ,3 , because that's what our test says that we want to do. And then finally, our Monday that we want is = column C, minus the number of days here, should always get us back to a Monday every single time, alright.
Now, so we have that formula, but unfortunately, you know, it took me several tries to get that formula, so I'm going to use the trick that I frequently use to build a nice mega-formula.
I see here that my formulas looking for D10, so I come back to D10 and I'm going to choose all of the cells except for the equal sign, Ctrl+C to copy, and then here choose D10, and Ctrl+V to paste. Great, now I have a formula that uses C10 twice, so we'll come back to C10, and again copy all of the characters except for the equal sign, copy. And then come here, and in each case where we have C10 I will use Ctrl+V to paste, and Ctrl+V to paste, and shoot that down. So there we go, that’s my formula there, not too bad, this was a formula that they were originally using, much, much longer, so, certainly a faster way to go. Let’s roll over to Mike, see what Mike has!
Mike: Thanks MrExcel! Wow, this is a really great question! So, we have this column of dates, and really what we want, Esc, is if the first day of the month is Monday. We want to keep that day, otherwise, we need to go back to the previous month and find the last Monday. Hey, you know, this original formula is pretty ingenious. If you run formula evaluator on that, that’s a pretty ingenious formula, this one right here, EOMONTH, that's about as short and as efficient as you are going to get. EOMONTH is added in through Analysis tool-pack, or if you have 2007 or 10, it's built in. Some people don't have that, I'll give you a formula that'll work without EOMONTH.
Now first, I'm going to just calculate, from all of these dates, the first day of whatever month it is. Why don't I hide these, right, highlight those two columns, right-click, Hide, and then I'll make this a little bit bigger, =DATE, we use the DATE function. Now all you need to do is give it a year, a month, and a day, since we want it to be changed as we copy it down. We'll use first the YEAR function, we'll look at this date and just give us 2009, which is exactly what the DATE function needs in the year argument. Comma, and then we'll do MONTH, same thing, it'll look here and give us a 10, comma, and the day argument! Well, we want the first, so we'll just hard code a 1 in, closed parenthesis, now Ctrl+Enter, when I copy that down, that's going to give us the first day of each one of these months.
Now let's see if we can notice a pattern here, come down here, there's a Monday right, so that date is fine. From this we want to -0, but what about Tuesday? Oh, we need to go back one day, so to get to the Monday before, so we -1, Sunday? We actually need to -6.
So from all of these dates, we need to subtract a number from 0 to 6, the WEEKDAY function, as MrExcel already showed you, does exactly that. Now the WEEKDAY function is not going to be looking at this date, it's actually going to be looking at this whole, the first day of the month. So I'm going to copy this, highlight, Ctrl+C, click at the end, and now I'm going to do minus, and we'll use that awesome WEEKDAY function.
Now the screen tip’s polite(?), it says “Hey, give me the serial number.” I'm not going to click there, I'm going to hit Ctrl+V, and just paste whatever that thing is that I just created calculating the first day of the month, comma. And then the return_type argument, just like MrExcel I can never remember it, but I'm in the middle, the screen tips says I'm in the WEEKDAY function. So I'm going to click the Fx, click in this argument dialog box here, Return_type, and read it: “Sunday 1 through Saturday = 7; use 1, Monday = 1, Sunday =7; 2” Ah, look at this, is this cool? “For a Monday 0, through Sunday = 6, use 3!” It’s as if they made this little 3 in this return_type argument exactly for finding the first Monday of whatever reporting period this is. Perfect for our particular use, click OK, and there it is. I’m going to double-click and send it down, and you can see right there, it looks big and scary, it was just that first part, which is first day of the month. We slap that in the weekday, and use the WEEKDAY, return_type #3 as a second argument, totally amazing. Alright, we'll see you next trick!
Bill: Hey, alright Mike, that was cool one, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from ExcelIsFun and MrExcel!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, it's another Dueling Excel podcast, I'm Bill Jelen, I’ll be joined later by Mike Girvin from ExcelIsFun. Great question sent in today, someone said “You know, I enter a date here, and I need to back that date up to the first week of the month.
But the way they define the first week is driving me crazy, because they want to go back to the first of the month, that's easy enough to do. But then figure out where the Monday is!” Alright so, we're going to build this here, I'm going to just put in a few dates. So =DATE, let's go with 2009,1,1 and then just add +RANDBETWEEN(1,350) so we get some random dates in there, and we'll copy that down. Alright, so we have our dates, and I'm actually going to format those, so I can see what day the week it is, just so that way I can do a few tests here, so I'll go to the long date.
Alright, so this is our original date, and first thing I want to do is I want to figure out where the first of the month is. I'm going to use, since I'm in Excel 2007, a great function called EOMONTH, and I'm going to ask for the first of not this month, but the previous month, -1. Check that out, great way to get back to the previous month, (Alt) E S T, alright.
So what we have, that takes us back from July 10th to Tuesday June 30th. I'm going to add one to that, which will get me to the first of this month reliably, copy that down, let's see here, make sure that we have at least a few of them to fall on Monday, perfect, we do.
OK, next I need to do the weekday thing, and I can never remember how the weekday works, so I'm just going to use the three possibilities(?) up here, 1-2-3. I'm going to ask for =WEEKDAY of this date, press F4 3 times, using that code 2 times, and we'll see how these work out here. What I'm looking for is something that, basically, on a Tuesday is going to return a 1, OK, so that looks like that's it, the 3 code, and even better, on a Monday it's returning a 0. So I want to then subtract the weekday, so I'm going to come up here with my Adjustment factor. Adjustment is =WEEKDAY of this original date, ,3 , because that's what our test says that we want to do. And then finally, our Monday that we want is = column C, minus the number of days here, should always get us back to a Monday every single time, alright.
Now, so we have that formula, but unfortunately, you know, it took me several tries to get that formula, so I'm going to use the trick that I frequently use to build a nice mega-formula.
I see here that my formulas looking for D10, so I come back to D10 and I'm going to choose all of the cells except for the equal sign, Ctrl+C to copy, and then here choose D10, and Ctrl+V to paste. Great, now I have a formula that uses C10 twice, so we'll come back to C10, and again copy all of the characters except for the equal sign, copy. And then come here, and in each case where we have C10 I will use Ctrl+V to paste, and Ctrl+V to paste, and shoot that down. So there we go, that’s my formula there, not too bad, this was a formula that they were originally using, much, much longer, so, certainly a faster way to go. Let’s roll over to Mike, see what Mike has!
Mike: Thanks MrExcel! Wow, this is a really great question! So, we have this column of dates, and really what we want, Esc, is if the first day of the month is Monday. We want to keep that day, otherwise, we need to go back to the previous month and find the last Monday. Hey, you know, this original formula is pretty ingenious. If you run formula evaluator on that, that’s a pretty ingenious formula, this one right here, EOMONTH, that's about as short and as efficient as you are going to get. EOMONTH is added in through Analysis tool-pack, or if you have 2007 or 10, it's built in. Some people don't have that, I'll give you a formula that'll work without EOMONTH.
Now first, I'm going to just calculate, from all of these dates, the first day of whatever month it is. Why don't I hide these, right, highlight those two columns, right-click, Hide, and then I'll make this a little bit bigger, =DATE, we use the DATE function. Now all you need to do is give it a year, a month, and a day, since we want it to be changed as we copy it down. We'll use first the YEAR function, we'll look at this date and just give us 2009, which is exactly what the DATE function needs in the year argument. Comma, and then we'll do MONTH, same thing, it'll look here and give us a 10, comma, and the day argument! Well, we want the first, so we'll just hard code a 1 in, closed parenthesis, now Ctrl+Enter, when I copy that down, that's going to give us the first day of each one of these months.
Now let's see if we can notice a pattern here, come down here, there's a Monday right, so that date is fine. From this we want to -0, but what about Tuesday? Oh, we need to go back one day, so to get to the Monday before, so we -1, Sunday? We actually need to -6.
So from all of these dates, we need to subtract a number from 0 to 6, the WEEKDAY function, as MrExcel already showed you, does exactly that. Now the WEEKDAY function is not going to be looking at this date, it's actually going to be looking at this whole, the first day of the month. So I'm going to copy this, highlight, Ctrl+C, click at the end, and now I'm going to do minus, and we'll use that awesome WEEKDAY function.
Now the screen tip’s polite(?), it says “Hey, give me the serial number.” I'm not going to click there, I'm going to hit Ctrl+V, and just paste whatever that thing is that I just created calculating the first day of the month, comma. And then the return_type argument, just like MrExcel I can never remember it, but I'm in the middle, the screen tips says I'm in the WEEKDAY function. So I'm going to click the Fx, click in this argument dialog box here, Return_type, and read it: “Sunday 1 through Saturday = 7; use 1, Monday = 1, Sunday =7; 2” Ah, look at this, is this cool? “For a Monday 0, through Sunday = 6, use 3!” It’s as if they made this little 3 in this return_type argument exactly for finding the first Monday of whatever reporting period this is. Perfect for our particular use, click OK, and there it is. I’m going to double-click and send it down, and you can see right there, it looks big and scary, it was just that first part, which is first day of the month. We slap that in the weekday, and use the WEEKDAY, return_type #3 as a second argument, totally amazing. Alright, we'll see you next trick!
Bill: Hey, alright Mike, that was cool one, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from ExcelIsFun and MrExcel!
