3 Replies Latest reply on Aug 31, 2011 1:55 PM by philmodjunk

# Calculation based on 2 date fields

### Title

Calculation based on 2 date fields

### Post

I making 2 tables.

One will have all of our available school dates.  Each date will have it's on unique date id.

From another table I will have:

Student name

level1 start date, total number of days on level1

level2 start date, total number of days on level2

Total days in Program

----------------------------------------------------------------------

What is the best way to get the (total number of days on level1/level2) based on the dates table?

If they have not started level2, I would like for it to count the number of available dates between the (level1 start date) and todays date, based on available dates in the dates table .

If they have started level2, I would like for it to count the number of available dates between the (level2 start date) and todays date, based on available dates in the dates table .

And for total days in program, I would like to just add the 2 total number of days fields

• ###### 1. Re: Calculation based on 2 date fields

Whenever you start adding multiple copies of fields to a table, you need to consider whether a related table will be a better option. The Level, start date, total number of days part of your description suggest such a possibility. If there is any chance that you either have more than 2 levels or that more such levels might be added in the future, I strongly recommend you consider that change.

Sticking with your current setup, you can use relationships with inequality operators to match to a range of dates in your SchoolDates table. The count of these records will then give you your total days.

First, we need a field that returns either today's date or the date that they left level1 for level2: This must be a field of type calculation, unstored with Date as it's return type:

cDateLeft1 : If ( Level2StartDate ; Level2StartDate ; Get ( currentDate ) )

and we'll use cToday : Get ( currentDate ) as well. This field must also have the same properties for type, return type and storage.

Then you can make two occurrences of SchoolDays, Level1Schooldays, Level2Schooldays and link them to your Student table like this:

Student::Level1StartDate < Level1Schooldays::date AND
Student::cDateLeft > Level1Schooldays::date

Student::Level2StartDate < Level2Schooldays::date AND
Student::cToday > Level2Schooldays::date

To compute the number of days you can use one of two options: Calculation fields in Student, can use Count ( Level1Schooldays::date ) and Count ( Level2Schooldays::date ) to compute the days in each level or you can define a summary field in School days to compute the "count of" date. If you place this summary field, taken from level1Schooldays on your student layout, it will report the number of school days for level 1. Add it from Level2SchooldDays, and this same summary field will compute the total number of days in level 2.

• ###### 2. Re: Calculation based on 2 date fields

Here is a better explanation of what I am trying to do........ How do I get this to work?

Table1 "Schooldates" (these are the available dates that should be used to count level days)

Fields

DateID - unique dateid

Date

--------------------------------------------------------------------------------------------------------------------------------------------

Table2 "Students" (needs to use days from "Schooldates" table to get amounts)

Fields

StudentID         - unique studentid

Student            - student name

Level1startdate  - level1start date

level1daycount   - count of days or available days from the schooldates table that fall between the range of the level1startdate and current available             schooldate

level2startdate   - level2start date

level2daycount   - count of days or available days from the schooldates table that fall between the range of the level2startdate and current available             schooldate

totaldays           - level1daycount+level2daycount

--------------------------------------------------------------------------------------------------------------------------------------------

example

school dates......8/1/11, 8/2/11, 8/3/11, 8/4/11, 8/5/11 for available dates

student was on level1 8/1/11, 8/2/11, 8/3/11

level1daycount should equal 3

student was on level2 8/4/11, 8/5/11

level2daycount should equal 2

totaldays should equal 5

• ###### 3. Re: Calculation based on 2 date fields

That's exactly what I understood you to have. What I suggested should work with that.

If "table occurrence" is a new concept, you might want to read this thread:  Tutorial: What are Table Occurrences?