6 Replies Latest reply on Nov 21, 2012 9:33 AM by bnuttman

# Calculate dates that are past due based on a calculated field

Hello,

I have a calculated field that calculates when reports for our students are due:

Basically, they are due every three months based on the entry date of the student. On the due date, the calculation updates to calculate the next due date. It is a moving target...

For example, Entry date is 6/1/12, then the report due date calculates to 9/1/12 and on 9/1/12, the field calculated to 12/1/12 and so on.

What I want to have happen is:

Reports not turned in by the due date need to be flagged and listed on a report of past due reports and the due date displayed. Currently that due date is updated to the next due date when that current due date is reached as explained above, it is not retained so that I can know if a report is past due or not.

Any ideas on how I can do this?

Here is my calculation for the due dates (thanks to another post in this forum for this calculation)

Let ( [

today = Get ( CurrentDate ) ;

elapsedMonths = 12 * ( Year ( today ) - Year ( entrydate_last) ) + Month ( today ) - Month ( entrydate_last) - ( Day ( today ) < Day ( entrydate_last) ) ;

elapsedIntervals = Div ( elapsedMonths ; 3 )

] ;

Date ( Month ( entrydate_last) + 3 * ( elapsedIntervals + 1 ) ; Day ( entrydate_last) ; Year ( entrydate_last) )

)

• ###### 1. Re: Calculate dates that are past due based on a calculated field

Is the next due date even relevant when the current report is overdue? Perhaps you should calculate the next due date relative to the last submitted report - not relative to current date.

• ###### 2. Re: Calculate dates that are past due based on a calculated field

The next due date is relevent since it will still be due 3 months after the previous due date even though the report may be late.

• ###### 3. Re: Calculate dates that are past due based on a calculated field

bnuttman wrote:

The next due date is relevent since it will still be due 3 months after the previous due date even though the report may be late.

I meant do we need to know that the next upcoming date is in December, when the September report is still not here?

• ###### 4. Re: Calculate dates that are past due based on a calculated field

No, we do not need to know the upcoming date is December when the September report is not here.

• ###### 5. Re: Calculate dates that are past due based on a calculated field

Ok, then if you replace the current date with the date of the latest report, the same calculation will return the next due date - whether future or past.

• ###### 6. Re: Calculate dates that are past due based on a calculated field

Thanks Michael, that put me on the right path and with some tweaking, I was able to make it work.