DATE Calculation

[MacFileman]

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

 

 

 

 

[MacFileman]

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.

[comment]

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
) 

 

[MacFileman]

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.

[comment]

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.

[MacFileman]

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