[求助]篩選生日期間

[jackdream]

各位大大:
資料如附件所示,若要篩選生日在 5/5 ~ 5/25 之間,
該如何進行呢?
p.s: 請勿使用 VBA ,感謝~

[hsieh]

C1陣列公式
{=IF(ROW(A1)>SUM(IF((MONTH($B$2:$B$10)=5)*(DAY($B$2:$B$10)>=5)*(DAY($B$2:$B$10)<=25),1)),"",INDIRECT("A"&SMALL(IF((MONTH($B$2:$B$10)=5)*(DAY($B$2:$B$10)>=5)*(DAY($B$2:$B$10)<=25),ROW($B$2:$B$10)),ROW(A1))))}
下拉

[jackdream]

感謝 hsieh 大大指導~
若要改為篩選 5/5 ~ 6/20 之間,該如改修改呢? 感謝~

[hsieh]

多個條件
{=IF(ROW(A1)>SUM(IF((MONTH($B$2:$B$10)>=5)*(MONTH($B$2:$B$10)<=6)*(DAY($B$2:$B$10)>=5)*(DAY($B$2:$B$10)<=25),1)),"",INDIRECT("A"&SMALL(IF((MONTH($B$2:$B$10)>=5)*(MONTH($B$2:$B$10)<=6)*(DAY($B$2:$B$10)>=5)*(DAY($B$2:$B$10)<=25),ROW($B$2:$B$10)),ROW(A1))))}

[jackdream]

大大~若篩選期間為 5/5 ~ 6/25 ,您的公式沒問題,
但是篩選 5/5 ~ 6/20 ,我將公式修改如下,則"辛"不會出現~
能再協助 check 看看嗎? 感謝!

{=IF(ROW(A1)>SUM(IF((MONTH($B$2:$B$10)>=5)*(MONTH($B$2:$B$10)<=6)*(DAY($B$2:$B$10)>=5)*(DAY($B$2:$B$10)<=20),1)),"",INDIRECT("A"&SMALL(IF((MONTH($B$2:$B$10)>=5)*(MONTH($B$2:$B$10)<=6)*(DAY($B$2:$B$10)>=5)*(DAY($B$2:$B$10)<=20),ROW($B$2:$B$10)),ROW(A1))))}

[hsieh]

Try it !
或
{=IF(ROW(A1)>SUM(IF((DATEVALUE("1911/"&TEXT($B$2:$B$10,"m/d"))>=DATEVALUE("1911/5/5"))*(DATEVALUE("1911/"&TEXT($B$2:$B$10,"m/d"))<=DATEVALUE("1911/6/20")),1)),"",INDIRECT("A"&SMALL(IF((DATEVALUE("1911/"&TEXT($B$2:$B$10,"m/d"))>=DATEVALUE("1911/5/5"))*(DATEVALUE("1911/"&TEXT($B$2:$B$10,"m/d"))<=DATEVALUE("1911/6/20")),ROW($B$2:$B$10)),ROW(A1))))}