I work with an Academy that has been serving students since 2001. I am combing through the archives and have to determine how many students were actively enrolled each month.
The only data I have is:
- student name
- an enrollment date
- and a discharge date.
Every month in between the enrollment date and discharge date is assumed the student was actively enrolled into the academy.
So in fig.1 below a student was enrolled in january 15th and was discharged at the end of the semester in April.
I need a formula that will automatically return:
- "Active" every month in between enrollment date and discharge date
- "Inactive: for every month after discharge date
- and a "blank" field for every month prior to enrollment
So I in fig1. below, which i manually entered, we see a student that attended our school for the summer semester. He:
- enrolled on May 15th 2001 (thus each month is blank leading up to May.
- attended our school in the summer of 2001 (resulting in each summer month being marked active)
- and discharged in August 15th 2001 ( resulting in every month for the remainder of the year being marked inactive)
Once I can collect this data, I can create a summary field for each month to count how many "active students" as seen in fig. 2
I can collect all this data manually, but i'm sure there is a calculation that can automate this task.