# DATE Calculation

## Recommended Posts

Hey...

I need help with a date function. The NJ Tax rate has changed and I never planned for it 12 years ago, simply because it never changed. My easiest solution is to work off the invoice date. Prior to 2017, tax rate is .07. In 2017, it will be .06875 and we know that 2018 will be .0625. In hind site, over 10 years ago, I would have field set the tax rate into each invoice, but working with what I have, the best solution is to work off the "Invoice Date."

I can sort of see the code....

Case (

Invoice Date > 1/1/17, .07, Invoice Date > 1/1/18, .06875, Invoice Date > 1/1/198, .0625)

Something like that. All help appreciated and this is needed today.

Thanks!

Mike

##### Share on other sites

This works temporarily...

Case ( INVOICE DATE > Date(12;31;2016); .06875; .07)

But not sure how to sequence in 2018 at .0625, but I do not need this for a year anyway.

##### Share on other sites
10 minutes ago, MacFileman said:

the best solution is to work off the "Invoice Date."

No, the best solution is to have a Rate field in the Invoices table and have it auto-enter the current rate. Fixing the existing invoices shouldn't take more than a minute.

13 minutes ago, MacFileman said:

I can sort of see the code....

Case (

Invoice Date > 1/1/17, .07, Invoice Date > 1/1/18, .06875, Invoice Date > 1/1/198, .0625)

Filemaker reads 1/1/17 as a division. If you want it to read a date, use Date ( 1 ; 1 ; 2017 ). Since you only deal with years, you could do simply:

```Case (
Year ( Invoice Date ) < 2017 ; .07 ;
Year ( Invoice Date ) < 2018 ; .06875 ;
.0625
) ```

##### Share on other sites

AMAZING!!!! THANK YOU!!

I figured out the "division" issue you mentioned.... but your solution is the simplest!! THANKS A MILLION!

Yes, it is a one minute fix.

##### Share on other sites

I am afraid you may have misunderstood me. I meant it shouldn't take more than a minute to populate old invoices with the value of 0.7. Then you should add a table of TaxRates and make the rate field in Invoice lookup the applicable rate from there using a relationship, instead of hardcoding the rates in a calculation formula.

##### Share on other sites

There may have been a few misunderstandings, not to worry, everything worked perfectly with the calculation you helped me with. My terminology may not be great. Unfortunately, I do not code Filemaker every day, so I come here for quick codes and calculations. There are times I work in it for 3-4 months straight, then not at all for 2 years, so I forget everything I knew. : /

Since my invoices had an invoice date and the tax rate changes for the year, your formula was perfection and exactly what the doctor ordered! Thanks again.

I should be good through 2018! LOL

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