#分享 Excel時間資料處理
2019年12月20日 23:34
目前已開放hackmd的協作功能
若有興趣或是覺得需要補充的都可以前往編輯
建議由HackMD觀看本文
-----------目錄------------
Excel_時間資料處理
Excel時間基礎
Excel的日期序列
Excel的時間序列
時間的輸入
取得系統時間
時間的加減
星期的判斷
計算實際工作天數
-----------------------------
更多Office相關文章以整理收錄至
大家可以參考看看若每項功能皆會使用應該可以算是熟練Office了
有疑問想討論的都歡迎於下方留言
喜歡的幫我分享給所有的朋友 \o/
有所錯誤歡迎指教
************本文****************
前幾天完成了R語言中的時間處理
今天來講講Excel中的時間格式
相比於R語言Excel中的時間格式更加的簡單使用
相對來說也有更多侷限性
# Excel時間基礎
## Excel的日期序列
Excel的日期是由從1900年01月01日開始為數字1==每日遞增1==至9999年12月31日2958465為止
## Excel的時間序列
時間上則為00:00:00至23:59:59，可以0~0.99998426代表
計算方式可以理解為1天為數字1
而一天包含24小時等於1400分鐘等於86400秒
則一秒就是1/24/60/60=1.15741E-05
若要查看時間所代表的序列值將格式選為數值即可
# 時間的輸入
日期最簡單的輸入方式就是以斜線`/`與減號`-`分隔
yyyy/mm/dd
yyyy-mm-dd
2019/12/19
2019-12-19
而時間上則是以冒號`:`將小時、分鐘、秒數作區隔
可以使用24小時制或是12小時制(am、pm、AM、PM)
hh:mm:ss
hh:mm
hh:mm am
hh:mm pm
原則上在安裝中文版的Excel中也可以辨識一些中文格式
例如年、月、日、時、分、秒、上午、下午
yyyy"年"mm"月"dd"日"
hh"時"mm"分"ss"秒"
上午/下午hh"時"mm"分"ss"秒"
但像是輸入幾月幾號就沒用了
簡單的判斷方式可以將日期作加減
若是文字格式則無法計算
就需要利用較複雜的公式將字串中的日期提取出來做判斷
=DATE(LEFT(B1,4),MID(B1,6,2),MID(B1,9,2))
## 取得系統時間
傳回目前日期的序列值
`TODAY()`回傳現在日期而`NOW()`則包含時間
=TODAY()
=NOW()
可作為報表自動算剩餘時間的函數
在每次開啟檔案時都會自動更新時間
# 時間的加減
而在Execl中的日期計算與R並不相同
在R與中時間加1則為一秒Excel中加1則為一天
所以若是需要增加一秒則須加上1天/24小時/60分/60秒(1.15741E-05)
像下面的案例假設若是要計算所經過的時間來算加班時數的話
相減後會得到一個奇怪的答案==上午 04:55:00==
這是因為Excel的顯示格式上的問題
只需要將格式自訂成`h:mm`即可
若是需要精準到秒數只需要修改成`h:mm:ss`即可
若是要計算日期也是以相同方式進行修改
簡單的日期相減差幾天可以將格式轉為通用格式即可
若是想算年資幾年就需要將格式改成`y/m/d`
上述更改格式方式也可以使用`TEXT()`函數來達到相同的結果
=TEXT(您要設定格式的值, "您要套用的格式代碼)
=TEXT(x-y,"y/m/d")
=TEXT(x-y,"h:mm:ss")
搭配`TODAY()`可以用於自動計算年資等資料
# 星期的判斷
在Excel中也提供了幾個很實用的函數
例如`WEEKDAY(serial_number[return_type])`
Return_type 這決定傳回值的類型
| Return_type | 傳回的數字 |
|---|---|
| 1 或省略 | 數字 1 (星期日) 到 7 (星期六)。與舊版 Microsoft Excel 的性質相同 |
| 2 | 數字 1 (星期一) 到 7 (星期日) |
| 3 | 數字 0 (星期一) 到 6 (星期六) |
| 11 | 數字 1 (星期一) 到 7 (星期日) |
| 12 | 數字 1 (星期二) 到 7 (星期一) |
| 13 | 數字 1 (星期三) 到 7 (星期二) |
| 14 | 數字 1 (星期四) 到 7 (星期三) |
| 15 | 數字 1 (星期五) 到 7 (星期四) |
| 16 | 數字 1 (星期六) 到 7 (星期五) |
| 17 | 數字 1 (星期日) 到 7 (星期六) |
或是也可以修改格式顯示為`星期X`或是`週X`
# 計算實際工作天數
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
==Start_date and end_date== 這是要計算差距的日期
Start_date 可以早於、等於或晚於 end_date
==Weekend== 這會指出 start_date 和 end_date 之間
屬於週末且不包含在所有工作日數中的日子Weekend 是指定何時是週末的數字或字串
==holidays== 這是要從工作日行事曆排除的一組選擇性的一個或多個日期
可用於扣除中間國定假日
| Weekend 數字 | 週末日 |
| ------------ | -------------- |
| 1 或省略 | 星期六、星期日 |
| 2 | 星期日、星期一 |
| 3 | 星期一、星期二 |
| 4 | 星期二、星期三 |
| 5 | 星期三、星期四 |
| 6 | 星期四、星期五 |
| 7 | 星期五、星期六 |
| 11 | 僅星期日 |
| 12 | 僅星期一 |
| 13 | 僅星期二 |
| 14 | 僅星期三 |
| 15 | 僅星期四 |
| 16 | 僅星期五 |
| 17 | 僅星期六 |
Weekend 字串值長度為七個字元，且字串中每個字元會代表一週內的一天，從星期一開始
1代表非工作日， 0代表 workday。 字串中僅允許字元 1 和 0。 使用 1111111 一律會傳回 0。
例如，0000011 代表週末為星期六和星期日。
最後補充幾個可能會用到的函數
`EOMONTH(start_date, months)` 傳回在 start_date 之前或之後所指定之月份數之當月最後一天的序列值
`WORKDAY.INTL(start_date, days, [weekend], [holidays])` 使用自訂 weekend 參數傳回指定工作日數之前或之後日期的序列值
