Jump to content
Sign in to follow this  
PASFKS

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites

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

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.