Calculate Number of Days in Next Month

[JTSmith]

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!

[LaRetta]

You can get how many days in any month by:  

 

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

[JTSmith]

You can get how many days in any month by:  

 

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

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

[Ocean West]

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, 2014 by Ocean West
rounded the result.

[LaRetta]

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. :-)

[comment]

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

[LaRetta]

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

[JTSmith]

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...

[JTSmith]

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.

[comment]

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.

[JTSmith]

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.

[comment]

 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?

[JTSmith]

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.

[JTSmith]

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

[comment]

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...

[JTSmith]

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.  

[comment]

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

 

Uhm... I thought I already did?

[Ocean West]

Try this on for size...

 

 

ProRate.fmp12.zip

[Ocean West]

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