HackMDExcel_時間資料處理
LHB阿好伯, Dec 26, 2019
tags: Excel
前幾天完成了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
而時間上則是以冒號:將小時、分鐘、秒數作區隔
可以使用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(您要設定格式的值, "您要套用的格式代碼")
搭配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 參數傳回指定工作日數之前或之後日期的序列值
參考資料
翻倍效率工作術：不會就太可惜的Excel 必學函數(第二版) (大數據時代必備的資料統計運算力！) 鄧文淵, 文淵閣工作室
計算兩個時間之間的差© Microsoft 2019
WEEKDAY 函數© Microsoft 2019
TEXT 函數© Microsoft 2019
NETWORKDAYS.INTL 函數© Microsoft 2019
本文章收錄至Office 技能樹
全文分享至
https://www.facebook.com/LHB0222/
有疑問想討論的都歡迎於下方留言
喜歡的幫我分享給所有的朋友 \o/
有所錯誤歡迎指教
