Welcome back to the Mr. Excel net cast. I’m Bill Jelen. Hey, there’s new book Excel Gurus Gone Wild. Now, every time I’m in front of anyone and I’m counting this is being in front of you, I beg people not to buy this book. Wow, it is filled with arcane stuff. It’s maybe 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 that is about Excel, wow then that’s the book for you.
Most people buy one of the other books the way to go now. Yesterday, we talked about how to figure out, how Monday, Wednesdays, Fridays were between a start date and end date and I use to custom VBA function because that’s what I always do. I always just fire up VBA but there’s an amazing way to do this. Now, the item of this book came from Mr. Excel message board. So the smartest Excel people around working on these problems and they come amazing formula. And when I saw this formula, I was fascinated by how it works.
So we have start date and an end date. I want to see how Excel is storing that. So I’m going to press control and the accent key, the groove accent. And you see that this is stored us the number of days as January 01, 1900. So we have two numbers there, 39, 632, 39, 634. 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 into numbers.
Now, check this out. I’m going to use the evaluate formula. So we’ll come to formulas and we’ll do evaluate for that and we’ll watch how this gets changed. So we evaluate the first date and then the second date. And then indirect of that whole thing is going to referred 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 a ray of those one, two, three dates.
Now here in this formula, I’m just doing something simple like summing it. But I want to show you what happens when we used 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, go away out, all of a sudden we ask 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—it into this big huge ray. Now, what can we do once you have that ray? 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 ray. We’ll take the mod of that date divided by seven. I was going to take each individual in a ray, add one to it because the mod is going to give us number from zero to six. And then go see if it’s Monday, a Wednesday or Friday using the choose function and finally sum 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 ray—we’ll 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, I’m going to do 09/01/2008 then miraculously that cool trick where we take the indirect of the first date of the later date, check the row of the whole thing, turns into again in a ray of a hundred dates. The choose is going to turn into at a ray of zeros and ones, only the Monday, Wednesday and Fridays get ones, just a beautiful, beautiful formula and then finally sum the whole thing up to get them Monday, Wednesday, Friday dates. So you can very easily change this formula by changing the zeros and ones and we’re here to correspond to the days that you’ve want just an amazing bit of a code.
And again, the thing that I love is taking the indirect at 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 ray that the formula can work. Now of course, this is a ray formula so you can’t just press enter. You have to use control shift enter to tell Excel that we are dealing with one of these super magic a ray formulas.
There you have it, a great solution, very esoteric to be honest. But it’s a problem you have, a great trick to have in your tool bag. Again, the Excel Gurus Gone Wild book filed with just the bazaar and our came, but sometimes incredibly useful formulas like this one. I want to thank you for stopping by. We’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services