EXCEL表格怎样用函数公式实现租期及租金的自动计算？ 知道起租日及结束日，自动计算租期。知道租
发布网友
发布时间：2022-04-29 08:30
共3个回答
热心网友
时间：2023-10-13 14:12
如下图，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"))
热心网友
时间：2023-10-13 14:12
如下图，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"))
热心网友
时间：2023-10-13 14:13
你好：公式如下图：
=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)
热心网友
时间：2023-10-13 14:13
=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)
下拉。
热心网友
时间：2023-10-13 14:13
你好：公式如下图：
=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)
热心网友
时间：2023-10-13 14:12
如下图，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"))
热心网友
时间：2023-10-13 14:13
=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)
下拉。
热心网友
时间：2023-10-13 14:12
如下图，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"))
热心网友
时间：2023-10-13 14:13
你好：公式如下图：
=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)
热心网友
时间：2023-10-13 14:13
你好：公式如下图：
=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)
热心网友
时间：2023-10-13 14:13
=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)
下拉。
热心网友
时间：2023-10-13 14:13
=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)
下拉。
热心网友
时间：2023-10-13 14:12
如下图，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"))
热心网友
时间：2023-10-13 14:13
你好：公式如下图：
=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)
热心网友
时间：2023-10-13 14:13
=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)
下拉。
