Complicated Calculation - HELP

[aigduke]

OK, so here's the mandate from the client for invoices for inpatient and outpatient accounts

for outpatients if the admit date is between 7/1/2010 and 6/30/2011, (amount recovered-.07*amount recovered)*contingency percent.

Otherwise it's just straight up Amount Recovered*percentage for outpatients.

For inpatients, it's a bit more complicated...

For admit dates between 7/1/2010 and 6/30/2011, it's (Amount recovered- .52*amount recovered)*contingenty percent.

BUT, if the admit date is before 7/1/2011 and discharge date is after 6/30/2011 it's (Amount recovered- .26*amount recovered)*contingenty percent.

Otherwise it's Amount Recovered*contingency percent for inpatients.

I think I have most of it figured out, except for the inpatient part that considers the discharge date.

Here's my formula that mostly works:

( Acct Type ≠ "Outpatient" and Discharge Date < 6/30/2011 and Admit Date < 7/1/2011; (Amount Recovered-.26*Amount Recovered)*Percentage;

Acct Type ≠ "Outpatient" and Admit Date>7/1/2010 and Admit Date<6/30/2011 ; (Amount Recovered-.52*Amount Recovered)*Percentage;

Acct Type = "Outpatient" and Admit Date>7/1/2010 and Admit Date<6/30/2011; (Amount Recovered - Amount Recovered*.07)*Percentage;

Amount Recovered* Percentage)

What am I doing wrong?!? I am ready to tear my hair out!

Thanks so much for your attention.

Best,

Adrienne

[comment]

I haven't looked at your formula, because there is something fundamentally wrong with your approach of tailoring a calculation to match specific dates. What is so special about these dates? It looks like you intend to modify this calculation again every time some circumstances change - and that's not good practice.

[LaRetta]

Hi Adrienne,

As mentioned is it never a good idea to hard-code data into a calculation. 7/1/2010 and 6/30/2011 appear to be fiscal periods. We could adjust the calc for different fiscals (based upon a date in a different field) but we would still need (and I suggest anyway) a separate table for percentages per fiscal/Acct Type so we are not hard-coding percentages for those portions as well.

I wanted to explain why your calculation failed, and it is good that it did or you might have continued down a faulty path here ... you cannot type > 7/1/2010. FM sees that as 7 divided by 1 divided by 2010 or .0034825870646766.

Instead, to include a date in a calculation you need to use one of the date functions such as Date ( 7 ; 1 ; 2010 ). But please follow Comment's direction from here; I just wanted to expand your thinking to include another table, consider fiscal ranges and explain how dates can be entered into calculations.

[aigduke]

Thanks for the advice. I got the formula to work, I just needed to change the order an put the section that included the discharge date at the top.

Best,

Adrienne

Hi Adrienne,

As mentioned is it never a good idea to hard-code data into a calculation. 7/1/2010 and 6/30/2011 appear to be fiscal periods. We could adjust the calc for different fiscals (based upon a date in a different field) but we would still need (and I suggest anyway) a separate table for percentages per fiscal/Acct Type so we are not hard-coding percentages for those portions as well.

I wanted to explain why your calculation failed, and it is good that it did or you might have continued down a faulty path here ... you cannot type > 7/1/2010. FM sees that as 7 divided by 1 divided by 2010 or .0034825870646766.

Instead, to include a date in a calculation you need to use one of the date functions such as Date ( 7 ; 1 ; 2010 ). But please follow Comment's direction from here; I just wanted to expand your thinking to include another table, consider fiscal ranges and explain how dates can be entered into calculations.