Figure out the number of billable days between two dates. Episode 1018 looks at ways to count the number of days, number of workdays, or number of Monday-Wednesday-Friday dates between two dates.
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.
Basically, we start out with massive amount of data.
So, how we're going to analyze as well as file up a pivot table.
Let's see if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday, we solve Shawn's problem, and I was looking at a data set and I mentioned that big huge Formula.
That was in the Excel Gurus Gone Wild book and I just 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.
So, it let's say that we have to build for both the start date and the end date if we're just trying to figure out the number of Days, it's pretty simple.
The later date minus the earlier date is going to say that there's one day that's elapsed.
And so, we want to add one to that and Double-Click to send that formula down.
And we can 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 =NETWORKDAYS NETWORKDAYS...
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 to Saturday 7/5/2008.
Which was a Saturday.
You said there are three days elapsed but only two work days.
Isn't that cool?
Now, NETWORKDAYS is always available in Excel 2007.
In excel 2003, how to make sure that the analysis toolpak was turned on?
Go to Tools>add-ins and check the box for Analysis Tool-pack.
NETWORKDAYS is really cool.
And that you can also give it a third argument that says hey, "want to ignore certain company holidays".
So, out here w'll enter a Formula and optional third argument, will come out.
And point to this range that has holidays, I'll press F4 and let's see it worth an extra equal sign.
And you see that because July 4th in the united states is a holiday, it now excludes that.
So, very cool cool way to go.
Alright! Now I'm going to throw a curve ball at you.
We want to count the number of Mondays, Wednesdays and Fridays in there.
You know maybe someones part time they only work those three days.
So, take out the Monday, Wednesday, Friday days.
It's alright.
Well, first of all I have to wrap my head around the weekday function.
I can never remember how this works, so we'll put in today's date.
I'll format that to show what Day week it is and grab the fill handle down.
I have a nice range of every Monday through Friday date = WEEKDAY.
Says check this date.
I'll press F4 three times using this return type press F4 twice.
Alright!
So, return types are 1 2 & 3.
We will copy that down and copy it over.
Alright! So, you see that the weekday function has different return types.
So, one of them says that Sunday is the one going through Saturday being seven.
Another one says that Monday is a one going through Sunday being seven and then yet another one has Monday at 0 going through Sunday as 6.
You know if you don't want to memorize these, here's just a cool way to go.
Let's use the MOD function.
Take the date, divide by 7 and get the remainder.
Now, we'll have to format that as a number.
Copy that down, and we can just look.
We want Monday, Wednesday and Friday.
That's where the result of this function is either a 2, a 4 or 6 2, 4, 6..
Alright! So, here is what I am going to do.
I'm going to switch over to VBA.
I'm going to create a new custom Function.
So, instead of starting with the word sub, I started off with the word function, the name of my function and the arguments.
I want a 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 to within the function.
I'm going to create a little loop here.
for i =StartDate to EndDate; Select Case I want to do the mod function.
So, start or actually i Mod 7 That's going to return a number from a zero to seven.
The only time.
So, I care about are: where the result is 2, 4, 6.
So, case 2, 4, 6 In those cases, MWF = MWF + 1.
End Select and Next i Alright! Now, we have a nice little function here the Monday Wednesday Friday Function.
Let's just give that a try.
Back here in our original data set =MWF the start Date comma the End Date.
And copy that down.
And now, that custom function, has figure out the number of just Monday when a Friday days.
We have very easily customize this for any days of the week.
Just put the result of the Mod Function that you want.
So, for example, we wanted to add in Thursday.
We would add 5, and now we're getting Thursdays as well and so on.
So, just a quick little bit of VBA code there.
Again, as I mentioned on Tuesday, the Livelessons power Excel Macro is the product from Q.
Will teach you how to write code.
Now, tomorrow where I look at an insane formula, it's going to solve this problem without using any VBA.
Whatsoever actually it's a very clever formula.
So thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
So, how we're going to analyze as well as file up a pivot table.
Let's see if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday, we solve Shawn's problem, and I was looking at a data set and I mentioned that big huge Formula.
That was in the Excel Gurus Gone Wild book and I just 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.
So, it let's say that we have to build for both the start date and the end date if we're just trying to figure out the number of Days, it's pretty simple.
The later date minus the earlier date is going to say that there's one day that's elapsed.
And so, we want to add one to that and Double-Click to send that formula down.
And we can 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 =NETWORKDAYS NETWORKDAYS...
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 to Saturday 7/5/2008.
Which was a Saturday.
You said there are three days elapsed but only two work days.
Isn't that cool?
Now, NETWORKDAYS is always available in Excel 2007.
In excel 2003, how to make sure that the analysis toolpak was turned on?
Go to Tools>add-ins and check the box for Analysis Tool-pack.
NETWORKDAYS is really cool.
And that you can also give it a third argument that says hey, "want to ignore certain company holidays".
So, out here w'll enter a Formula and optional third argument, will come out.
And point to this range that has holidays, I'll press F4 and let's see it worth an extra equal sign.
And you see that because July 4th in the united states is a holiday, it now excludes that.
So, very cool cool way to go.
Alright! Now I'm going to throw a curve ball at you.
We want to count the number of Mondays, Wednesdays and Fridays in there.
You know maybe someones part time they only work those three days.
So, take out the Monday, Wednesday, Friday days.
It's alright.
Well, first of all I have to wrap my head around the weekday function.
I can never remember how this works, so we'll put in today's date.
I'll format that to show what Day week it is and grab the fill handle down.
I have a nice range of every Monday through Friday date = WEEKDAY.
Says check this date.
I'll press F4 three times using this return type press F4 twice.
Alright!
So, return types are 1 2 & 3.
We will copy that down and copy it over.
Alright! So, you see that the weekday function has different return types.
So, one of them says that Sunday is the one going through Saturday being seven.
Another one says that Monday is a one going through Sunday being seven and then yet another one has Monday at 0 going through Sunday as 6.
You know if you don't want to memorize these, here's just a cool way to go.
Let's use the MOD function.
Take the date, divide by 7 and get the remainder.
Now, we'll have to format that as a number.
Copy that down, and we can just look.
We want Monday, Wednesday and Friday.
That's where the result of this function is either a 2, a 4 or 6 2, 4, 6..
Alright! So, here is what I am going to do.
I'm going to switch over to VBA.
I'm going to create a new custom Function.
So, instead of starting with the word sub, I started off with the word function, the name of my function and the arguments.
I want a 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 to within the function.
I'm going to create a little loop here.
for i =StartDate to EndDate; Select Case I want to do the mod function.
So, start or actually i Mod 7 That's going to return a number from a zero to seven.
The only time.
So, I care about are: where the result is 2, 4, 6.
So, case 2, 4, 6 In those cases, MWF = MWF + 1.
End Select and Next i Alright! Now, we have a nice little function here the Monday Wednesday Friday Function.
Let's just give that a try.
Back here in our original data set =MWF the start Date comma the End Date.
And copy that down.
And now, that custom function, has figure out the number of just Monday when a Friday days.
We have very easily customize this for any days of the week.
Just put the result of the Mod Function that you want.
So, for example, we wanted to add in Thursday.
We would add 5, and now we're getting Thursdays as well and so on.
So, just a quick little bit of VBA code there.
Again, as I mentioned on Tuesday, the Livelessons power Excel Macro is the product from Q.
Will teach you how to write code.
Now, tomorrow where I look at an insane formula, it's going to solve this problem without using any VBA.
Whatsoever actually it's a very clever formula.
So thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
