Dear All,
自動計算年齡、民國日期換算、日期格式…設計教學。
而我最近呢!?最近從稽核員轉換成專管工作了，然後發現住民資料沒有做好管理，所以我幫姊姊設計「住民個人資料管理」的Excel表和公式。
會使用的函數:
- ◆ 入住、退住換算、生日換算: TEXT、DATE、MID 函數
- ◆ 年紀: DATEDIF、NOW 函數
- ◆ 共住天數: IFERROR 函數
- ◆ 累積天數: IF、NOT 函數
完成圖如下:
介紹【TEXT】函數
此函數之前有寫過，請閱讀如下:
68. Excel 教學_將日期數字 轉成 中文大寫 (TEXT)
介紹【DATE】函數
此函數之前有寫過，請閱讀如下:
52. EXCEL教學_自動 民國轉西元年, 西元轉民國年 (TEXT,DATE,MID)
介紹【MID】函數
此函數之前有寫過，請閱讀如下:
7. EXCEL 教學：擷取、合併文字、分割 不用自己Key｜LEFT / RIGHT / CONCATENATE (&)/MID
入住、退住換算、生日換算綜合應用
首先，必須先告訴各位讀者一件事情，Excel日期格式轉換必須使用「/ 斜線」做為區隔，不能使用「. 點號」做為區隔。因此，使用TEXT函數就要用來轉換成正確的日期格式:
|
原始格式 |
Excel日期格式 |
|
107.09.01 |
2018/09/01 |
|
107.09.03 |
2018/09/03 |
|
107.09.17 |
2018/09/17 |
轉換日期公式: D2儲存格=TEXT(DATE(MID(C2,1,3)+1911,MID(C2,5,2),MID(C2,8,2)),"yyyy/mm/dd")
我們先個別來看DATE、TEXT函數，首先是DATE函數:
|
DATE(MID(C2,1,3)+1911,MID(C2,5,2),MID(C2,8,2)) |
||
|
DATE |
將日期轉換成yyyy/mm/dd |
|
|
年 |
MID(C2,1,3)+1911 |
C2儲存格第1個字開始共3字，並加數字1911 |
|
月 |
MID(C2,5,2) |
C2儲存格第5個字開始共2字 |
|
日 |
MID(C2,8,2) |
C2儲存格第8個字開始共2字 |
|
結果 |
2018/9/3 |
|
透過DATE函數所得的日期會忽略不必要的日期格式，
如: 2018/9/3；2018/09/03。因此，必須透過TEXT函數調整成統一格式:
|
TEXT(DATE(MID(C2,1,3)+1911,MID(C2,5,2),MID(C2,8,2)),"yyyy/mm/dd") |
|
|
TEXT |
將 DATE結果轉換成"yyyy/mm/dd"格式 |
|
結果 |
2018/09/03 |
這樣子，就完成 Excel 統一可供轉換轉換天數的日期格式。
介紹【DATEDIF】函數
此函數之前有寫過，請閱讀如下:
1. EXCEL 教學：自動計算員工年齡｜TODAY / DATEDIF / $應用｜
介紹【NOW】函數
此函數之前有寫過，請閱讀如下:
32. EXCEL: NOW(),DAYS360 自動倒數離總統大選、工作截止日天數│附練習檔
年紀換算應用
年紀主要換算方式，是用現在的時間減去生日的時間，然後只擷取年份。
NOW函數: K2儲存格 =NOW()，就能得到現在日期、時間。
DATEDIF函數:
|
B2儲存格 =DATEDIF(J2,$K$1,"Y") |
|
|
DATEDIF |
從J2日期起算，到K2的日期，共計幾年 |
|
J2 |
1973/02/06 |
|
K2 |
NOW函數(2020/10/24) |
|
Y |
單位:年 (Y) |
|
結果 |
47 |
留言列表
SEO 教學 (2)