返回列表 上一主題 發帖

[發問] 擷取儲存格日期問題

[發問] 擷取儲存格日期問題

1.PNG
2016-7-17 00:09

說明:
B欄公式,主要計算A欄日期是否小於D欄,如小於則B欄顯示"逾期"
但因為A欄單一儲存格有可能會有2個以上日期不一定(例如A2:A6),但只需參考最上面第一個日期為主即可,故新增了C欄輔助欄
C欄主要是抓取A欄 "-" 以前的日期,提供給B欄公式參照
D欄則是=TODAY()公式輔助欄


問題:
C欄公式如何直接合併進B欄公式內,故合併則希望可刪除C欄輔助欄
另外我發現目前C欄公式只要遇到A欄有2個以上日期(例如A2:A6),日期即便小於D欄,B欄仍不會出現"逾期",不確定是哪裡出了問題


以上求解~~~~~~非常感謝!!!!



測試檔.rar (10.89 KB)
*宅女一枚無誤*

回復 1# msmplay
B2=IF(ISERR(FIND("-",A2))*(A2>D2),"",IF(ISERR(FIND("-",A2))*(A2<D2),"逾期",IF(FIND("-",A2),IF(DATEVALUE(MID(A2,1,FIND("-",A2,1)-2))>D2,"","逾期"))))
向下複製
請參考!

TOP

本帖最後由 KCC 於 2016-7-17 13:22 編輯

回復 1# msmplay

    參考你原來公式列出可能要的檢測點,可自己視需要調整公式

    B2=IF((A2="")+(A2="-"),"",IF((IF(ISERR(FIND("-",A2)),A2,--(LEFT(A2,FIND("-",A2)-2)))<D2),"逾期",""))  下拉

     1.find("-",A1), 找到"-"的位置後,因為前面還有一個換行字元(char(10), 按 alt-enter後產生),所以要減 2
        若改成find char(10),就要改成減1。
     2.同1原理, 不減2的話截取出來的是字串,字串和日期比大小,字串>數字,所以不會逾期。
         自己用type()和len()檢測一下你截取出來的資料型態和長度就懂了。
     3.用iserr和isnumber都可以,邏輯處理剛好相反,if公式對調調整一下就可。

TOP

=IF(COUNT(0/(-LOOKUP(,-LEFT(A2,{8,9,10}))<D2)),"逾期","")

LEFT(A2,{8,9,10})
日期格式可能如下:
2016/3/1   8個字元
2016/3/12  9個字元
2016/12/1  9個字元
2016/10/23  10個字元

同時以陣列取{8,9,10}位元, 再以lookup帶出最完整日期來比對即可!
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 5# 准提部林

准大~~好厲害ㄛ!
*宅女一枚無誤*

TOP

為什麼輸入=-LOOKUP(,-LEFT(E2,{8,9,10}))
會是出現2016/7/15
這好強
但看不懂
求准大解釋

TOP

這樣也會有一樣效果欸
=IF(-LOOKUP(,-LEFT(A2,{8,9,10}))<D2,"逾期","")

TOP

回復 6# popomilk


=LEFT(A2,{8,9,10}) >產生這三種結果 {"2016/7/1","2016/7/15","2016/7/15?"} 紅色?表示〔換行字元〕
=-LEFT(A2,{8,9,10}) >變成這三種結果 {-42552,-42566,#VALUE!} 有效日期序列值只剩2個
=-LOOKUP(,-LEFT(A2,{8,9,10})) = 42566,亦即為 2016/7/15 (LOOKUP只抓最後一個有效數值)
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 7# popomilk


若A欄都存在日期,這公式可以!
但若為空格或非日期,會產生錯誤值!
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

B2=IF(--LEFT(A2,FIND(CHAR(10),A2&CHAR(10))-1)<D2,"逾期","")
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 人生不一定球球是好球,但是有歷練的強打者,隨時都可以揮棒。
返回列表 上一主題