#分享 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 參數傳回指定工作日數之前或之後日期的序列值
28
回應 2
文章資訊
共 2 則回應
感謝分享!!覺得很有用
B1 不客氣 希望有幫助到妳