Jump to content

Paid-To Date


This topic is 6099 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

  • Newbies

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.

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

Link to comment
Share on other sites

This topic is 6099 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

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