Continuing the discussion from Episode 829, I show how to use the array of dates from a start date to an end date in order to perform a calculation on each date in the array. Episode 830 shows you how.
This blog is the video 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 blog is the video 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.
Now, you have to watch yesterday's podcast.
If you didn't watch yesterday, you’re going to be completely lost today.
So, go back and watch that one first.
We're trying to answer Jonathan's question here.
He has a program START date and a program END date, and COSTS, and need to allocate those costs from up to 3 months that could be encompassed from the start date to the end date.
The program generally runs 45 to 60 days.
So, I hit him with, well, hey, what if it started at 8/30?
That may not end till October, so then we have August, September, and October.
He conceded there could be up to 3.
Here's the formula I have and we're using the formula from yesterday, basically where we're going to take the INDIRECT of the first date, a :, and the second date.
Well, what's that going to do for us?
Excel actually stores those dates as a serial number, and let me just cruise down here to show you what's happening virtually in memory.
Now, this doesn't actually happen in these rows.
I'm just building this to illustrate what's happening.
[ =SUM(IF(MONTH(ROW(INDIRECT($A18&“:”&$B18)))=MONTH(D18),1,0)) ] When we ask for the INDIRECT of the first date to the last date, Excel’s going to convert those to serial numbers, and, in this case, it happens to be 39567 through 39571, and then we ask for the row of those serial numbers.
So, in this case, it's only 5 days.
It's going to give me the row function.
Basically, these 5 numbers will be evaluated in memory, and then I ask for the month of those numbers.
Well, that converts it back to a day and it shows me that these first 2 numbers happen in April, the fourth month, and these 3 numbers happen in May.
Okay.
Well, now what do I do?
Then, I use an IF statement.
It says if that month happens to be = to the month in D18, which let’s say is 4, the first month, April, then put a 1, otherwise a 0.
So, what we end up with is an array of 5 numbers.
The first 2 numbers are 1s because they fell in April and the next 3 numbers are 0 because they fell in May.
Finally, I take the sum of that whole thing.
That's like this little formula down here, the sum formula, that says that we have 2 dates that fell in April, and then, in the next cell, I check to see if it's = to the month of E18, which is May.
So, that's like this column where we said, hey, is that month over there in column B = to 5, and if it is, then use a 1, and so I find out that there's 3 dates that happened in May.
So, we'll come back up.
Now, of course, we have to press CONTROL+SHIFT+ENTER to force Excel to evaluate this as an array, but this horrendous formula will figure out exactly how many days we have.
So, let's come down here to that example where you have August 30th.
2 days -- the 30th and 31st -- fell in the first month, 30 days fell in September, and then 16 days fell in October.
Now, to finish out, John, it's just basic blocking and tackling.
We total the number of days here and then allocate the cost.
So 2 48th * the cost of 1190 shows that 49.58 gets allocated to the first month, and then we could build a little summary table that would add all these up.
The real tough part here though is basically what we talked about in yesterday's podcast where we take these 2 cells, these 2 cells here -- so 8/30 and 10/16 -- and basically coerce that into an array of 48 different numbers, and then, using the array formula, we can check the month of each one, see if it's = to the month that we're looking for, and then sum that whole thing up.
An amazing, amazing formula.
Incredibly tough.
I want to thank John for sending that in and thank you for stopping by.
We’ll see you on Monday for hopefully something far simpler.
We’ll see you next time for another netcast from MrExcel.
Now, you have to watch yesterday's podcast.
If you didn't watch yesterday, you’re going to be completely lost today.
So, go back and watch that one first.
We're trying to answer Jonathan's question here.
He has a program START date and a program END date, and COSTS, and need to allocate those costs from up to 3 months that could be encompassed from the start date to the end date.
The program generally runs 45 to 60 days.
So, I hit him with, well, hey, what if it started at 8/30?
That may not end till October, so then we have August, September, and October.
He conceded there could be up to 3.
Here's the formula I have and we're using the formula from yesterday, basically where we're going to take the INDIRECT of the first date, a :, and the second date.
Well, what's that going to do for us?
Excel actually stores those dates as a serial number, and let me just cruise down here to show you what's happening virtually in memory.
Now, this doesn't actually happen in these rows.
I'm just building this to illustrate what's happening.
[ =SUM(IF(MONTH(ROW(INDIRECT($A18&“:”&$B18)))=MONTH(D18),1,0)) ] When we ask for the INDIRECT of the first date to the last date, Excel’s going to convert those to serial numbers, and, in this case, it happens to be 39567 through 39571, and then we ask for the row of those serial numbers.
So, in this case, it's only 5 days.
It's going to give me the row function.
Basically, these 5 numbers will be evaluated in memory, and then I ask for the month of those numbers.
Well, that converts it back to a day and it shows me that these first 2 numbers happen in April, the fourth month, and these 3 numbers happen in May.
Okay.
Well, now what do I do?
Then, I use an IF statement.
It says if that month happens to be = to the month in D18, which let’s say is 4, the first month, April, then put a 1, otherwise a 0.
So, what we end up with is an array of 5 numbers.
The first 2 numbers are 1s because they fell in April and the next 3 numbers are 0 because they fell in May.
Finally, I take the sum of that whole thing.
That's like this little formula down here, the sum formula, that says that we have 2 dates that fell in April, and then, in the next cell, I check to see if it's = to the month of E18, which is May.
So, that's like this column where we said, hey, is that month over there in column B = to 5, and if it is, then use a 1, and so I find out that there's 3 dates that happened in May.
So, we'll come back up.
Now, of course, we have to press CONTROL+SHIFT+ENTER to force Excel to evaluate this as an array, but this horrendous formula will figure out exactly how many days we have.
So, let's come down here to that example where you have August 30th.
2 days -- the 30th and 31st -- fell in the first month, 30 days fell in September, and then 16 days fell in October.
Now, to finish out, John, it's just basic blocking and tackling.
We total the number of days here and then allocate the cost.
So 2 48th * the cost of 1190 shows that 49.58 gets allocated to the first month, and then we could build a little summary table that would add all these up.
The real tough part here though is basically what we talked about in yesterday's podcast where we take these 2 cells, these 2 cells here -- so 8/30 and 10/16 -- and basically coerce that into an array of 48 different numbers, and then, using the array formula, we can check the month of each one, see if it's = to the month that we're looking for, and then sum that whole thing up.
An amazing, amazing formula.
Incredibly tough.
I want to thank John for sending that in and thank you for stopping by.
We’ll see you on Monday for hopefully something far simpler.
We’ll see you next time for another netcast from MrExcel.
