In Today's Dueling Podcast Mike 'ExcelIsFun' Girvin and Bill 'MrExcel' Jelen look at Counting The Number of Days Between Two Dates - not including Saturdays. The Duel becomes a Team Effort today as Mike and Bill work with a variety of Functions.
Transcript of the video:
Hey! Welcome back, it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
We'll be joined live in the same room with by Mike Girvin from Excel Is Fun, check this out a new book is here.
I just flew across the country to bring Mike the first box.
Most publishers would just use UPS, but hey! We're here.
So, let's do some dueling Excel podcast.
Now, this question is send in by Imran at Facebook, has two dates needs to count, how many days are between those two dates.
Not counting Saturdays.
Alright!
So, there's a Saturday.
It's out of there.
So, normally what we do is the later date minus the earlier date plus one, would give us the number date.
So, here on the 27 to 28, you see there's two days.
Simple enough, right.
Double click to shoot that down.
Alright! Now, if we need to get the number of Saturdays.
That formula would make my head hurt.
So, I'm gonna cheat.
I think and do equal net work days international from the earlier date comma to the later date comma up and then check this out.
There's a way to count this Saturdays only.
This is doing Excel 2010, [ tab ], close parentheses, double click to shoot that down.
Alright! Number Saturdays is the first calculation minus the other calculation.
Double click, all right!
Let's just do a little test here.
You know what would make this a lot easier, if I come back here and change these two long dates.
There we go, okay!
So, from Sunday to Monday no Saturdays there.
That worked out perfectly.
This one Thursday to Thursday, there should be one Saturday.
Yep! Friday to a week from Saturday, two Saturdays that works, all right!
Mike! Let's see what you have.
Mike: Wow! I gotta love this new function in 2010.
No, not that one.
This one right there, networking days international, and you can tell it the weekend is Saturday.
Oh man! That's worth getting 2010, right there.
Alright! I'm actually gonna zoom in, just a little bit here.
So, I can see everything on the screen, all right!
I'm going to use the weekday function equals weekday.
What's great about weekdays, you can give it a serial number.
It's expecting a single serial number in old to give you the number 1 to 7.
7 will be a Saturday.
So, serial number. Oh, but I have a start and an end and I really need a whole range in between.
So, I'm going to use the Row function and then inside the Row function.
I'm going to use indirect.
Now indirect, we can give it Text.
So, I'm going to create from this begin date to this end date, arranged and then the row will convert it.
The indirect will convert it to a reference and row will convert it to numbers.
I didn't say that very well, but let's go ahead start ampersand, colon just like a regular range and then the return date.
Now, if I highlight this and hit [ F9 ].
Evaluate, you can see sure enough it gives us our two dates.
That's an array created, inside the formula [ ctrl Z ].
Now, return type 1 through 7, you could pick whichever one you want.
Default is one and that means one for Sunday, seven is Saturday.
So, I'm going to leave that argument off.
Now, right now that will give me.
[ F9 ] They give me a 1 and a 2.
It's going to give me an array of those numbers representing which day [ ctrl + Z ], and I'm simply going to say, hey!
I want to see which ones are greater than 6, that means 7 is the only day greater than 6.
Now, that will give me trues and falses and I want ones and zeros.
So, I'm going to wrap a double negative and put all of this inside of parentheses.
If I hit that [ F9 ], it gives me two zeros because there are no Saturday's [ ctrl Z ].
I want less than 6, let's try this again no less than MrExcel: Less than 7.
Mike: Less than 7.
Teamwork, all right! let's...
[ F9 ], there we go because we want to exclude the seven.
[ Ctrl Z ] and I'll just put it inside of sum product.
[ Ctrl Enter ], double click and send it down.
MrExcel: Wait, why sum product went out sum simple wrapper function.
Mike: Well first off. Let's get the number of Saturday's.
It is actually.
MrExcel: Oh yeah! Greater than 6.
Mike: I'm going to notice this whole range is highlighted that active.
So, right through and hit [ F2 ].
It's actually, seven is what we want to exclude.
So, MrExcel: And we're counting the number of Saturdays.
You want to know how many Saturdays So, do equal 7.
Mike : Equal 7 [ ctrl enter ] to populate all those formulas.
Now, why the sum product instead of sum?
Well, if I have sum, there's an array.
This is an array right here and sumwould require [ ctrl shift enter ].
Sure enough you could do that.
There's the curly brackets double click and send it down.
But anytime, I'm adding and I have an array.
Why not use sum product, because it doesn't require [ ctrl **** enter ].
Double click and send it down, alright!
We made it through that one with the little teamwork.
Alright!
MrExcel: Well, hey all right!
I want to thank you for stopping by, we'll see you next time for another dueling Excel podcast from MrExcel and...
Mike:Excel Is Fun.
I'm Bill Jelen from MrExcel.
We'll be joined live in the same room with by Mike Girvin from Excel Is Fun, check this out a new book is here.
I just flew across the country to bring Mike the first box.
Most publishers would just use UPS, but hey! We're here.
So, let's do some dueling Excel podcast.
Now, this question is send in by Imran at Facebook, has two dates needs to count, how many days are between those two dates.
Not counting Saturdays.
Alright!
So, there's a Saturday.
It's out of there.
So, normally what we do is the later date minus the earlier date plus one, would give us the number date.
So, here on the 27 to 28, you see there's two days.
Simple enough, right.
Double click to shoot that down.
Alright! Now, if we need to get the number of Saturdays.
That formula would make my head hurt.
So, I'm gonna cheat.
I think and do equal net work days international from the earlier date comma to the later date comma up and then check this out.
There's a way to count this Saturdays only.
This is doing Excel 2010, [ tab ], close parentheses, double click to shoot that down.
Alright! Number Saturdays is the first calculation minus the other calculation.
Double click, all right!
Let's just do a little test here.
You know what would make this a lot easier, if I come back here and change these two long dates.
There we go, okay!
So, from Sunday to Monday no Saturdays there.
That worked out perfectly.
This one Thursday to Thursday, there should be one Saturday.
Yep! Friday to a week from Saturday, two Saturdays that works, all right!
Mike! Let's see what you have.
Mike: Wow! I gotta love this new function in 2010.
No, not that one.
This one right there, networking days international, and you can tell it the weekend is Saturday.
Oh man! That's worth getting 2010, right there.
Alright! I'm actually gonna zoom in, just a little bit here.
So, I can see everything on the screen, all right!
I'm going to use the weekday function equals weekday.
What's great about weekdays, you can give it a serial number.
It's expecting a single serial number in old to give you the number 1 to 7.
7 will be a Saturday.
So, serial number. Oh, but I have a start and an end and I really need a whole range in between.
So, I'm going to use the Row function and then inside the Row function.
I'm going to use indirect.
Now indirect, we can give it Text.
So, I'm going to create from this begin date to this end date, arranged and then the row will convert it.
The indirect will convert it to a reference and row will convert it to numbers.
I didn't say that very well, but let's go ahead start ampersand, colon just like a regular range and then the return date.
Now, if I highlight this and hit [ F9 ].
Evaluate, you can see sure enough it gives us our two dates.
That's an array created, inside the formula [ ctrl Z ].
Now, return type 1 through 7, you could pick whichever one you want.
Default is one and that means one for Sunday, seven is Saturday.
So, I'm going to leave that argument off.
Now, right now that will give me.
[ F9 ] They give me a 1 and a 2.
It's going to give me an array of those numbers representing which day [ ctrl + Z ], and I'm simply going to say, hey!
I want to see which ones are greater than 6, that means 7 is the only day greater than 6.
Now, that will give me trues and falses and I want ones and zeros.
So, I'm going to wrap a double negative and put all of this inside of parentheses.
If I hit that [ F9 ], it gives me two zeros because there are no Saturday's [ ctrl Z ].
I want less than 6, let's try this again no less than MrExcel: Less than 7.
Mike: Less than 7.
Teamwork, all right! let's...
[ F9 ], there we go because we want to exclude the seven.
[ Ctrl Z ] and I'll just put it inside of sum product.
[ Ctrl Enter ], double click and send it down.
MrExcel: Wait, why sum product went out sum simple wrapper function.
Mike: Well first off. Let's get the number of Saturday's.
It is actually.
MrExcel: Oh yeah! Greater than 6.
Mike: I'm going to notice this whole range is highlighted that active.
So, right through and hit [ F2 ].
It's actually, seven is what we want to exclude.
So, MrExcel: And we're counting the number of Saturdays.
You want to know how many Saturdays So, do equal 7.
Mike : Equal 7 [ ctrl enter ] to populate all those formulas.
Now, why the sum product instead of sum?
Well, if I have sum, there's an array.
This is an array right here and sumwould require [ ctrl shift enter ].
Sure enough you could do that.
There's the curly brackets double click and send it down.
But anytime, I'm adding and I have an array.
Why not use sum product, because it doesn't require [ ctrl **** enter ].
Double click and send it down, alright!
We made it through that one with the little teamwork.
Alright!
MrExcel: Well, hey all right!
I want to thank you for stopping by, we'll see you next time for another dueling Excel podcast from MrExcel and...
Mike:Excel Is Fun.
