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,"过半;;今日截止"))))

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

声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com