Column A contains a start date. Column B contains an end date. You need to calculate how many days occur in each month of the program. In Episode 829, we learn how to coerce an array of dates from those two cells.
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.
Boy, we have a tough question here sent in by Jonathan.
Jonathan has a program START date and a program END date, and the way that this works is that can span up to 60 days which means that it could include 1, 2, or 3 months, and he has a certain program cost and he needs to prorate that cost into each of the monthly buckets.
So, you know, in this case, we have part of April, part of May, and he needs to allocate that cost.
We need to figure out there's 16 days in April, 14 days in May.
So, that way, April gets 16 30th and May get 14 30th.
This is a really tough question, but there's an amazing feature, and I'm going to start to talk about this feature today and then we'll bring it home tomorrow.
I’m going to come down here to a lower section of the spreadsheet where I'm just going to have basically 5 days, the 15th, 16th, 17th, 18th, and 19th, 5 days, and I'm going to use the INDIRECT.
I'm going to ask for the INDIRECT and I'm going to build a reference that's A35 -- so, in other words, that date in A35 -- a :, and then the date in B35.
[ =SUM(INDIRECT(A35“:”&B35)) ] Now, of course, Excel shows us 4/15, but what's it really storing there -- if I press CONTROL and then the ` accent -- you'll see that what it is storing is 39553 through 39557.
So, if I come over and look at this formula, and use the EVALUATE FORMULA icon, what we're going to get is we’re building a reference to the rows, the 5 rows, 39553 through 39557.
This is an amazing way to take 2 cells and coerce it into a large array.
In this case, that contains 5 cells, but, in reality, it might contain up to 60 cells in Jonathan's case.
[ =SUM(INDIRECT(“39553:”&39557)) ] So, let me close this and show you what we would do with that.
In the next formula, I use that same indirect, but, for every item in the indirect, I ask for the row number.
Now, basically, that's going to convert from 5 cells to 5 numbers and it's going to be the five numbers from 39553 through 39559.
We’ll EVALUATE, EVALUATE, and you see that I end up with an array here with the 5 dates represented as numbers.
[ =SUM(ROW(INDIRECT(A36“:”&B36))) ] Now, with that, I can start to do some cool things, and we'll take a look at how we can use that information in tomorrow’s netcast.
Well, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Boy, we have a tough question here sent in by Jonathan.
Jonathan has a program START date and a program END date, and the way that this works is that can span up to 60 days which means that it could include 1, 2, or 3 months, and he has a certain program cost and he needs to prorate that cost into each of the monthly buckets.
So, you know, in this case, we have part of April, part of May, and he needs to allocate that cost.
We need to figure out there's 16 days in April, 14 days in May.
So, that way, April gets 16 30th and May get 14 30th.
This is a really tough question, but there's an amazing feature, and I'm going to start to talk about this feature today and then we'll bring it home tomorrow.
I’m going to come down here to a lower section of the spreadsheet where I'm just going to have basically 5 days, the 15th, 16th, 17th, 18th, and 19th, 5 days, and I'm going to use the INDIRECT.
I'm going to ask for the INDIRECT and I'm going to build a reference that's A35 -- so, in other words, that date in A35 -- a :, and then the date in B35.
[ =SUM(INDIRECT(A35“:”&B35)) ] Now, of course, Excel shows us 4/15, but what's it really storing there -- if I press CONTROL and then the ` accent -- you'll see that what it is storing is 39553 through 39557.
So, if I come over and look at this formula, and use the EVALUATE FORMULA icon, what we're going to get is we’re building a reference to the rows, the 5 rows, 39553 through 39557.
This is an amazing way to take 2 cells and coerce it into a large array.
In this case, that contains 5 cells, but, in reality, it might contain up to 60 cells in Jonathan's case.
[ =SUM(INDIRECT(“39553:”&39557)) ] So, let me close this and show you what we would do with that.
In the next formula, I use that same indirect, but, for every item in the indirect, I ask for the row number.
Now, basically, that's going to convert from 5 cells to 5 numbers and it's going to be the five numbers from 39553 through 39559.
We’ll EVALUATE, EVALUATE, and you see that I end up with an array here with the 5 dates represented as numbers.
[ =SUM(ROW(INDIRECT(A36“:”&B36))) ] Now, with that, I can start to do some cool things, and we'll take a look at how we can use that information in tomorrow’s netcast.
Well, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
