[第124期]计算年休天数[已结]

jinlong506

1. =ROUND(LOOKUP(YEAR(NOW())-YEAR(B2),H$3:O$3/12,H$4:O$4)-SUM(((YEAR(NOW())-YEAR(B2))=H$3:O$3/12)*{0,5,2,2,3,3,2,3})*DATEDIF("1901/1/1",B2,"YD")/365,)

复制代码

先放个答案，占个楼，字数超100了  数组公式

41126238

1. =ROUND(SUM(ABS((DATE(YEAR(B2)+1,1,1)-B2)/365-{1,0})*LOOKUP(IFERROR(DATEDIF(B2,DATE(YEAR(TODAY())+{0,1},1,{1,0}),"m"),),$H$3:$O$4)),)

复制代码

1216281843

你好，这个是我的公式，可以给我看看那个少于100字符的公式是啥样的么？我想学习下？=IFERROR(ROUND(((DAYS(2018&"/"&MONTH(B2)&"/"&DAY(B2),$I$16)*INDEX($H$4:$O$4,MATCH(DAYS(2018&"/"&MONTH(B2)&"/"&DAY(B2),B2)/365*12-1,$H$3:$O$3))+(365-DAYS(2018&"/"&MONTH(B2)&"/"&DAY(B2),$I$16))*INDEX($H$4:$O$4,MATCH(DAYS($P$16,B2)/365*12,$H$3:$O$3)))/365),0),0)

sucx007

1. '=ROUND(SUM(IF(YEAR(B2+ROW(A:A))=YEAR(NOW()),LOOKUP(DATEDIF(B2,B2+ROW(A:A),"y"),{0,1,3,5,7,10,12,15;0,5,7,9,12,15,17,20})/365)),)

复制代码

如果能用h3:o4则,改=ROUND(SUM((YEAR(B2+ROW(A:A)-1)=YEAR(NOW()))*LOOKUP(DATEDIF(B2,B2+ROW(A:A)-1,"m"),H$3:O$4)/(365+(DAY(DATE(YEAR(B2+ROW(A:A)-1),3,0))=29))),)

1. =ROUND(SUM((YEAR(B2+ROW(A:A)-1)=YEAR(NOW()))*LOOKUP(DATEDIF(B2,B2+ROW(A:A)-1,"m"),H$3:O$4)/365),)

复制代码

sghsz

=IFERROR(ROUND((DATEDIF(DATE(2018,MONTH(B2),DAY(B2)),"2018-12-31","D")+1)/365*LOOKUP(YEAR(TODAY())-YEAR(B2),$H$3:$O$3/12,$H$4:$O$4)+(DATEDIF("2018-1-1",DATE(2018,MONTH(B2),DAY(B2)),"D"))/365*LOOKUP(YEAR(TODAY())-YEAR(B2)-1,$H$3:$O$3/12,$H$4:$O$4),0),0)

102339267

=IFNA(ROUND(SUM(LOOKUP(DATEDIF(B2,DATE(YEAR(NOW())+{1,0},,)+31,"m"),H$3:O$3,H$4:O$4)*ABS(DATE(YEAR(NOW())+{1,0},1,1)-DATE(YEAR(NOW()),MONTH(B2),DAY(B2))))/365,),)把IFNA改成IFERROR应该可以了，我在WPS里用第一个公式是可以的，切换了excel版本就出现错误了
=IFERROR(ROUND(SUM(LOOKUP(DATEDIF(B2,DATE(YEAR(NOW())+{1,0},,)+31,"m"),H$3:O$3,H$4:O$4)*ABS(DATE(YEAR(NOW())+{1,0},1,1)-DATE(YEAR(NOW()),MONTH(B2),DAY(B2))))/365,),)

杉槐liya

回复考题
=IFERROR(ROUND(((B2-DATE(YEAR(B2),1,1))/365)*OFFSET($H$4,0,SUMPRODUCT(($H$3:$O$3<((DATE(YEAR(NOW()),1,1)-B2)/365*12))*1)-1)+(1-(B2-DATE(YEAR(B2),1,1))/365)*OFFSET($H$4,0,SUMPRODUCT(($H$3:$O$3<((DATE(YEAR(NOW()),12,31)-B2)/365*12))*1)-1),0),"0")

265094

=ROUND(SUM(TRANSPOSE(H$4:O$4)/365*FREQUENCY("1/1"+ROW($1:$365),EDATE(B2,--I$3:O$3))),)

补充内容 (2018-12-16 12:23):
=ROUND(SUM(LOOKUP(DATEDIF(MIN(B2+1,"1/1"+1),"1/1"+ROW($1:$365),"m"),H$3:O$4)/365),)

补充内容 (2018-12-16 13:03):
=ROUND(SUM(IFERROR(LOOKUP(DATEDIF(B2+1,"1/1"+ROW($1:$365),"m"),H$3:O$4),)/365),)

补充内容 (2018-12-20 08:08):
=ROUND(SUM(LOOKUP(DATEDIF(B2,MAX(B2,"1/1"-1)+ROW($1:$365),"m"),H$3:O$4)/365),)

WJX-online

1. =IFNA(ROUND(SUM(LOOKUP((YEAR(TODAY())-YEAR(B2)-{0;1})*12,H$3:O$4)*MOD((43466-B2)*{1;-1}/365,1)),),)  经过多少次的题目理解，终于做出一个含"等号"共99个字节。2018-12-10 22:50  困难困难困困难。。。。
   
2. =IFNA(ROUND(SUM(LOOKUP((YEAR(TODAY())-YEAR(B2)-{0;1})*12,H$3:O$4)*MOD((B2-"1-1")*{-1;1}/365,1)),),)  没有找到什么好的办法2018-12-11 11:02
   
3. =IFNA(ROUND(SUM(LOOKUP((INT((NOW()-B2)/365)-{0;1})*12,H$3:O$4)*MOD((B2-"1-1")*{-1;1}/365,1)),),)   96个字符  2018-12-11 11:36
   
4. =TEXT(SUM(LOOKUP(TRUNC((NOW()-B2)/365-{0;1})*12,H$3:O$4)*MOD((B2-"1-1")*{-1;1}/365,1)),0)   89个字符   2018-12-12 17:18
   
5. =ROUND(SUM(LOOKUP(TRUNC((NOW()-B2)/365-{0;1})*12,H$3:O$4)*MOD((B2-"1-1")*{-1;1}/365,1)),)   89个字符   2018-12-12 17:18

复制代码

sadhare

超过字数了,想不出更好的公式