How to Find the First Week of Month in Excel

Bill Jelen: Hey I’m Bill Jelen from Mr. Excel.com and I’ve got a cool Excel tip for you today. Mike Gel Girvin: Hey this is Mike Gel Girvin at Excel is Fun at YouTube and I have a different way to do that. Bill Jelen: Hey welcome back it’s another dueling Excel Podcast, I’m Bill Jelen I’ll be joined later by Mike Girvin from Excel is Fun. Great questions and today someone said you know I enter our date here and I need to back that date up to the first week of the month. With 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. Oh alright so I— we’re going to build this here, I’m going to just put in a few dates so equal date so let’s go with 2009, 1, 1 and then just add plus random between 1,350 so we get some random dates in there and copy that down. Alright so we have our dates. Make sure I’m going to format so I can see what day of week it is just that way I can do a few tests here so go to the long date. Alright so this our original date and the first thing I want to do is I want to figure out where the first of the month is. First of the month, I’m going to use, since I’m in Excel 2007 that great function called, EO month, EO month and I’m going to ask for the first of not this month but the previous month minus one. Check that out, great way to get back to the previous month EST, alright so what we have that takes us back to from July 10th of Tuesday June 30th when I had I had 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 enough on Monday. Perfect, we do, okay, 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 appear 1, 2, 3 and I ask for equal week day of this date press F4 1, 2, 3, times and using that code one two times all right and we’ll see how these workout here. What I’m looking for is something that basically on a Tuesday is going to return a one. Okay, so that looks like that’s at three code and even better on a Monday it’s returning zero so I want to then subtract the week day so I want to come up here with my adjustment factor. The adjustment is equal week day. 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 equal 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 formula is looking for a D10 so I come back to D10 and I’m going to choose all of the cells except for the equal sign CONTROL C to copy and then here choose D10 and control 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 characteristic except for the equal sign copy and then come here and each case where you have C10 will use CONTROL V to paste and CONTROL V to paste and shoot that down. So there we go that’s my formula there not too bad and this was the formula that they are we’re originally using much, much longer so it’s certainly a faster way to go. Let’s throw over Mike to see what Mike has? Mike Gel Girvin: Thanks Mr. Excel, wow this is a really great question so we have these column of dates and really what we want escape is that 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 value in on that that’s a pretty ingenious formula. This one right here the end of month that’s about a short and as efficient as you are going to get. End of month 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 4 mill that would work without end of month. Now first I’m going to just calculate from all of these dates the first day of whatever month it is. I went to hide these, highlight those two columns, right click hide and I’ll make this a little bit bigger. Equals date, we’ll 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 change as we copy it down. We’ll use first the year function, the year function will look at this data, just give us 2009 which is exactly what the date function needs in the year argument, and we’ll do month same thing and we’ll look here and give us a 10, and the day argument. Well we want the first that will just hard code a one end close parenthesis. Now, Control Enter when I copy that down that’s going to give s the first day of each one of these months. Now let’s see if we can notice a pattern here from down here. There’s a Monday right so that day is fine. From this we want to subtract zero but what about Tuesday. Oh we need to go back one day so to get to the Monday before so we subtract one Sunday, we actually need to subtract six. So from all of these dates we need to subtract a number from or from zero, to six. The week day function as Mr. Excel already show you it does exactly that. Now the week day 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 CONTROL C, click at the end and I’m going to do minus and we’ll use that awesome week day function. Now the screen tips play that says, “Hey give me the cereal number I’m not going to click there I’m going to click CONTROL V and just place whatever thing is that I just created and calculated on the first day of the month, and then the return type argument just like Mr. Excel like and I have to remember but I’m in the middle of the screen tip says I’m in a week day function so I want to click the F of X click in this argument dialogue box here return type and read it. Sunday one through Saturday equal seven use one Monday equals one Sunday equals seven two, oh look at this is this cool for Monday equals zero through Sunday equals six use three is ad if they make this little three in this return type parchment exactly for finding the first Monday of whatever reporting period this is. Perfect for our particular use, click okay 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 at first part which is first day of the month, we slapped that in the week day and use the weekday return type number three as second argument totally amazing. Alright we’ll see you next. Bill Jelen: Hey Mike that was cool, well I thank everyone fro stopping by we’ll see you next time for another dueling podcast from Excel is Fun and Mr. Excel.