..
Lv 4
.. asked in 電腦與網際網路軟體 · 9 years ago

EXCEL可以在單一格內計算時數嗎??

例如我在同一格內輸入(時間)

1234-2234

我希望可以在另一格內顯示出

10

EXCEL可以在單一格內計算時數嗎??

或是有什麼方法可以讓它在輸入4位數後自動跳到右邊那格??

請達人解答

2 Answers

Rating
  • 鬍鬚
    Lv 7
    9 years ago
    Favorite Answer

    =(TEXT(RIGHT(A1,4),"00!:00")-TEXT(LEFT(A1,4),"00!:00"))*24

    2012-05-16 10:47:03 補充:

    假設原資料在A1

    =(TEXT(RIGHT(A1,4),"00!:00")-TEXT(LEFT(A1,4),"00!:00"))*24

    2012-05-16 10:47:28 補充:

    承讓了,謝謝K大!

    其實版大並未明確要顯示的格式是什麼,如果10小時30分要顯示"10:30"則要依K大的意見。

  • KK
    Lv 7
    9 years ago

    A1 =1234-2234

    B2 輸入下列公式

    =TEXT(TEXT(MID(A1,FIND("-",A1)+1,9),"00!:00")-TEXT(LEFT(A1,FIND("-",A1)-1),"00!:00"),"hh:mm")

    2012-05-14 19:52:37 補充:

    如果固定前後都是4位數時,可用下式

    =TEXT(TEXT(RIGHT(A1,4),"00!:00")-TEXT(LEFT(A1,4),"00!:00"),"hh:mm")

    2012-05-15 18:27:15 補充:

    鬍鬚大公式比較符合題意,請上答

    版大有問題再回覆就好

Still have questions? Get your answers by asking now.