12 Replies Latest reply on Sep 16, 2016 9:48 AM by kschjei

# How many days when you have multiple exit dates

Help! I have no idea how to write this calculation!

I need to find out the number of days a student was with us.

Students have a

Start Date

Exit Date

Most Current Exit Date

They may exit and reenter several times during the year and can be gone for days, weeks or months at a time.

Can anyone help me with this?

Thanks!

• ###### 1. Re: How many days when you have multiple exit dates

Hi,

can you lay out a few scenarios for us please?  Like the simplest and the most complex - and how you'd solve them on paper.  Thanks.

• ###### 2. Re: How many days when you have multiple exit dates

The student enters on 9-1-16

The student exits on 9-15-16

He was enrolled for 15 days

He came back on 10-10-15

He exited again on 11-10-15

He was enrolled another 30 days

Total days = 45

Does that help?

• ###### 3. Re: How many days when you have multiple exit dates

How are you tracking the multiple entries? Do you create a new record for the same student when they re-enter after exiting previously?

Regardless of how you've structured your data, it might help that:

( 20 - 15 ) + ( 30 - 25 )

is equal to

( 20 + 30 ) - ( 15 + 25 )

i.e. if "20" and "30" are your end dates and "15" and "25" are your start dates.

In other words, if you add up all the end dates into one variable, and add up all the start dates into another variable, then subtract one from the other, that should give you the total enrolled time.

- RG>

• ###### 4. Re: How many days when you have multiple exit dates

You would need a table with one record for each pair of entry and exit dates.  Then you would have a calculation (in that table) equal to:  end date - start date + 1 (plus one in order to make it inclusive).  Finally, in the parent table that same table, you would have a summary field that was the sum of the related calculation field - a total of all days enrolled.

1 of 1 people found this helpful
• ###### 5. Re: How many days when you have multiple exit dates

ditto. Like newspaper subscriptions. You need a table to store every entry and exit date. You can then get elapsed days, total elapsed days, etc. You can even figure how many days were "missing".

beverly

• ###### 6. Re: How many days when you have multiple exit dates

1. Create two repeating fields, one for start and leave.

2. Create a calculated repeating field, subtracting the leave from the start using Get ( CalculationRepetitionNumber )

3. Create new field to sum the calculated field you just made. (e.g SUM ( field)

I have attached an example DB to this post, as I appreciate the brief instructions are quite vague. If you have any questions, please give me a shout. I'm not stating this is the best method, but it does work. I can also help you adapt this to you current setup if needed.

EDIT: Just realised I didn't change the running total summary field into a SUM calc before uploading. Please read instructions above and amend calculation accordingly.

• ###### 7. Re: How many days when you have multiple exit dates

Yes they are all separate records in a table. So what would the calculation look like for that?

• ###### 8. Re: How many days when you have multiple exit dates

ohhhh I think I understand. I will report back

• ###### 9. Re: How many days when you have multiple exit dates

the repeating field may have it's uses, but not here, as the entry/exit may have more than repeats. it would be better to have a table (related) for these values and related to the student.

1 of 1 people found this helpful
• ###### 10. Re: How many days when you have multiple exit dates

And, his example is confusing, if

The student enters on 9-1-16

The student exits on 9-15-16

He was enrolled for 15 days

this is "inclusive" (15-1+1=15), but

He came back on 10-10-15

He exited again on 11-10-15

He was enrolled another 30 days

is not. (Oct has 31 days, then if count it inclusive the result is 32 days)

Are there difference between "entry" and "come back" ?

1 of 1 people found this helpful
• ###### 11. Re: How many days when you have multiple exit dates

Oh, yeah ... good point user19752

fortunately FM cyphers it properly:

Date(11;10;2016) - Date(10;10;2016) + 1 = 32

(And one can rely on it to deal with leap years to boot.)

• ###### 12. Re: How many days when you have multiple exit dates

Thanks to all of you! I love the Filemaker community and how everyone pitches in to help others!

Kerry