# Paid-To Date

## Recommended Posts

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.

##### Share on other sites

Maybe calculate in hours in stead of days?

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

Edited by Guest

##### Share on other sites

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?

##### Share on other sites

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 . . .

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

1 member has viewed this topic:
vwgtiturbo
×
×
• Create New...