Hey! Welcome back to the Mr. Excel Net cast, I’m Bill Jelen. Well today a question, this was a call. I was driving in the car I got a call from Patricia. Patricia works at a school in Arizona, think it was Patricia. A school in Arizona and I recognized the question immediately she says she has a start date and an end date. She needs if 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 students whose enrolled on October 1st. Provided that they show up on October, that’s great! Kids are moving to the district on October 2nd, and we are supposed to educate them for free. So go figure their last school district and count them in. Kind of a strange way to do it, but I recognize the question right away.
Here’s what we talked about on the phone. Patricia, first thing I want to do is I want to do an “if statement” of course, write if”. But there are two conditions we have to check for. I’m going to use an “and” function, and I’m going to check that earlier date, the start date in A2. See if it is less than, or equal to my date up here in the blank cell, the formula 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 the next condition, we will see if the date in B2 is greater than or equal to that day then we press F4, close the end.
And now 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 zero. That way we can just count how many we get, sum this up basically. So I have all my X students there, looks like the sum is 5 down here in little left hand corner, a great way to go. Now what we did, we said, you know let’s do a little test, let’s just change some dates here on one of this test records. And make sure that it is working there we go that’s good, if the first on left on 9/17/2008 then they are not in.
There you have it, great way to go. A very common problem for everyone in a school district, you have to measure that. Kind of a tougher formula and you think it might be but certainly have a good way to go. I want to thank you for stopping by. We will see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services