Hi, I have a requirement for calculating interest from averdue accounts such that

if (Date) <30, 0

if (Date) >30 and <60, (ammount) *x

if (Date) >60 and <90, (ammount) *x

etc

Can somebody give me an elegant solution please

Case(Date <30,0,Date >= 30 and date <=60,(ammount) *x,Date >=60 and Date <= 90,(ammount) *x)

Stu

It appears that if the number is greater than 30, then the result is always amount * x, regardless of in which 30 number span it falls. If this is the case, then Case( Date < 30, 0, amount * x ) will work for you. If there is indeed a typo in your post and it's not reading how it should, then you can use Choose( Int( Date / 30 ), 0, amount * x, result if 2, result if 3, etc. ), which is, as you requested, quite elegant.

Try this, but subsitute your fields for mine. Today's Date = Status(CurrentDate) or a date field of your choosing.

Case(

(Todays Date - Invoice Date) <= 30, 0,

(Todays Date - (Invoice Date > 30)) and (Todays Date- Invoice Date <= 60), 15.00,

(Todays Date - (Invoice Date > 60)) and (Todays Date-Invoice Date <= 90), 30.00,

(Todays Date - (Invoice Date > 90)) and (Todays Date- Invoice Date <= 120), 45.00,

(Todays Date - Invoice Date > 120), 60.00

)

HTH

Lee

Hi Oyseka,

I forgot the second part of the calculation, so this should do it for you.

Case(

(Todays Date - Invoice Date) <= 30, 0,

(Todays Date - (Invoice Date > 30)) and (Todays Date- Invoice Date <= 60), Amount*.010,

(Todays Date - (Invoice Date > 60)) and (Todays Date-Invoice Date <= 90), Amount*.020,

(Todays Date - (Invoice Date > 90)) and (Todays Date- Invoice Date <= 120), Amount*.030,

(Todays Date - Invoice Date > 120), Amount*.045

)

You've some very confusing paren additions in there, Lee.

And you could still streamline this considerably, in the vein of the Choose() statement I posted earlier. Something like Choose( Int( ( Todays Date - Invoice Date ) / 30 ), 0, Amount * 0.01, Amount * 0.02, Amount * 0.03, Amount * 0.045 )

Or you could turn it around and just say:

Case ( Date >= 30, amount * x )

(I read it the way -Q- does, it appears you're ALWAYS wanting the amount * x, unless the date difference is less than 30. If that's not the case, please tell us what you do want. Also, I suspect that Lee is right about your "Date" field, i.e. in the calc above and in -Q-'s calcs, we're assuming "Date" is a number; but if it's actually a date, you'll need to calculate that number the way Lee did, by subtracting one date from another. )

Hey Queue,

Oops, here it is without the extra parens:

Case(

Todays Date - Invoice Date <= 30, 0,

Todays Date - Invoice Date > 30 and Todays Date- Invoice Date <= 60, Amount*.010,

Todays Date - Invoice Date > 60 and Todays Date-Invoice Date <= 90, Amount*.020,

Todays Date - Invoice Date > 90 and Todays Date- Invoice Date <= 120, Amount*.030,

Todays Date - Invoice Date > 120, Amount*.045

)

However, I like your choose much better.

Lee

Hey, thanks. And congrats on the near 1300. I'll be there within a year.

FWIW, here is Lee's calc simplified:

Case(

Todays Date - Invoice Date <= 30, 0,

Todays Date - Invoice Date <= 60, Amount * .010,

Todays Date - Invoice Date <= 90, Amount * .020,

Todays Date - Invoice Date <= 120, Amount * .030,

Amount * .045 )

...or...

Amount * Case(

Todays Date - Invoice Date <= 30, 0,

Todays Date - Invoice Date <= 60, .01,

Todays Date - Invoice Date <= 90, .02,

Todays Date - Invoice Date <= 120, .03,

.045 )

Sorry, you are right of course, it should have been *x, *y, *z. but the "Case" argument looks totally elegant, thank you