How to calculate total rent / total salary between two dates in excel when the total number of days in each month is different?
How to pro-rata it accurately?
The daily rent would be different in each month – March has 31 days, June has 30 days, February has 28 days?! How to create an accurate rent roll given the daily rent would be different in each month?!
當每一個月的總日數都不同（2月有28日、6月有30日、3月有31日），到底如何準確計算到兩個日子中間的租金/薪金呢？每一個月的日租/日薪都不同！ 如何靠一條excel公式就可以pro rata？如何做到準確的rent roll呢？
——-
Cell A4 is commencement date (e.g. 22/3/2019) 起租日/起薪日
Cell C4 is expiry date 完租日/完薪日 (e.g. 19/6/2019)
Cell D4 is the monthly rent 月租/月薪 (e.g. $100,000)
公式如下:
=(DAY(EOMONTH(A4,0))-DAY(A4)+1)/DAY(EOMONTH(A4,0))*D4+(12*(YEAR(C4)-YEAR(A4))+MONTH(C4)-MONTH(A4)-1)*D4+DAY(C4)/DAY(EOMONTH(C4,0))*D4
=(31-22-1)/31*100000+(12*(2019-2019)+6-3-1)*100000+19/30*100000
– EOMONTH shows the end date of the month, DAY(EOMONTH(A2,0) will show 3. EOMONTH會顯示該月的總日數。
– YEAR, MONTH, DAY shows the year, month and date of the excel, 會顯示該月的年月日。
– 12*(YEAR(C4)-YEAR(A4)) 萬一跨年了。如果是同一年的日期，自動變成零。
impromptuz 