Update month with calculation

[Alex C]

I created a simple database to track my customers and have all functions working as required--except one. What I have is a payment field and a and a due date field. What I want is when I enter the payment amount the month in the date field is incremented by a number in the calc field. The formula I used was Payment/monthly amount + Month(due date) but with formula I get some strange results in the Payment due date field. Any suggestions.

[Tim W]

Hi Alex,

Try this as the calculation with a ( date result )

Date ((Payment/monthly amount + Month(due date));day(due date);year(due date))

I suspect the problem is with type missmatching. I assume your result field is a date type, however Month(date) returns a number type. This would probably result in a date somewhere in the year 1900. Filemaker does math with dates as a number representing the number of days since 1/1/1900. Working with date fields can get tricky until this logic becomes clear to you.

Hang in there.

Tim

[Alex C]

This was first attempt at a filemaker database, it was a lot more daunting than I thought. But thanks to this forum and a lot of reading the help files and special thanks to Tim, all was accomplished. If thanks were gold you would be a rich man Tim--Thanks again.

[LaRetta]

Filemaker does math with dates as a number representing the number of days since 1/1/1900

A small (but important) point ... Filemaker's first date is Jan 1, 0001 and days are numbered from there.

Also, drop the slash from between Payment/Monthly Amount. Any calculations based upon this field will FAIL (it is invalid) and FM should have told you so when you created it. Also, if you have no due date, you will receive ? in the field. Instead, I would write the calculation as:

If ( due date ; Date ( Month ( due date ) + PaymentMonthlyAmount ; Day ( due date ); Year ( due date ) ) )

[comment]

Ahmmm... the way I understand it, it is actually a division. You divide the total charge by the monthly installment, and that should give you the number of installments. Strictly speaking, it should be:

Date ( Ceiling ( Sum / Payment ) + Month ( StartDate ) ; Day ( StartDate ) ; Year ( StartDate ) )

in case the division leaves a remainder.