Excel_時間資料處理

Excel的時間序列. 時間上則為00:00:00至23:59:59，可以0~0.99998426代表 計算方式可以理解為1天為數字1 而一天包含24小時等於1400分鐘等於86400秒       Published LinkedwithGitHub Like Bookmark Subscribe Edit #Excel_時間資料處理 >[color=#40f1ef][name=LHB阿好伯,Dec26,2019][:earth_africa:](https://www.facebook.com/LHB0222/) ######tags:`Excel` [TOC] 前幾天完成了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 若要查看時間所代表的序列值將格式選為數值即可  #時間的輸入 日期最簡單的輸入方式就是以斜線`/`與減號`-`分隔 :::danger yyyy/mm/dd yyyy-mm-dd ::: ``` 2019/12/19 2019-12-19 ```  而時間上則是以冒號`:`將小時、分鐘、秒數作區隔 可以使用24小時制或是12小時制(am、pm、AM、PM) :::danger hh:mm:ss hh:mm hh:mmam hh:mmpm ::: 原則上在安裝中文版的Excel中也可以辨識一些中文格式 例如年、月、日、時、分、秒、上午、下午 :::danger yyyy"年"mm"月"dd"日" hh"時"mm"分"ss"秒" 上午/下午hh"時"mm"分"ss"秒" :::  但像是輸入幾月幾號就沒用了 簡單的判斷方式可以將日期作加減 若是文字格式則無法計算  就需要利用較複雜的公式將字串中的日期提取出來做判斷 :::danger =DATE(LEFT(B1,4),MID(B1,6,2),MID(B1,9,2)) :::  ##取得系統時間 傳回目前日期的序列值 `TODAY()`回傳現在日期而`NOW()`則包含時間 :::danger =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()`函數來達到相同的結果 :::danger =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(星期六)。

與舊版MicrosoftExcel的性質相同| |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`  #計算實際工作天數 :::danger =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) ::: ==Start_dateandend_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必學函數(第二版)(大數據時代必備的資料統計運算力！)](https://www.books.com.tw/exep/assp.php/gtgrthrst4577/products/0010782724?utm_source=gtgrthrst4577&utm_medium=ap-books&utm_content=recommend&utm_campaign=ap-201912)[name=鄧文淵,文淵閣工作室] >[計算兩個時間之間的差](https://support.office.com/zh-tw/article/%e8%a8%88%e7%ae%97%e5%85%a9%e5%80%8b%e6%99%82%e9%96%93%e4%b9%8b%e9%96%93%e7%9a%84%e5%b7%ae-e1c78778-749b-49a3-b13e-737715505ff6?ui=zh-TW&rs=zh-TW&ad=TW)[name=©Microsoft2019] >[WEEKDAY函數](https://support.office.com/zh-tw/article/weekday-函數-60e44483-2ed1-439f-8bd0-e404c190949a?NS=EXCEL&Version=90&SysLcid=1028&UiLcid=1028&AppVer=ZXL900&HelpId=xlmain11.chm60118&ui=zh-TW&rs=zh-TW&ad=TW)[name=©Microsoft2019] >[TEXT函數](https://support.office.com/zh-tw/article/text-函數-20d5ac4d-7b94-49fd-bb38-93d29371225c?NS=EXCEL&Version=90&SysLcid=1028&UiLcid=1028&AppVer=ZXL900&HelpId=xlmain11.chm60096&ui=zh-TW&rs=zh-TW&ad=TW)[name=©Microsoft2019] >[NETWORKDAYS.INTL函數](https://support.office.com/zh-tw/article/networkdays-intl-函數-a9b26239-4f20-46a1-9ab8-4e925bfd5e28?NS=EXCEL&Version=90&SysLcid=1028&UiLcid=1028&AppVer=ZXL900&HelpId=xlmain11.chm60568&ui=zh-TW&rs=zh-TW&ad=TW)[name=©Microsoft2019] 本文章收錄至[Office技能樹]( https://coggle.it/diagram/W4FNtodcxln_KJa2/t/office-%E6%8A%80%E8%83%BD%E6%A8%B9/b3ae8decb8c1ca82de6a9ab3d5156148c7865c506e2edab1855c656b9554a5fd) 全文分享至 https://www.facebook.com/LHB0222/ 有疑問想討論的都歡迎於下方留言 喜歡的幫我分享給所有的朋友\o/ 有所錯誤歡迎指教  × Signin Email Password Forgotpassword or Byclickingbelow,youagreetoourtermsofservice. SigninviaFacebook SigninviaTwitter SigninviaGitHub SigninviaDropbox SigninviaGoogle NewtoHackMD?Signup