EXCEL表格怎样用函数公式实现租期及租金的自动计算？ 知道起租日及结束日，自动计算租期。知道租

EXCEL表格怎样用函数公式实现租期及租金的自动计算？ 知道起租日及结束日，自动计算租期。知道租

如下图，C2的公式如下并下拉：
=IF(DATEDIF(A2,B2,"YM")=0,IF(DATEDIF(A2,B2,"D")=0,"",DATEDIF(A2,B2,"D")&"天"),DATEDIF(A2,B2,"YM")&IF(DATEDIF(A2,B2,"MD")=0,"个月整","个月"&DATEDIF(A2,B2,"MD")&"天"))
 
E2的公式如下并下拉：
=IF(DATEDIF(A2,B2,"YM")=0,--LEFt(D2,LEN(D2)-2),LEFt(D2,LEN(D2)-2)*DATEDIF(A2,B2,"YM")+LEFt(D2,LEN(D2)-2)/DAY(EOMONTH(B2,0))*DATEDIF(A2,B2,"MD"))
 

追问
非常感谢您的回答！不好意思，我按照您的公式输入，计算出来的租期少了一天（租期是把起租当天及结束当天都算在租期内的），租金计算错误，还有就是如果B列不输入内容，C列及E列的公式都是无效。请问我在哪个环节出错了吗？
追答是我漏写了一部分公式，抱歉。
C2：
=IF(DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"YM")=0,IF(DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"D")=0,"",DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"D")&"天"),DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"YM")&IF(DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"MD")=0,"个月整","个月"&DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"MD")&"天"))



E2：
=IF(DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"YM")=0,--LEFt(D2,LEN(D2)-2),LEFt(D2,LEN(D2)-2)*DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"YM")+LEFt(D2,LEN(D2)-2)/DAY(EOMONTH(IF(B2="",TODAY(),B2)+1,0))*DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"MD"))

追问不好意思，还要麻烦你。按照你的公式输入后，计算租金尾数部分是按照当月天数计算的（如:一月份按31天，4月份则按30天），我想要任何一个月都是按30天计算该怎么表达？追答E2的公式改成这个：
=IF(DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"YM")=0,--LEFt(D2,LEN(D2)-2),LEFt(D2,LEN(D2)-2)*DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"YM")+LEFt(D2,LEN(D2)-2)/30*DATEDIF(A2,IF(B2="",TODAY(),B2)+1,"MD"))

　　你好：公式如下图：
　　=IF(DATEDIF(A2,B2,"M")=0,"",DATEDIF(A2,B2,"M"))&IF(DATEDIF(A2,B2,"M")=0,"","月")&IF(DATEDIF(A2,B2,"Md")=0,"",DATEDIF(A2,B2,"Md"))&IF(DATEDIF(A2,B2,"Md")=0,"整","天")



　　=ROUND(DATEDIF(A2,B2,"M")*D2+D2/DAY(DATE(YEAR(B2),MONTH(B2)+1,1)-1)*DATEDIF(A2,B2,"Md"),2)

C2公式：
=IF(DATEDIF(A2,IF(B2<>"",B2,TODAY()),"m")=0,"",DATEDIF(A2,IF(B2<>"",B2,TODAY()),"m"))&IF(DATEDIF(A2,IF(B2<>"",B2,TODAY()),"md")=0,"月整",IF(DATEDIF(A2,IF(B2<>"",B2,TODAY()),"m")=0,"","月"))&IF(DATEDIF(A2,IF(B2<>"",B2,TODAY()),"md")=0,"",DATEDIF(A2,IF(B2<>"",B2,TODAY()),"md")&"日")
下拉；

E2公式：
=IF(DATEDIF(A2,IF(B2<>"",B2,TODAY()),"m")=0,1*LEFt(D2,LEN(D2)-2),DATEDIF(A2,IF(B2<>"",B2,TODAY()),"m")*LEFt(D2,LEN(D2)-2)+DATEDIF(A2,IF(B2<>"",B2,TODAY()),"md")*LEFt(D2,LEN(D2)-2)/30)
下拉。