You can solve the MWF problem from episode 1018 using an incredible array formula from the book Excel Gurus Gone Wild. Episode 1019 takes a look at how to coerce an array of dates from a start date and end date cell.
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 Jump.
Basically, we start out with massive amount of data.
So, how we're gonna analyze as well plus file up a pivot table.
Let's see if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, this new book Excel Gurus Gone Wild...
Now, every time I'm in front of anyone...
And I'm counting this as being in front of you.
I begged people not to buy this book.
Wow! It is filled with arcane stuff.
It may be a thousand years before you find one instance, where you can use something cool from that book.
But if you're a real Excel guru, somebody loves just every weird thing there is about Excel.
Wow! then that's the book for you.
Most people buy one of the other books.
That's the way to go.
Now, yesterday, we talked about how to figure out having Monday Wednesday, Friday's were between a start date and an end date.
And I use the Custom VBA Function because that's what I always do.
I always says fire up VBA.
But there's an amazing way to do this now.
The items in this book came from the MrExcel message board.
So, you have the smartest Excel people around, working on these problems.
And they come with amazing formula.
So, when I saw this formula, I was fascinated by how it works.
So, we have a start date and end date.
I want to see how Excel is joining that.
So, I'm going to press CTRL and the accent key that grave accent.
And you see that it's stored as the number of days since January 1st, 1900.
So, we have Two numbers there 39632, 39634.
The formula takes advantage of something called INDIRECT.
So, we take the INDIRECT of B2 to C2.
So, we're saying the INDIRECT of 39632 ;39634.
And then using the ROW Function, to turn those dates in two numbers.
Now, check this out.
I'm going to use the EVALUATE Formula.
So, we'll come to Formulas and we'll do a Evaluate Formula.
And we'll watch how this gets changed So, we evaluate the first date and then the second date.
And now, INDIRECT of that whole thing is going to refer to three specific rows.
So, we have two cells but it grows into three rows.
And when we take the ROW Function, all of a sudden we have an array of those one, two, three dates.
Now, here in this forum, I'm just doing something simple like summing it, but I want to show you what happens when we use this in a larger range.
So, here we go from May 7th to September 1st.
I'm going to evaluate formula again.
And we'll Evaluate>Evaluate>Evaluate all the way out.
All of a sudden when we asked for the ROW Function, those two cells change into a hundred cells easily.
Look at that every single date from the start date to the end date.
That's amazing.
We're taking two cells and coercing it into this big huge array.
Now, what can we do once we have that array now?
I'm going to go on to the next worksheet, where I've actually written the Formula.
So, I say hey, we're going to take this big huge array will take the MOD of that date, divide by 7 and it's going to take each individual date in the array.
Add 1 to it because the mod is going to give us numbers from 0 to 6 and then go see if it's a Monday, Wednesday or Friday using the CHOOSE Function and finally some that whole thing up, So, here, we'll evaluate formula here.
And as we go through you see that eventually, we get down to our array.
And there will be three dates here and the ChOOSE then turns into a series of Zeros and ones.
And we have one Monday, Wednesday, Friday in that range.
But when we go down and Evaluate formula on a larger range, let be 9/1/2008.
Then miraculously that cool trick where we take the indirect of the first date to the later date, check the row of the whole thing turns into again on an array of 100 dates.
The CHOOSE is going to turn into a an array of Zeros [ and ] ones.
Only the Monday, Wednesdays and Fridays get 1's.
Just a beautiful, beautiful formula, and then finally some the whole thing up to get them Monday, Wednesday, Friday dates that you can very easily.
Change this Formula by changing the zeros and ones over here to correspond to the days that you want.
Just an amazing bit of code.
And again, the thing that I love is taking the INDIRECT of the first date to the later date.
Putting the ROW Function around that whole thing Which takes just two cells and turns it into a huge array.
That..
The formula can work in now.
Of course, this is an array formula.
So, you can't just press ENTER.
You have to use CTRL+SHIFT+ENTER, to tell Excel that we are dealing with one of these Super Magic Array Formulas.
There you have it.
A great solution!
Very esoteric to be honest, but if it's a problem that you have, a great trick to have in your tool bag.
Again the Excel Gurus Gone Wild book, filled with just the Bizarre and Arcane, but sometimes Incredibly useful formulas, like this one.
Want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jump.
Basically, we start out with massive amount of data.
So, how we're gonna analyze as well plus file up a pivot table.
Let's see if we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Hey, this new book Excel Gurus Gone Wild...
Now, every time I'm in front of anyone...
And I'm counting this as being in front of you.
I begged people not to buy this book.
Wow! It is filled with arcane stuff.
It may be a thousand years before you find one instance, where you can use something cool from that book.
But if you're a real Excel guru, somebody loves just every weird thing there is about Excel.
Wow! then that's the book for you.
Most people buy one of the other books.
That's the way to go.
Now, yesterday, we talked about how to figure out having Monday Wednesday, Friday's were between a start date and an end date.
And I use the Custom VBA Function because that's what I always do.
I always says fire up VBA.
But there's an amazing way to do this now.
The items in this book came from the MrExcel message board.
So, you have the smartest Excel people around, working on these problems.
And they come with amazing formula.
So, when I saw this formula, I was fascinated by how it works.
So, we have a start date and end date.
I want to see how Excel is joining that.
So, I'm going to press CTRL and the accent key that grave accent.
And you see that it's stored as the number of days since January 1st, 1900.
So, we have Two numbers there 39632, 39634.
The formula takes advantage of something called INDIRECT.
So, we take the INDIRECT of B2 to C2.
So, we're saying the INDIRECT of 39632 ;39634.
And then using the ROW Function, to turn those dates in two numbers.
Now, check this out.
I'm going to use the EVALUATE Formula.
So, we'll come to Formulas and we'll do a Evaluate Formula.
And we'll watch how this gets changed So, we evaluate the first date and then the second date.
And now, INDIRECT of that whole thing is going to refer to three specific rows.
So, we have two cells but it grows into three rows.
And when we take the ROW Function, all of a sudden we have an array of those one, two, three dates.
Now, here in this forum, I'm just doing something simple like summing it, but I want to show you what happens when we use this in a larger range.
So, here we go from May 7th to September 1st.
I'm going to evaluate formula again.
And we'll Evaluate>Evaluate>Evaluate all the way out.
All of a sudden when we asked for the ROW Function, those two cells change into a hundred cells easily.
Look at that every single date from the start date to the end date.
That's amazing.
We're taking two cells and coercing it into this big huge array.
Now, what can we do once we have that array now?
I'm going to go on to the next worksheet, where I've actually written the Formula.
So, I say hey, we're going to take this big huge array will take the MOD of that date, divide by 7 and it's going to take each individual date in the array.
Add 1 to it because the mod is going to give us numbers from 0 to 6 and then go see if it's a Monday, Wednesday or Friday using the CHOOSE Function and finally some that whole thing up, So, here, we'll evaluate formula here.
And as we go through you see that eventually, we get down to our array.
And there will be three dates here and the ChOOSE then turns into a series of Zeros and ones.
And we have one Monday, Wednesday, Friday in that range.
But when we go down and Evaluate formula on a larger range, let be 9/1/2008.
Then miraculously that cool trick where we take the indirect of the first date to the later date, check the row of the whole thing turns into again on an array of 100 dates.
The CHOOSE is going to turn into a an array of Zeros [ and ] ones.
Only the Monday, Wednesdays and Fridays get 1's.
Just a beautiful, beautiful formula, and then finally some the whole thing up to get them Monday, Wednesday, Friday dates that you can very easily.
Change this Formula by changing the zeros and ones over here to correspond to the days that you want.
Just an amazing bit of code.
And again, the thing that I love is taking the INDIRECT of the first date to the later date.
Putting the ROW Function around that whole thing Which takes just two cells and turns it into a huge array.
That..
The formula can work in now.
Of course, this is an array formula.
So, you can't just press ENTER.
You have to use CTRL+SHIFT+ENTER, to tell Excel that we are dealing with one of these Super Magic Array Formulas.
There you have it.
A great solution!
Very esoteric to be honest, but if it's a problem that you have, a great trick to have in your tool bag.
Again the Excel Gurus Gone Wild book, filled with just the Bizarre and Arcane, but sometimes Incredibly useful formulas, like this one.
Want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
