Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Amoeba asked in 電腦與網際網路軟體 · 1 decade ago

Excel IF AND MID綜合應用問題

請教各位高手

A欄是郵件寄出日,但時間如果是下午3點以後寄出的,就算是隔天的日期;B欄則是收件日,想請問有什麼方法可以計算出從寄件到收件經過幾天呢?

A欄資料如下:

2008/8/11 下午 03:41:48

2008/8/11 上午 09:44:18

2008/8/12 下午 02:18:43

2008/8/12 下午 04:52:57

B欄資料如下:

2008/8/12 上午 11:36:52

2008/8/13 上午 11:14:19

2008/8/12 下午 06:08:49

2008/8/13 下午 12:08:19

我本來想要利用下列函數先做A欄的日期判斷,但卻變成只要是下午寄出的,就會自動+1天,是不是有什麼地方有錯呢?

=IF(AND(ISNUMBER(FIND("下午",A1)),MID(A1,14,2)>=3),MID(A1,8,2)+1,MID(A1,8,2))

Update:

因是系統直接接下來的資料(資料量龐大),所以沒辦法改成日期/時間格式…

剛試了一下2位的方法,Judy的一開始有成功,但當遇到A欄的天數為單數時(如下列的8/5),就又失靈了…冏

A欄資料如下:

2008/8/5 下午 03:59:27

6 Answers

Rating
  • Judy
    Lv 5
    1 decade ago
    Favorite Answer

    原公式改成這樣

    =IF(AND(ISNUMBER(FIND("下午",A1)),VALUE(MID(A1,14,2))>=3),MID(A1,8,2)+1,MID(A1,8,2))

    以下供你參考

    如果你的儲存格能改用標準日期/時間格式,不要用文字,那只要用下列公式就可以算出經過天數

    =INT(B1)-INT(A1+0.375)+1

    依你的範例,經過天數應該是1,3,1,1

    2008-10-07 14:09:32 補充:

    這就是用文字麻煩的地方,因為2008/8/11與2008/8/5的長度不同,所以用MID抓時就會錯誤,所以若日期為2008/10/11也會錯。

    公式修改如下:

    =IF(ISNUMBER(FIND("下午",A1)),--(--MID(A1,FIND("下午",A1)+3,2)>=3),0)+MID(A1,FIND("/",A1,6)+1,2)

    參考檔案

    http://hilisalady.googlepages.com/know971007time.x...

    2008-10-08 09:19:26 補充:

    借用worlonzeng大師的公式,我也提供一個轉換日期/時間的公式

    =LEFT(A1,FIND(" ",A1))+RIGHT(A1,8)+IF(ISNUMBER(FIND("下午",A1)),0.5,0)

  • Anonymous
    1 decade ago

    熱鬧贊助 ~

    2008-10-07 20:27:05 補充:

    湊熱鬧 :

    =DATEVALUE(LEFT(A1,FIND(" ",A1)))+TIMEVALUE(RIGHT(A1,8))+9/24+0.5*COUNTIF(A1,"*下*")

    2008-10-07 20:28:55 補充:

    短些 :

    =DATEVALUE(LEFT(A1,FIND(" ",A1)))+TIMEVALUE(RIGHT(A1,8))+3/8+COUNTIF(A1,"*下*")/2

    2008-10-07 20:31:48 補充:

    模組區放入 :

    Function MyDate(Rg As Range) As Date

    MyDate = DateValue(Rg) + TimeValue(Rg) + 3 / 8

    End Function

    任意儲存格輸入

    =MyDate(A1)

    2008-10-07 20:55:33 補充:

    或 :

    Function MyDate2(Rg As Range) As Date

    MyDate2 = Rg

    End Function

    任意儲存格輸入

    =MyDate2(A1) + 3/8

  • 1 decade ago

    大家真的很熱心又超強的…

    我本來也有想說有沒有可以直接改格式的方法,無奈所學不深…就放棄了

    今天回家後,我會好好研究各位高手的方法,再向各位回報結果。

    真的很謝謝大家熱心的幫忙,這問題應該有很多解決方法,值得大家腦力激盪。

  • ?
    Lv 7
    1 decade ago

    將日期修正為 Excel 所接受的格式,試公式:

    TEXT((SUBSTITUTE(SUBSTITUTE(A1,"上午",),"下午","")&IF(COUNTIF(A1,"*上午*")," AM"," PM"))+"9:00","d")

  • ?
    Lv 7
    1 decade ago

    轉檔有問題,日期格式不統一也是問題的所在,無法再去做解決,所以我也刪掉解答了!

  • 夏日
    Lv 5
    1 decade ago

    二位手腳都好快,那我只好貼這裡了。

    就你的公式幫修一下

    =IF(AND(ISNUMBER(FIND("下午",A1)),--MID(A1,14,2)>=3),MID(A1,8,2)+1,--MID(A1,8,2))----------秀出來的才都會是數字不會一半文字類一半數字類

    另日期建議改成這類型的2008/01/01 才不會mid出錯位置。

    2008-10-07 13:15:01 補充:

    假設:ab欄內都是日期格式的話直接相減就行了

    C1算日期

    =IF(MOD(A1,1)>=15/24,DAY(A1+1),DAY(A1))

    =--(MOD(A1,1)>=15/24)+DAY(A1)

    D1算相差日

    =CEILING(B1-A1,1)

    寄出到收到相差小於等於24小時顯示1日

    只要大於24小時 小於等於48小時都算 2日

    如果不是日期格式就有點頭大了

    2008-10-07 13:32:23 補充:

    忽然想你這有跨月的問題31號 會變 32.............至於有這問題的話找人修了。

    2008-10-07 16:26:26 補充:

    JUDY大大這一題解的很好

    applerot大大TEXT也是不錯另類想法

    那我只好反過來改日期好了,反過來思考,改原始檔案....借applerot大大改一下,以防以後還要使用這日期,改成excel能看的懂的較好處理。

    =--TEXT((SUBSTITUTE(SUBSTITUTE(A1,"上午",),"下午",)+IF(COUNTIF(A1,"*下午*"),"12:00",)),"yyyy/mm/dd hh:mm:ss")

    跨月問題也能輕易解決。

Still have questions? Get your answers by asking now.