Jump to content
Sign in to follow this  
MacFileman

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 this post


Link to post
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 this post


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

Share this post


Link to post
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 this post


Link to post
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 this post


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

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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