Excel剩余有效期实现公式?

发布网友 发布时间：2022-04-24 07:35

我来回答

共5个回答

热心网友 时间：2022-06-17 16:08

用if函数配合日期函数就可以达到目的，

效果图和部分公式如下，

=DATE(YEAR(B3),MONTH(B3)+C3,DAY(B3))，

截止日期预警的公式为：

=IF(DATE(YEAR(B3),MONTH(B3)+C3,DAY(B3))<TODAY(),"过期",IF(DAY(D3)<DAY(TODAY()),(MONTH(D3)-MONTH(TODAY())-1)&"月"&DAY(D3)+30-DAY(TODAY())&"天",(MONTH(D3)-MONTH(TODAY()))&"月"&DAY(D3)-DAY(TODAY())&"天"))

下拉填充就好！

热心网友 时间：2022-06-17 16:08

效果如图，公式如下：

D3公式：

=IF(TODAY()<EDATE(B3,C3),TEXT(DATEDIF(TODAY(),EDATE(B3,C3),"M"),"0个月;;")&TEXT(DATEDIF(TODAY(),EDATE(B3,C3),"MD"),"0天;;"),TEXT(IFERROR(EDATE(B3,C3)-TODAY()+0/(B3*C3),""),";过期;今日止;-"))

E3公式：

=IF(D3="-","-",TEXT(EDATE(B3,C3),"YYYY-MM-DD")&IF(EDATE(B3,C3)-TODAY()<15,TEXT(EDATE(B3,C3)-TODAY(),"临期;已过期;今日止"),TEXT((EDATE(B3,C3)-TODAY())/(EDATE(B3,C3)-B3),"[>0.5] ;过半")))

--------------------------------------------------

D3改后公式：

=IF(COUNTIF(B3:C3,"=")>0,"-",IF(TODAY()<EDATE(B3,C3),TEXT(DATEDIF(TODAY(),EDATE(B3,C3),"M"),"0个月;;")&TEXT(DATEDIF(TODAY(),EDATE(B3,C3),"MD"),"0天;;"),TEXT(EDATE(B3,C3)-TODAY(),";过期;今日止")))

E3新公式：(保质期还长的年份四位数，其余二位数)

=TEXT(IF(D3="-","-",TEXT(EDATE(B3,C3),"YY-MM-DD")&IF(EDATE(B3,C3)-TODAY()<15,TEXT(EDATE(B3,C3)-TODAY(),"临期;已过期;今日止"),TEXT((EDATE(B3,C3)-TODAY())/(EDATE(B3,C3)-B3),"[>0.5] ;过半"))),"YYYY-MM-DD")

热心网友 时间：2022-06-17 16:09

一、在剩余有效期单元格输入

=IFERROR(IF(DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))<NOW(),"过期",INT((DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365)/30)&"个月"&(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365-INT(((DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365)/30))*30&"天")),"-")

二、在截止日期预警单元格输入

=IFERROR(IF(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))<0,TEXT(DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)),"yyyy-mm-dd") & " 过期",IF(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))<=30,TEXT(DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)),"yyyy-mm-dd") & " 临期",IF(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))>=90,TEXT(DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)),"yyyy-mm-dd"),TEXT(DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)),"yyyy-mm-dd") & " 过半"))),"-")

三、注意单元格，我的是从B2开始。

四、效果图

追答可以，增加个if判断
=IFERROR(IF(DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2))<NOW(),"过期",IF(INT((DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365)/30)=0,DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365-INT(((DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365)/30))*30&"天",INT((DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365)/30)&"个月"&(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365-INT(((DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))-INT(DAYS360(NOW(),DATE(YEAR(B2),MONTH(B2)+C2,DAY(B2)))/365)*365)/30))*30&"天"))),"-")

热心网友 时间：2022-06-17 16:09

如图：

D3公式：

=IF(OR(B3="空",C3="空"),"-",IFERROR(DATEDIF(TODAY(),DATE(YEAR(B3),MONTH(B3)+C3,DAY(B3)),"m")&"个月"&DATEDIF(TODAY(),DATE(YEAR(B3),MONTH(B3)+C3,DAY(B3)),"md")&"天","过期"))

下拉；

E3公式：

=IF(D3="-","-",TEXT(DATE(YEAR(B3),MONTH(B3)+C3,DAY(B3)),"e-mm-dd")&IF(D3="过期","过期",IF(DATEDIF(B3,TODAY(),"m")>=C3-1,"临期",IF(D3="过期","过期",IF(DATEDIF(B3,TODAY(),"m")>=C3/2,"过半","")))))

下拉。

热心网友 时间：2022-06-17 16:10

根据你的图示，D3、B3单元格比较简单的二个公式为：

D3=IF(COUNTIF(B3:C3,"=")>0,"-",TEXT(EDATE(B3,C3)-TODAY(),"剩余0天;过期;今日止"))

F3=IF(D3="-",D3,TEXT(EDATE(B3,C3),"YY-MM-DD")&IF(D3="过期",D3,TEXT(TODAY()-B3-(EDATE(B3,C3)-B3)/2,"过半;临期;今日截止")))

复制并下拉，即可

若非要按图上的显示效果，则可把D3单元格公式改为：

=IF(COUNTIF(B3:C3,"=")>0,"-",IF(TODAY()>EDATE(B3,C3),"过期",IF(TODAY()=EDATE(B3,C3),"今日止",DATEDIF(TODAY(),EDATE(B3,C3),"M")&"个月"&TEXT(DATEDIF(TODAY(),EDATE(B3,C3),"MD"),"0天;;"))))

对于F3单元格的公式，则需要确定在“过半”和“临期”重叠时哪个优先？？？

现以“临期”优先为，并对于未“过半”但且又未到“临期”的显示为空白的规则，则：

F3=IF(D3="-",D3,TEXT(EDATE(B3,C3),"YY-MM-DD")&IF(D3="过期",D3,IF(ABS(EDATE(B3,C3)-TODAY()-7.5)<7.5,"临期",TEXT(TODAY()-B3-(EDATE(B3,C3)-B3)/2,"过半;;今日截止"))))

老喜欢折腾啊！！！

若是有临期与过半重叠的时候你没有说明！！！

      就按你的图上半句说明再折腾一回吧！！

现以1-14天“临期”优先为例，并对于非“过半”但未到“临期”的时间段显示为空白操作，则F3单元格应该输入公式为：

=IF(D3="-",D3,TEXT(EDATE(B3,C3),"YY-MM-DD")&IF(D3="过期",D3,IF(ABS(EDATE(B3,C3)-TODAY()-7.5)<7.5,"临期",TEXT(TODAY()-B3-(EDATE(B3,C3)-B3)/2,"过半;;今日截止"))))

复制并下拉，即可…………