I'm trying to create a calculation that prorates rent based on next month.  So if their rental rate is $1,000 per month, and they haven't submitted a move out date, they owe $1,000.  If they submitted it for 2/15/14, I need to be able to calculate the prorate.  15 days of rent (2/1 - 2/15), divided by 28 days in the month of February, so they owe $535.71.  

 

Any help would be appreciated...  Thanks!

You can get how many days in any month by:  

 

Day ( Date ( Month ( MoveOutDate ) + 1 ; 0 ; Year ( MoveOutDate ) ) )

You can get how many days in any month by:  

 

Day ( Date ( Month ( MoveOutDate ) + 1 ; 0 ; Year ( MoveOutDate ) ) )

hmm...  I get a question mark...  

Hi JT,

 

Let ( [

 

stay = MoveOut - Date ( Month ( MoveOut )  ; 1 ; Year ( MoveOut ) ) +1 ;

theLast =  Day ( Date ( Month ( MoveOut ) + 1 ; 0 ; Year ( MoveOut ) ) )

 

];

 

Round ( stay *   ( Rental / theLast )  ; 2 ) 

 

 )

 

 

Rental is your normal Rental rate.

Edited January 17, 201412 yr by Ocean West
rounded the result.

If my calc did not work for you then check your parentheses again.    But it looks like Stephen gave you the whole thing anyway.  I thought you had it all but how to get the last day or I would have continued. :-)

You will get a question mark if MoveOutDate is not a Date field or if it doesn't contain a valid date.

Oh of course ... otherwise he wouldn't have been able to get out of the dialog at all if it were parentheses.

Weird, I still get a ?.  I started a new database and it works, but the calculation is wrong.  I put the MoveOutDate as 6/15/2014, and the rental rate at $2,000, but it calculates next month's rent as $1,000.  I think I need a CurrentDate in there somewhere...  

 

Here is my actual calc though based on Stephen's help:

 

Let ( [

 

stay = Jobs::Move Out Date - Date ( Month ( Jobs::Move Out Date )  ; 1 ; Year ( Jobs::Move Out Date ) ) +1 ;

theLast =  Day ( Date ( Month ( Jobs::Move Out Date ) + 1 ; 0 ; Year ( Jobs::Move Out Date ) ) )

 

];

 

Round ( stay *   ( Property Leases::Current Rate / theLast )  ; 2 ) 

 

 )

 

The calculation result is a number, the move out is a 4 digit date, and the current rental rate is a number...

OK, so originally, years ago, Comment you gave me this: 

Let ( [today = Get ( CurrentDate ) ;m = Month ( today ) ;y = Year ( today ) ;start = Max ( Property Leases::Lease Start Date ; Date ( m + 1 ; 1 ; y ) ) ;end = Min ( Jobs::Property NTV Move Out Date ; Date ( m + 2 ; 0 ; y ) )] ; Property Leases::Current Rate * Max ( end - start + 1 ; 0 )) / 30

 

Works great, but I want to change the "30" to "Number of days in next month".

 

When I do this:

Let ( [today = Get ( CurrentDate ) ;m = Month ( today ) ;y = Year ( today ) ;start = Max ( Property Leases::Lease Start Date ; Date ( m + 1 ; 1 ; y ) ) ;end = Min ( Jobs::Property NTV Move Out Date ; Date ( m + 2 ; 0 ; y ) )] ; Property Leases::Current Rate * Max ( end - start + 1 ; 0 )) / Day ( Date ( Month ( Jobs::Property NTV Move Out Date ) + 1 ; 0 ; Year ( Jobs::Property NTV Move Out Date ) ) )

 

I get a question mark.

I put the MoveOutDate as 6/15/2014, and the rental rate at $2,000, but it calculates next month's rent as $1,000.

 

 

Isn't that the correct result? 15 days out of 30, that's exactly half.

 

 

Note that:

MoveOutDate - Date ( Month ( MoveOutDate )  ; 1 ; Year ( MoveOutDate ) ) +1 

can be simplified to:

Day ( MoveOutDate )

so your entire calc could become =

Let ( [
daysUsed = Day ( MoveOutDate ) ;
daysInMonth = Day ( Date ( Month ( MoveOutDate ) + 1 ; 0 ; Year ( MoveOutDate ) ) ) ;
proRate = daysUsed * Rate / daysInMonth
] ;
Round ( proRate ; 2 )
)

In which table do you perform this calculation? I notice that all referenced fields are coming from related TOs.

This is random... It works on some records, but even when it works it's wrong.  But if I change everything after the / back to 28 (for days in Feb), it's accurate.  And if I remove the +1, it's accurate...

Isn't that the correct result? 15 days out of 30, that's exactly half.

 

 

Note that:

MoveOutDate - Date ( Month ( MoveOutDate )  ; 1 ; Year ( MoveOutDate ) ) +1 

can be simplified to:

Day ( MoveOutDate )

so your entire calc could become =

Let ( [
daysUsed = Day ( MoveOutDate ) ;
daysInMonth = Day ( Date ( Month ( MoveOutDate ) + 1 ; 0 ; Year ( MoveOutDate ) ) ) ;
proRate = daysUsed * Rate / daysInMonth
] ;
Round ( proRate ; 2 )
)

In which table do you perform this calculation? I notice that all referenced fields are coming from related TOs.

OK, this works, I see, but I'm looking for "next month's".  Not the month of move out...  So I run a report today, which will show me all rent's for February.  If the move out is 3/15, it will be the regular rent for February, but when I run the report in February, it will be prorated for March...

 

Sorry for the hassles/confusion.

 I think I need a CurrentDate in there somewhere...  

 

I don't see how current date comes into this. Maybe we are not talking about the same thing. You have a monthly rate, applied each month. If they submitted a move out date, the pro-rated payment applies to that month, regardless of when the calculation is performed, does it not?

OK, question mark was my fault.  The formula you gave me works great to show me the last month's prorated rent, but I want it to be the current rate if next month is not the last month.  I guess I can do some sort of If statement.  If current month +30 = month of move out, then use your formula, if not, use the regular rate.

Not the best way, but here is how I made it work:

If ( Month ( Get ( CurrentDate ) + 30 ) = Month ( Jobs::Property NTV Move Out Date ) ; 

Let ( [

daysUsed = Day ( Jobs::Property NTV Move Out Date ) ;

daysInMonth = Day ( Date ( Month ( Jobs::Property NTV Move Out Date ) + 1 ; 0 ; Year ( Jobs::Property NTV Move Out Date ) ) ) ;

proRate = daysUsed * Property Leases::Current Rate / daysInMonth

] ;

Round ( proRate ; 2 )

) 

 

; Property Leases::Current Rate )

 

 

THANK YOU EVERYONE FOR YOUR HELP

IIRC, you want an unstored calculation predicting next month's payment? If so, try something like =

Let ( [
today = Get ( CurrentDate ) ;
next1st = Date ( Month ( today ) + 1 ; 1 ; Year ( today ) )  
] ;
Case (
next1st ≠ MoveOutDate - Day ( MoveOutDate ) + 1 ; Rate ;
Let ( [
daysUsed = Day ( MoveOutDate ) ;
daysInMonth = Day ( Date ( Month ( MoveOutDate ) + 1 ; 0 ; Year ( MoveOutDate ) ) ) ;
proRate = daysUsed * Rate / daysInMonth
] ;
Round ( proRate ; 2 )
)
)
)

If ( Month ( Get ( CurrentDate ) + 30 ) = Month ( Jobs::Property NTV Move Out Date )

 

That's not only inaccurate, but also potentially wrong: if someone gives you a move out date a year from now...

I'll see if I can come up with a better way to do:

 

If Next month is the same month as their move out date, then do the formula, if not, their rent is the regular rate.

 

I know + 30 is not accurate, for example on Jan 31st, + 30 days will be in March, but it's the best I got so far.  

 

I run my report around the 10th of every month to calculate what I need to bill for.  So last week, I would look at a report, and send invoices to everyone for February.  

I'll see if I can come up with a better way to do:

 

Uhm... I thought I already did?

Try this on for size...

 

 

ProRate.fmp12.zip

ignore the last file here is a new modified file.

 

This one will accommodate if your rental policy accommodates for ProRated rents at either end of the term
 

ProRate.fmp12 2.zip