I am trying to calculate a paid-to date but have struck a problem when a person pays by instalments eg:

Month - April

Start Date - 1/4/2007 (the first day)

End Date - 30/4/2007

Payment Due - $1000 at a day rate of $33.333333 (ie $1000/30days)

1st Instalment paid is $750 which gives the number of days at 22.5 (ie $750/33.333333)

So the paid to date becomes 22/4/2007

2nd Instalment paid is $250 which gives the number of days 7.5 and a paid-to date of 29/4/2007 NOT 30/4/2007.

The problem is the half day which is rounded down by FileMaker. If I round up the paid-to date becomes 1/5/2007 which is still not 30/4/2007.

Any ideas on how to handle this rounding problem would be greatly appreciated.

Maybe calculate in hours in stead of days?

add it to a timestamp, not to a date...

Edited July 17, 200718 yr by Guest

No experience of this at all.. but couldn't you store the numbers of days and total them each time, rather than doing it cumulatively?

So the 2nd instalment would be 22.5 + 7.5 =30 days?

Thanks Balder and Emma for your comments.

Have been playing around with hours and time stamps but not really had any success. At the end of the day I have to convert the hours to a date and still get the same rounding problem.

I will keep working with hours and see where I get to . . .

When you round twice in the same direction, the rounding errors accumulate. You should do something along the lines suggested by Emma, and sum the amounts paid before trying to figure out how many days they cover.

Thanks Comment. I did have a go at this early on and it works well in isolation (ie one month) but I found with multiple months and multiple instalments of many different values it created it's own complications (adjusting for different vslues, month lengths, leap years etc).

I might give it another go, regards

We only know what you chose to told us. The problem you described is a problem of accumulated rounding error:

Round ( .5 ; 0 ) + Round ( .5 ; 0 ) = 2

Sorry about that, just trying to keep it simple - obviously made it to simplistic.

The calculation I am trying to devise is one which returns the correct Paid-To date for any amount paid irrespective of whether that instalment spans multiple days, months, years etc.

If you sum all the installments made and divide this sum by daily rate, you will get the number of days fully paid - and possibly a fraction. If you then add the result to the starting date, you will get the last fully paid date - regardless of month lengths and leap years.

I am not sure what you want to do with the fraction. Filemaker dates are integers only, so any fraction is simply truncated. If you want to display the result as a date, you need to decide whether you want to show the last fully-paid date, or the last partially-paid date, or the last date more than half paid, or what.