Pro rate a dollar amount

[Former Member]

Good Day To All,

I'm a bit confused on how to handle a prorated amount.

I have a reoccurring charge.

The charge is let's say 170.00 per month for a period.

But the charge starts at 05/14/2013 through 07/31/2014.

First I  must find how many days are in the first month and pro rate it by divide number the charge (170.00) by the remaining number of days left in the month, then I must find out how many remaining months and multiply this by 170.00.

First can this be done in Crystal?

Second is the a function that will take care of it?

Also if the first month starts at the begining of the month I will not need to pro rate it. It's just a simple multiply the number of months by 170.00

Thanks

David

[Former Member]

Good Day Brian,

I want to thank you for this impressive formula. One no make it two questions. I come out a day and a month short when the start date is not equal to 1. Where do I have add this code.

The reason is that we have to count the current month and the current date.

If the contract runs from May 14, 2013 to July 31, 2014 I have to compasate for the current date and the last whole month.

Regards,

David

[former_member292966]

Hi David,

Try this formula.  I hard coded the dates and charge into the first 3 variables.  The first part of the If calculates the last day of the Start date and then get the number of days to use into FirstMonth. 

FirstMonthProRate has the prorated value for the month. 

MonthsBetween is the number of months after to the end date. 

NumberVar Charge := 170;
DateVar StartDate := Date (2013, 05, 14);
DateVar EndDate := Date (2014, 07, 31);
NumberVar FirstMonth := 0;
DateVar LastDayFirstMonth;
NumberVar FirstMonthProRate := 0;
NumberVar MonthsBetween := 0;

If Day (StartDate) > 1 Then
    (
    LastDayFirstMonth := CDate (DateAdd ("m", 1, Date (Year(StartDate), Month(StartDate), 1)) - 1);
    FirstMonth := DateDiff ("d", StartDate, LastDayFirstMonth);
    FirstMonthProRate := Charge / Day(LastDayFirstMonth);
    MonthsBetween := DateDiff ("m", LastDayFirstMonth + 1, EndDate);
    )
Else MonthsBetween := DateDiff ("m", StartDate, EndDate);

(FirstMonthProRate * FirstMonth) + (Charge * MonthsBetween);

The answer I got was $2303.23.

I was going to expand this in case the end date didn't end on the end of the month so you would have to prorate that as well but it would be similar to how the first month is calculated. 

Hope this helps,

Brian

[Former Member]

Good Day Brian,

Let me say "THANKS FOR YOUR HELP" this forrmula is great. I was just told that

not only do we have mid month start dates but we also have mid month end dates.

My charge is 209.00 per month

the start date is 06/24/2013

the end date is  09/23/2014

my total charge for this is 3,135.00

for the 06/24/2013 until 06/30/2013 the charge should be 48.77

for the 09/01/2013 until 09/23/2014 the charge should be 160.23

the months in between should be 14 full months time 209.00 which is 2,926.00

giving the grand total of 3,135.00

with you formula I get 2967.80 the difference of 167.20.

Thanks again, and if this can't be done by crystal let me know so I can let my boss know.

Regards,

David M.

[former_member292966]

Hi David,

I've made the changes that you need.  For the StartDate, I added the extra day.  The EndDate does a similar calculation to prorate. 

The section for MonthsBetween does an initial count of all the months then subtracts 1 if the EndDate is not a full month.  So the StartDate will always be counted. 

NumberVar Charge := 209;
DateVar StartDate := Date (2013, 06, 24);
DateVar EndDate := Date (2014, 09, 23);
NumberVar FirstMonth := 0;
DateVar LastDayFirstMonth;
NumberVar FirstMonthProRate := 0;
NumberVar MonthsBetween := 0;
NumberVar LastDayLastMonth := 0;
NumberVar LastMonth := 0;
NumberVar LastMonthProRate := 0;

If Day (StartDate) > 1 Then
    (
    LastDayFirstMonth := CDate (DateAdd ("m", 1, Date (Year(StartDate), Month(StartDate), 1)) - 1);
    FirstMonth := DateDiff ("d", StartDate - 1, LastDayFirstMonth);
    FirstMonthProRate := Charge / Day(LastDayFirstMonth);
    );

If EndDate < CDate (DateAdd ("m", 1, Date (Year(EndDate), Month(EndDate), 1)) - 1) Then
    (
    LastDayLastMonth := Day (CDate (DateAdd ("m", 1, Date (Year(EndDate), Month(EndDate), 1)) - 1));
    LastMonth := Day (EndDate);
    LastMonthProRate := Charge / LastDayLastMonth;
    );

MonthsBetween := DateDiff ("m", StartDate, EndDate);
If Day (EndDate) < LastDayLastMonth Then
    MonthsBetween := MonthsBetween - 1;

(FirstMonthProRate * FirstMonth) + (Charge * MonthsBetween) + (LastMonthProRate * LastMonth);

Brian

[Former Member]

Thanks Brian works perfectly!!!!

[Former Member]

Good Day Brian,

Jumped the gun. The formula works for mid month start date with the mid month end date. But now  when my start date is 01 and my end date is the end of month I missing one month.

I took a stab at it and added1 month to the formula, but it throws off the total. Just wondering if you can help me out.

MonthsBetween := DateDiff ("m", StartDate, EndDate) + 1;

If Day (EndDate) < LastDayLastMonth Then

    MonthsBetween := MonthsBetween - 1;

[former_member292966]

Hi David,

I moved the calculation for LastDayLastMonth outside the If so it is available regardless.  I also added another condition to calculate MonthsBetween. 

NumberVar Charge := 209;
DateVar StartDate := Date (2013, 06, 01);
DateVar EndDate := Date (2014, 09, 23);
NumberVar FirstMonth := 0;
DateVar LastDayFirstMonth;
NumberVar FirstMonthProRate := 0;
NumberVar MonthsBetween := 0;
NumberVar LastDayLastMonth := 0;
NumberVar LastMonth := 0;
NumberVar LastMonthProRate := 0;

If Day (StartDate) > 1 Then
    (
    LastDayFirstMonth := CDate (DateAdd ("m", 1, Date (Year(StartDate), Month(StartDate), 1)) - 1);
    FirstMonth := DateDiff ("d", StartDate - 1, LastDayFirstMonth);
    FirstMonthProRate := Charge / Day(LastDayFirstMonth);
    );

LastDayLastMonth := Day (CDate (DateAdd ("m", 1, Date (Year(EndDate), Month(EndDate), 1)) - 1));
    
If Day (EndDate) < LastDayLastMonth Then
    (
    LastMonth := Day (EndDate);
    LastMonthProRate := Charge / LastDayLastMonth;
    );

MonthsBetween := DateDiff ("m", StartDate, EndDate);

If Day (EndDate) < LastDayLastMonth Then
    MonthsBetween := MonthsBetween - 1
Else (If Day (StartDate) = 1 And Day (EndDate) = LastDayLastMonth Then
        MonthsBetween := MonthsBetween + 1);

(FirstMonthProRate * FirstMonth) + (Charge * MonthsBetween) + (LastMonthProRate * LastMonth);

[Former Member]

Thanks Brian!!!

On a side note I'm passing a variable from a sub-report to the main report and it's working.

But I useing the vaule to trigger a supression of an object. Is this possible.

This is what I have in a formaula called Passed_Variables

WhilePrintingrecords;

shared NumberVar Amount;

Amount := {Command.camount} * {Command.iduration};

This is what I have in a formula in the main report Called Variable_SubReport

Whileprintingrecords;

Shared NumberVar Amount;

This part is working I have the sub report above the the passed variable

In the supression I have the formula

if {@Variable_SubReport} < 1 then true

else False

Any Thoughts?

Thanks in Advance.

[former_member292966]

Hi David,

This will work but instead of using the Formula in the suppressing formula, use the variable like: 

Shared NumberVar Amount;

If Amount < 1 then True else False;

As long as the section you want to suppress is below the section the subreport is in.  Crystal can only evaluate formulas left-rigth top-down.  It can't evaluate a formula then jump back up to a previous section to format it. 

Good luck,

Brian

[Former Member]

Thanks Brian it worked. I was using the formula name instead of using the variable.