Excel formula 公式 – 租金/薪金rent/salary pro rata by days

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)) 萬一跨年了。如果是同一年的日期，自動變成零。