发布网友 发布时间: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,"过半;;今日截止"))))
复制并下拉,即可…………