Formula to Prorate Rent Based on Dates

I am using this formula to calculate a prorated rent amount:

=(DATEDIF(C20,C21,"m")*C8)+(C8/(EOMONTH(C20,0)-EOMONTH(C20,-1))*(EOMONTH(C20,0)-C20+1))-(C8/(EOMONTH(C21,0)-EOMONTH(C21,-1))*(EOMONTH(C21,0)-C21))

The problem is that if the two dates being used are the same date the formula assumes the difference is 1 day.  If I set the two dates a day apart it should calculate the difference as 1 not 2, etc, etc

Example:
                                                  Formula Shows                       Should Be
April 20, 2005 & April 21, 2005            2                                            1
April 30, 2005 & April 30, 2005            1                                            0

I appreciate your help.

Joe

 
 Send to a Friend     Printer Friendly   See Solution  
   
 Comment from matthewspatrick
Date: 04/03/2005 12:05PM PDT
 Comment  


Hi Joe,

How about sticking a -1 at the end?

Regards,

Patrick
 
Comment from joedunn
Date: 04/03/2005 12:21PM PDT
 Your Comment  


I tried that but the result of the formula is a dollar value.  In my test case the cell changes from $20 to $19.
 
Comment from patrickab
Date: 04/03/2005 12:36PM PDT
 Comment  


Joe,

If the rental period starts and finishes on the same day then the contract has lasted exactly 1 day. If the rental period starts on one day nd finishes the following day the contract lasts two days. Contractual 'days' must of necessity start and end at midnight, so starting and finishing on the same day means 24 hours - in other words one day. If have a zero number of days then by definition the contract cannot have existed.

I'd be interested in your response on the points I have made - particularly as it was a formula that I suggested!

Patrick
 
Comment from patrickab
Date: 04/03/2005 12:56PM PDT
 Comment  


Joe,

Whilst I do not agree with the implications of the datal differences quoted in the question, the modified formula is:

=(DATEDIF(A2,A3,"m")*A1)+(A1/(EOMONTH(A2,0)-EOMONTH(A2,-1))*(EOMONTH(A2,0)-A2))-(A1/(EOMONTH(A3,0)-EOMONTH(A3,-1))*(EOMONTH(A3,0)-A3))

Patrick
 
Comment from ture
Date: 04/04/2005 02:33AM PDT
 Comment  


Hi joedunn,

I find it difficult to understandyour formula. What do you want it to do, actually?

Best regards,
Ture Magnusson
Karlstad, Sweden
 
Comment from patrickab
Date: 04/04/2005 03:13AM PDT
 Comment  


Ture,

The formula pro-rates a rental according to the number of whole months and days from start to finish of a contract. The months are charges at a constant monthly rate but the days are only charged at a daily rate that depends on the number of days in the month. It's an usual method of charging but that's what joedunn wanted. The original formula worked OK but joedunn is now wanting it to work differently. I have already questioned the basis of the new calc. - see above. However if joedunn wants it that way then the new one will do it - even though I believe it is an incorrect calculation!

Patrick
 
Comment from ture
Date: 04/04/2005 03:23AM PDT
 Comment  


Thanks Patrick!

/Ture
 
Comment from joedunn
Date: 04/04/2005 09:57AM PDT
 Your Comment  


Patrick,

I am trying to determine if the resident owes any further rent.

I am using two cells and your formula to determine this.

c20 = Date Paid Through
c21 = Lease Obligation Rent

Date Paid Through:  October 31, 2005
Lease Obligation Date:  October 31, 2005

The formula shows one day of rent still due, in this case $20 and change.  If the Paid Through Date and Lease Obligation Date are the same then they should owe $0.00.

Your point about the contract dates being at least one day are correct.  However, in this situation they have already paid the rent for the last day.  I tried to add another layer to the If...Then statement that would check to see if the two dates were the same and if they were to make the resulting cell display $0.  I could not get this to work.

Your original formula was perfect for that time.  The enitre form, with your help has just evolved a bit.

Joe
 
Comment from ShadowProgrammer
Date: 04/04/2005 10:16AM PDT
 Comment  


Assuming EOMONTH is a function, What does it calculate ?

your last comment stated...
c20 = Date Paid Through
c21 = Lease Obligation Rent

What is in C8 ?

Tony.
 
Accepted Answer from patrickab
Date: 04/04/2005 12:35PM PDT
Grade: A
 Accepted Answer  


Joe,

The most recent incarnation of the formula:

=(DATEDIF(A2,A3,"m")*A1)+(A1/(EOMONTH(A2,0)-EOMONTH(A2,-1))*(EOMONTH(A2,0)-A2))-(A1/(EOMONTH(A3,0)-EOMONTH(A3,-1))*(EOMONTH(A3,0)-A3))

will give $0 due if the start and finish dates are the same.

Patrick
 
Comment from patrickab
Date: 04/05/2005 06:53AM PDT
 Comment  


Joe - Thanks for the grade - Patrick
 
Comment from joedunn
Date: 04/09/2005 02:05PM PDT
 Your Comment  


I think I have found a problem witht he above formula.


The current formula is:  (with correct cell references)

=(DATEDIF(C19,C20,"m")*C8)+(C8/(EOMONTH(C19,0)-EOMONTH(C19,-1))*(EOMONTH(C19,0)-C19))-(C8/(EOMONTH(C20,0)-EOMONTH(C20,-1))*(EOMONTH(C20,0)-C20))


If the date in  C19 is 3/31/2005 and the date in c20 is 3/31/05 the formula comes back with an answer of $0.00 which is correct.  However, any dates in the following month is wrong, April 2005.  For example if c8=$600

C19=3/31/5     c20=4/1/5     Formula=($580)     Should Be $20
C19=3/31/5     c20=4/2/5     Formula=($560)     Should Be $40
C19=3/31/5     c20=4/3/5     Formula=($540)     Should Be $60
C19=3/31/5     c20=4/4/5     Formula=($520)     Should Be $80
C19=3/31/5     c20=4/30/5   Formula=$0.00       Should Be $600
C19=3/31/5     c20=5/1/5     Formula=$19.35     Should Be $619.35

Is it because April has 30 days?
 
 
Start Free Trial