Hey, welcome back to the Mr. Excel Net cast. I’m Bill Jelen. Yesterday, we solved Shawn’s problem and I was looking at the data site and I mentioned that big huge formula that was in the Excel Gurus Gone Wild book and I want to set that up. I’m going to talk about that formula tomorrow but let’s talk about when we need to figure out the number of days elapsed between two dates.
Let’s say that we have to build for both the start date and the end date and we’re just trying to figure out that number of days is pretty simple. The later date minus the earlier date, let’s going to say that there’s one date that’s elapsed and so we want to add one to that and double click this, and that formula down and we’re going to figure out the number of days. But what if you only work Monday through Friday and you only want to build for the Monday through Friday dates.
There’s a great function that was in the analysis tool pack called equal network days, network days, start from the start date, go to the later date and it’s going to only count Monday through Friday dates. So right here in the first one, the number of days and the number of work days is exactly the same but let me extend that through the Saturday, 07/05/2008 which was a Saturday. He said there’s three elapsed but only to work days, isn’t that call?
Now, network days is always available in Excel 2007 and Excel 2003, how to make sure that the analysis took pack was turned on, go to tools, add ins and check the box for analysis tool pack. Network days are really cool and that you can also give a third argument that says, “Hey, I want to ignore certain company holidays. So out here, we’ll enter formula and optional third argument. We’ll come out and point to this range that has holidays. I’ll press F4 and see. You can remember that extra equal sign. And you see that because July 4th in the United States is a holiday, now excludes that, so very cool, cool way to go.
Alright now, I’m going to throw a curl ball at you. Who want to count a number of Monday, Wednesdays and Fridays in there, maybe some part time? They only work for those three days. So take out the Monday, Wednesday, Friday dates. Alright, well first of all I have to wrap my head around the weekday function. I can never remember how this works. So put in today’s date. I’ll format that to show what the week is it and grab the fill headed down so that we’re going to have a nice range of every Monday through Friday date equal weekday. Says, check this date, I’ll press F4 three times using this return type, press F4 twice. Alright, so the turn types are one, two and three. We’ll copy that down and copy that over.
Alright, so you see that the weekday function has different return type cell. One of them says that Sunday is a one going through Saturday being seven. Another one it says that Monday is a one going through Sunday being seven and then yet another one has Monday and zero going through Sunday as six. If you don’t want to memorize this, here’s just a cool way to go. Let’s use the mod function. Take the date, divided by seven and get the reminder. Now, I’ll have to format that as a number, copy that down and we can just look if we want Monday, Wednesday and Friday. That’s where the result of this function is either two, a four or a six, two four six.
Alright so, here’s what we’re going to do. I’m going to switch over to VBA. I’m going to create a new custom functions. So instead of starting with the word sum, I started off with the word function, the name of my function and the arguments. I want to start date and an end date. Very important that name of the function has to be a variable that you’re going to set a value two within the function. I’m going to create a little loop here for I equals start date to end date, select case. I want to do the mod function. Actually, I, mod seven, that’s going to return the number from zero to seven. The only times that I care about are where the result is two, four or six, so case two comma four comma six in those cases, Monday, Wednesday, Friday is equal to Monday, Wednesday, Friday plus one and select and next I.
Alright, so we have a nice little function here, the Monday, Wednesday, Friday function. Let’s just give that a try back here in the original date set equal Monday, Wednesday, Friday of start date, the end date and copy that down and now that custom function is figuring out the number of just Monday, Wednesday, Friday. It’s very easily to customize this for any days of the mod function that you want. So for example if you want to add Thursday, we would have five comma and now we’re getting Thursday as well and so on, so just a quick little bit of VBA coder. Again, as I mentioned on Tuesday, the live lessons power, Excel, micros, product from Q will teach you how to write code.
Now tomorrow, we’re going to look at in the same formula, let’s going to solve this problem without using any VBA whatsoever. Actually, it’s a very clever formula.
So, thanks for stopping by, we’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services