剖析excel日期、時間、星期的核心
日期、時間在excel中是很重要,常要用到的東西。但大部分的人還是搞不清它的來龍去脈,尤其是牽涉到日期時間的計算更是常常會遇到一些問題而繞不出來,這裡徹底為大家做一個剖析,讓大家清楚是怎麼回事。
要講解日期、時間,要先介紹一個東西,叫做 樣式。樣式是什麼東西?樣式就是儲存格的化妝師或是面罩。你在儲存格輸入一個數字或文字,譬如,1234, 可以要它顯示出小數兩位,1234.00,
可以要它顯示千進位的撇點,1,234。也可以要它靠左、靠右、置中,紅色的字,16號字,粗體字.....等等,你可以指定各種各樣的樣式。就像老式的紅綠燈,有時候颱風損壞時我們看到裡面就是一個白熾燈泡,外面罩上一個紅色的燈罩,我們就看到紅燈;罩上綠色燈罩,就看到綠燈一樣。
所以,看到什麼是一回事,儲存格的內容真正是什麼又是一回事。譬如我的另一篇文章,「在excel 中如何讓 5元 × 8 支 =
40 元?」 就有教到,你在儲存格輸入 5元,就是「文字」,文字是不能計算的。但你可以只輸入 5, 而應用自訂的樣式 123元,來顯示 5元,看到「5元」,但電腦中其實只是5 ,就可以進行計算。
樣式 在哪裡?格式(0)/ 樣式(S), 最常用的樣式 就是 一般,你點 修改(M)進去看看,就有一大堆內建好的樣式。再點入日期,點入 時間,就可以看到各式各樣的樣式。最下面還有 自訂,你還可以自己訂一個你方便的樣式,譬如上面說的 123元。
進入主題。我們輸入2008/10/18 , 請問在電腦中它是文字還是數字?答案是數字。2008/10/18 表面上是文字,但實際上只是以 yyyy/mm/dd這個樣式顯現出來而已。既然是數字,那就可以來相減。A1= 2008/10/18
, A2=2008/10/12 , A3=A1-A2 =6, 可以算出來差六天。
我們再回到 A1, 格式(0)/ 樣式(S), 選 一般 這個樣式,出現什麼? 39739, 果然是數字!這是亂數嗎?不是!
我們說每年的元旦是那一年的第一天,請問2008年的第100天是哪一天?100-1月31天- 2月29天- 3月31 天=10, 所以 2008/4/10 是2008年的第100天。
接著我們要揭曉excel時間日期設定的核心
規則一、上述的數字,以日為單位。
規則二、如果要顯示成實際的年月日,就是換算成1900年1月1日 的前一日0時0分0秒算起的第幾天。
回到A1,
39737, 我們把他改成1, 再回去把樣式改成 2001/3/14 , 就出現 1900/1/1 . 你再輸入 39737, 就出現2008/10/18 . 也就是說今天2008/10/18 是1900年元旦算起的第39737天。
再來講樣式的規則:
規則一、內建每一格的樣式都是 一般。你要去改它,才會成為你的樣式。
規則二、任一個儲存格,被改過後,電腦就記憶著前一個樣式。
規則三、excel是智慧型的軟體,它也經常會自行判斷選擇最合適的樣式來表示。
所以,剛剛 2008/10/18
– 2008/10/12 , 其實電腦內部是 39737- 39731=6.
接著講 星期。大家知道星期就是 mod 7, 星期的函數是 weekday(), A1= 2008/10/18
, B1= weekday(A1, 2) = 6, 我們把1900/1/1 的前一天為基準日,那一天為星期六。所以 mod (39737, 7) +6 = 6. 有點複雜,但 weekday 就是從基準日計算過來的。
然後來講時間。A1輸入16:00, 然後修改 樣式 為 一般 看看存了哪一個數字?0.6666666
(天),再改為日期的樣式2001/3/4 看看, 出現的是 1900/1/0 16:00, 也就是從基準日算起的日期時間。
所以,日期是數字,可以計算。時間也是數字,也可以計算。得到的單位都是日。但依照你選的樣式 或電腦自動判斷幫你選的樣式,選示成日期、時間,日數或其他組合。
接著有人想要計算時間差額的加總,算成以時為單位要怎麼做?就是乘以24就可以了,0.6666666 ×24 =16 (小時)。如果要算成分呢? × 1,440就得到分。× 86,400就得到秒。
如果你要跨日計算工時,A1=16:00, B1=2:00, C1=B1-A1會不會得到10小時,不會!電腦會出現######, 他在罵你!時間不可以是負值。但你將之改為一般格式,就得到-0.583333, 因為電腦不會知道你的2:00是隔一天的早上,他還是會認為是今天早上2:00.
隔一天的早上的2:00該如何處理?最簡單的方法就是+24:00, 輸入26:00就一切都正確了。
沒有留言:
張貼留言