How to calculate a partial rate from a date range?

[ML2008]

Every month I need to calculate the Internet charges for my guests, the monthly rate sometimes differs from guest to guest.

I currently have the following fields in my db:

IntBeginMonthly = The Internet contract beginning date

IntEndMonthly = The Internet contract ending date

IntCharge = The monthly Internet charge

*(monthly = from 20th - 19th next month)

IntChargeProrated = THIS IS THE FIELD I NEED TO SET-UP

I want to define a field that will calculate the Internet charges for the month based on the contract dates, if the Internet contract end date is after the 19th of the month and the start date is before the 20th of the previous month then there will be a full month's charge if not how can I calculate the charges based on the start and end dates?

[-Queue-]

Do you mean something like

Case(

Day(IntEndMonthly) > 19 and Day(IntBeginMonthly) < 20 and Date( Month(IntBeginMonthly) + 1; 1; Year(IntBeginMonthly) ) = Date( Month(IntEndMonthly); 1; Year(IntEndMonthly) ); IntCharge;

(IntEndMonthly - IntBeginMonthly) * IntCharge / Day(Date( Month(IntBeginMonthly) + 1; 0; Year(IntBeginMonthly) ))

)

?

[ML2008]

The field now calculates the charges based on the total lengh of the contract. I need it to calculte a full month charge (if the contract stars and ends outside the billing period) to be the maximum monthly charge and if not how can I calculate the charges based on the start and end dates (if the contract starts or ends within the billing period)?

[comment]

I am not sure exactly how you generate the monthly charge (a script?) and where do you keep it, but I guess this the general idea:

 

Let ( [

periodStart = Date ( Month ( Get ( CurrentDate ) ) - 1 ; 20 ; Year ( Get ( CurrentDate ) ) ) ;

periodEnd = Date ( Month ( Get ( CurrentDate ) ) ; 19 ; Year ( Get ( CurrentDate ) ) ) ;

daysInPeriod = periodEnd - periodStart + 1 ;



fullCharge = IntCharge



] ;





Case (

IntBeginMonthly > periodStart ;

fullCharge * ( periodEnd - IntBeginMonthly + 1 ) / daysInPeriod ;



IntEndMonthly < periodEnd ;

fullCharge * ( IntEndMonthly - periodStart + 1 ) / daysInPeriod ;



fullCharge

)

) 

[ML2008]

Thank you for the assistance. Seems like we are almost there...

Now when the starting date (IntBeginMonthly) is set to a future month (outside of the periodEnd), the charge shows a negative number for the current billing period.

Also when the ending date (IntEndMonthly) is set to a past month (outside of the periodStart), the charge shows a negative number for the current billing period.

[comment]

I was afraid you'd say that - that's one aspect of "not sure exactly how you generate the monthly charge".

Is it safe to assume that no one has a contract that starts AND ends within the same billing period?

If so, then:

 ...

Case (

IntBeginMonthly > periodEnd or IntEndMonthly < periodStart ;

0 ;



IntBeginMonthly > periodStart ;

fullCharge * ( periodEnd - IntBeginMonthly + 1 ) / daysInPeriod ;



IntEndMonthly < periodEnd ;

fullCharge * ( IntEndMonthly - periodStart + 1 ) / daysInPeriod ;



fullCharge

)

) 

[ML2008]

Thanks a lot... seems like you are a Filemaker expert!

I would like to know how to contact you, maybe I will have some small Filemaker jobs for you.

[comment]

I am only an egg.

I've sent you a private message with my e-mail.