Below is a formula using the case function for determining which range the date I specify falls into and it should return 1, 2, 3, or default 0.

Currently, it works fine if the date specified is within 2007, i.e., a 1 appears. If I specify a 2008 or 2009 date, it returns the default 0. What am I doing wrong?

BTW, 2009 is intentionally left open ended.

Thanks for the help.

Leonard

Case (Search_date ≥ Date( 1 ; 1 ; 2007 ) ; Search_date ≤ Date( 12 ; 31 ; 2007 ) ; "01" ;

Search_date ≥ Date( 1 ; 1 ; 2008 ) ; Search_date ≤ Date( 12 ; 31 ; 2008 ) ; "02" ;

Search_date ≥ Date( 1 ; 1 ; 2009 ) ; "03" ; 0)

I believe it should be:

Case (

Search_date ≥ Date( 1 ; 1 ; 2007 ) [color:red]and Search_date ≤ Date( 12 ; 31 ; 2007 ) ; "01" ;

Search_date ≥ Date( 1 ; 1 ; 2008 ) [color:red]and Search_date ≤ Date( 12 ; 31 ; 2008 ) ; "02" ;

Search_date ≥ Date( 1 ; 1 ; 2009 ) ; "03" ;

0 )

Or, perhaps more simply:

Let (

y = Year ( Search_date ) ;

;

Case (

y = 2007 ; "01" ;

y = 2008 ; "02" ;

y ≥ 2009 ; "03" ;

0 )

)

Case ( Search_date ≥ Date( 1 ; 1 ; 2009 ); 3;

       Search_date ≥ Date( 1 ; 1 ; 2008 ); 2; 

       Search_date ≥ Date( 1 ; 1 ; 2007 ); 1;

       0

     )  

But I dont see how this will help as you have to change the calc every year? What is the purpose here of this calc?



A calc that make more sense could be:

Let ( [ y = Year ( Search_date ); 

       cy = Year ( Get ( CurrentDate ) )

      ]; 

         Case( y = cy; 3;

               y = cy - 1; 2;

               y = cy - 2; 1;

               0

             )

     )

Edit: Oops didnt see your post Michael.

Edited August 19, 200916 yr by Guest

Thanks for the rapid responses comment and mr_vodka.

Comment, your first suggestion worked like a charm. I will try your second one and mr_vodka's, too.

Mr_vodka asked, "What is the purpose here of this calc?" An example is when sales tax changes over time. My Search_date would be the date on a proposal which is step one of the money tracking process. The 1, 2, 3 would be the tax rates over time used to calculate costs (purchases, fees, etc.).

Edit: I was just staring at the optional solutions and one reason they might not work and why I chose my approach is that normally, tax changes take effect on the first day of the new fiscal year (July 01 here). This year, a change took effect on Aug. 01. So, proposals could have different tax rates within the same calendar year. –L.

Edit 2: The date range would then span one or more years and not necessarily begin or end on the same month and day. –L.

Thanks.

Leonard

Edited August 19, 200916 yr by Guest

John was right to question your purpose: this is not a good approach. You should use a lookup, not a calculation.

Why? How is a lookup more advantageous?

Why? How is a lookup more advantageous?

For one thing, a lookup is based on data rather than tweaking calculation definitions. So once set up, it is good for current and future time periods.

I don't see why you would want to calculate 2007 taxes in 2009. From a business perspective, you will run afoul of recordkeeping requirements if you don't actually store the amount you billed someone back in 2007. Your accountant (and the IRS, if you're in the U.S.) will bark. Far better just to put in the amount. It can be supplied by a simple calculation.

If you need to track multiple tax rates or multiple time frames, I think Michael's suggestion of a lookup is best.

David

P.S. -- Welcome to the Forums.

Edited August 19, 200916 yr by Guest

Hmmm. I'm not 100% sure but I better take look at our set up.

Thanks for that, BruceR.

L.

Hi T-Square. Thanks for the welcome.

My first post was just a two-minute creation to figure out the equation format and only it is real. But, you are very right about taxes. Egads, I have enough people barking at me.

It is unanimous that lookup is the way to go.

Thanks.

L.