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.

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, 200916 yr by Guest
added calc

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

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