在excel表中怎么能显示保质期剩余的年数月数及天数

2024-11-19 14:50:04

推荐回答（4个）

回答1：

公式很长的

=DATEDIF(TODAY(),DATE(YEAR(A1)+LEFT(B1,FIND("年",B1)-1),MONTH(A1)+MID(B1,FIND("年",B1)+1,FIND("个",B1)-FIND("年",B1)-1),DAY(A1)),"y")&"年"

&DATEDIF(TODAY(),DATE(YEAR(A1)+LEFT(B1,FIND("年",B1)-1),MONTH(A1)+MID(B1,FIND("年",B1)+1,FIND("个",B1)-FIND("年",B1)-1),DAY(A1)),"ym")&"个月"

&DATEDIF(TODAY(),DATE(YEAR(A1)+LEFT(B1,FIND("年",B1)-1),MONTH(A1)+MID(B1,FIND("年",B1)+1,FIND("个",B1)-FIND("年",B1)-1),DAY(A1)),"md")&"天"

看了很头疼吧

如果是这样的格式就好做了

=DATEDIF(TODAY(),B2,"y")&"年"&DATEDIF(TODAY(),B2,"ym")&"个月"&DATEDIF(TODAY(),B2,"md")&"天"

回答2：

=DATEDIF(TODAY(),EDATE(A1,1.5*12),"Y")&"年"&DATEDIF(TODAY(),EDATE(A1,1.5*12),"YM")&"月"&DATEDIF(TODAY(),EDATE(A1,1.5*12),"MD")&"日"
或
=DATEDIF(TODAY(),DATE(YEAR(A1)+1,MONTH(A1)+6,DAY(A1)),"Y")&"年"&DATEDIF(TODAY(),DATE(YEAR(A1)+1,MONTH(A1)+6,DAY(A1)),"YM")&"月"&DATEDIF(TODAY(),DATE(YEAR(A1)+1,MONTH(A1)+6,DAY(A1)),"MD")&"日"

回答3：

=INT((WORKDAY(A1,548)-TODAY())/360)&"年"&INT(MOD((WORKDAY(A1,548)-TODAY()),360)/30)&"月"&MOD((WORKDAY(A1,548)-TODAY()),30)&"天"

－－－－－－－－－－－－－－

回答4：

=DATEDIF(TODAY(),B1,"Y")&"年"&DATEDIF(TODAY(),B1,"YM")&"月"&DATEDIF(TODAY(),B1,"MD")&"天"