# Excel_時間資料處理
> [color=#40f1ef][name=LHB阿好伯, Dec 26, 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:mm am
hh:mm pm
:::

原則上在安裝中文版的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 (星期六)。與舊版 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`


# 計算實際工作天數

:::danger
=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 必學函數(第二版) (大數據時代必備的資料統計運算力！)](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=© Microsoft 2019]
>[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=© Microsoft 2019]
>[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=© Microsoft 2019]
>[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=© Microsoft 2019]

本文章收錄至[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/

有所錯誤歡迎指教


<style>

html, body, .ui-content {
background-color: #333;
color: #ddd;
}

body > .ui-infobar {
display: none;
}

.ui-view-area > .ui-infobar {
display: block;
}

.markdown-body h1,
.markdown-body h2,
.markdown-body h3,
.markdown-body h4,
.markdown-body h5,
.markdown-body h6 {
color: #ddd;
}

.markdown-body h1,
.markdown-body h2 {
border-bottom-color: #ffffff69;
}

.markdown-body h1 .octicon-link,
.markdown-body h2 .octicon-link,
.markdown-body h3 .octicon-link,
.markdown-body h4 .octicon-link,
.markdown-body h5 .octicon-link,
.markdown-body h6 .octicon-link {
color: #fff;
}

.markdown-body img {
background-color: transparent;
}

.ui-toc-dropdown .nav>.active:focus>a, .ui-toc-dropdown .nav>.active:hover>a, .ui-toc-dropdown .nav>.active>a {
color: white;
border-left: 2px solid white;
}

.expand-toggle:hover,
.expand-toggle:focus,
.back-to-top:hover,
.back-to-top:focus,
.go-to-bottom:hover,
.go-to-bottom:focus {
color: white;
}

.ui-toc-dropdown {
background-color: #333;
}

.ui-toc-label.btn {
background-color: #191919;
color: white;
}

.ui-toc-dropdown .nav>li>a:focus,
.ui-toc-dropdown .nav>li>a:hover {
color: white;
border-left: 1px solid white;
}

.markdown-body blockquote {
color: #bcbcbc;
}

.markdown-body table tr {
background-color: #5f5f5f;
}

.markdown-body table tr:nth-child(2n) {
background-color: #4f4f4f;
}

.markdown-body code,
.markdown-body tt {
color: #eee;
background-color: rgba(230, 230, 230, 0.36);
}

a,
.open-files-container li.selected a {
color: #5EB7E0;
}

</style>