AnsweredAssumed Answered

How do I Automate the Collection of Data with a Calculation

Question asked by joshuaw07 on Apr 28, 2017
Latest reply on May 1, 2017 by StylisticGambit

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.


Screen Shot 2017-04-28 at 9.25.17 AM.png fig.1


Screen Shot 2017-04-28 at 9.31.27 AM.png fig.2