Jump to content

DATE Calculation


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

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

 

 

 

 

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

 

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

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

Link to comment
Share on other sites

This topic is 2641 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.