Patricia called the other day with a problem which will be familiar to everyone who works in a public school district. The state pays the school for every student who is enrolled on the magic date of October 1. Given a start date and end date, how can you tell if the date is in that range? Episode 1133 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.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, today a question, this was a call, I was driving in the car, got a call from Patricia.
Patricia works at a school in Arizona, think it was Patricia, and I recognized the question immediately!
She says she has a start date and an end date, she needs to figure out how many of those students were enrolled on October 1st.
That's a very common problem, our school here in Ohio, I know that we get a payment for every student who's enrolled on October 1st, provided they show up by October, that's great.
Kids that move into the district on October 2nd, you know, we're supposed to educate them for free, so go figure, their last school district got the money.
Kind of a strange way to do it, but I recognize the question right away.
So here's what we talked about on the phone with Patricia.
First thing I want to do, is I want to do an IF statement, of course, right, IF.
But there's two conditions we have to check for so, I'm going to use an AND function.
And I'm going to check that earlier date, the start date in A2, see if that's <= my date up here in the blank cell, the formerly-blank cell.
And we press F4 to lock that down, because as we copy that down, I want the A2 to be relative, but the E1 has to be frozen.
And then in the next condition, we'll see if the date in B2 is >= that date, will press F4, close the end.
And now, well, what to do if it's TRUE, I'm going to put a 1, if it's TRUE I put a 1, and otherwise I put a 0.
That way we can just count how many we get, sum this up basically.
So I have all my students there, it looks like the sum is 5 down here in the lower left-hand corner, so a great way to go.
Now what we did, we said “You know, let's do a little test, let's just change the dates here on one of these test records, and make sure that it's working.” So there we go, that's good, if the person had left on 9/17/2008, then they're not in.
So there you have it, great way to go, very common problem for everyone in a school district, we have to measure that.
Kind of a tougher formula than you think it might be, but certainly a good way to go.
Well I want to thank you for stopping by, and we'll, see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, today a question, this was a call, I was driving in the car, got a call from Patricia.
Patricia works at a school in Arizona, think it was Patricia, and I recognized the question immediately!
She says she has a start date and an end date, she needs to figure out how many of those students were enrolled on October 1st.
That's a very common problem, our school here in Ohio, I know that we get a payment for every student who's enrolled on October 1st, provided they show up by October, that's great.
Kids that move into the district on October 2nd, you know, we're supposed to educate them for free, so go figure, their last school district got the money.
Kind of a strange way to do it, but I recognize the question right away.
So here's what we talked about on the phone with Patricia.
First thing I want to do, is I want to do an IF statement, of course, right, IF.
But there's two conditions we have to check for so, I'm going to use an AND function.
And I'm going to check that earlier date, the start date in A2, see if that's <= my date up here in the blank cell, the formerly-blank cell.
And we press F4 to lock that down, because as we copy that down, I want the A2 to be relative, but the E1 has to be frozen.
And then in the next condition, we'll see if the date in B2 is >= that date, will press F4, close the end.
And now, well, what to do if it's TRUE, I'm going to put a 1, if it's TRUE I put a 1, and otherwise I put a 0.
That way we can just count how many we get, sum this up basically.
So I have all my students there, it looks like the sum is 5 down here in the lower left-hand corner, so a great way to go.
Now what we did, we said “You know, let's do a little test, let's just change the dates here on one of these test records, and make sure that it's working.” So there we go, that's good, if the person had left on 9/17/2008, then they're not in.
So there you have it, great way to go, very common problem for everyone in a school district, we have to measure that.
Kind of a tougher formula than you think it might be, but certainly a good way to go.
Well I want to thank you for stopping by, and we'll, see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
