帖子: 218
主題: 73
精華: 0
積分: 290
點名: 0
作業系統: WIN10
軟體版本: Office2010
閱讀權限: 20
性別: 女
註冊時間: 2014-5-19
最後登錄: 2022-11-29

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2016-7-17 00:11 | 只看該作者

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

說明：
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)

樓主熱帖

*宅女一枚無誤*

aer

中學生 Rank: 2

帖子: 190
主題: 16
精華: 0
積分: 236
點名: 280
作業系統: windows xp
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2011-8-8
最後登錄: 2024-11-15

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

2^#

發表於 2016-7-17 10:45 | 只看該作者

回復 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

中學生 Rank: 2

帖子: 85
主題: 0
精華: 0
積分: 126
點名: 0
作業系統: WIN7
軟體版本: 2007
閱讀權限: 20
性別: 男
註冊時間: 2015-8-31
最後登錄: 2022-4-15

3^#

發表於 2016-7-17 13:09 | 只看該作者

本帖最後由 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

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-15

4^#

發表於 2016-7-17 20:54 | 只看該作者

=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帶出最完整日期來比對即可!

TOP

msmplay

中學生 Rank: 2

帖子: 218
主題: 73
精華: 0
積分: 290
點名: 0
作業系統: WIN10
軟體版本: Office2010
閱讀權限: 20
性別: 女
註冊時間: 2014-5-19
最後登錄: 2022-11-29

5^#

發表於 2016-7-18 12:46 | 只看該作者

回復 5# 准提部林

准大~~好厲害ㄛ！

*宅女一枚無誤*

TOP

popomilk

中學生 Rank: 2

帖子: 34
主題: 4
精華: 0
積分: 50
點名: 0
作業系統: WIN7
軟體版本: WIN7
閱讀權限: 20
性別: 男
註冊時間: 2016-7-11
最後登錄: 2016-12-2

論壇贊助勳章

6^#

發表於 2016-7-19 11:47 | 只看該作者

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

TOP

popomilk

中學生 Rank: 2

帖子: 34
主題: 4
精華: 0
積分: 50
點名: 0
作業系統: WIN7
軟體版本: WIN7
閱讀權限: 20
性別: 男
註冊時間: 2016-7-11
最後登錄: 2016-12-2

論壇贊助勳章

7^#

發表於 2016-7-19 12:01 | 只看該作者

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-15

8^#

發表於 2016-7-19 13:43 | 只看該作者

回復 6# popomilk

=LEFT(A2,{8,9,10})　＞產生這三種結果　{"2016/7/1","2016/7/15","2016/7/15?"}　紅色？表示〔換行字元〕
=-LEFT(A2,{8,9,10})　＞變成這三種結果　{-42552,-42566,#VALUE!}　有效日期序列值只剩２個
=-LOOKUP(,-LEFT(A2,{8,9,10}))　＝　42566，亦即為 2016/7/15　（LOOKUP只抓最後一個有效數值）

TOP

准提部林