Calculating contract dates and amounts over time.

[Nic Brown]

I have a simple database that I enter in the following information:

Contract Start Date

Contract End Date

Frequency In Days

Contract Obligation (Dollar Amount)

What I would like, is for the following to happen:

Evaluate the start date and end dating and divide the contract obligation based on the "Frequency in days"

So if I were to put in 8/1/09 as my start date and 6/30/10 as my end date and "30" as my frequency with the obligation amount being $50,000.00 for FileMaker to calculate there would be 10 billable cycles based on those contract days and then to take the $50,000/10 to come up with $5,000 every 30 days. I'm having issues thinking through this to know the best way to start.

Any direction is helpful.

[mr_vodka]

If its every 30 days, shouldn't that be 12 billing cycles?

08/31/2009

09/30/2009

10/30/2009

11/29/2009

12/29/2009

01/28/2010

02/27/2010

03/29/2010

04/28/2010

05/28/2010

06/27/2010

07/27/2010

If so the calc could be:

Let ( days = end - start; Contract Obligation / Ceiling ( days / frequency ) )

add in checks for empty and invalid criteria.

Edited July 20, 2009 by Guest
added calc

[Nic Brown]

Well, that is why I have the start contract date and the end contract date. Because not all of our contracts are 12 month. It might only be an 8 month contract. Plus, the days will be variable. Some contracts might be 30 days others might be 60 or even 90.

Thanks,

Nic

[mr_vodka]

No it has nothing to do with whether I think it should be a 12 month contract or not.

I mean that if you are using a 30 days cycle, and with the start / end dates you provided, there would be 12 dates ( which I posted ).

08/01/09 + 30 = 08/31/09

08/31/09 + 30 = 09/30/09

09/30/09 + 30 = 10/30/09

and so on.

The 10th billing cycle is 05/28/2010 in this example, which is before 06/30/10, as is 06/27/10. Then the last 3 days should be on 07/27/2010