79.

Dear All,

自動計算年齡、民國日期換算、日期格式設計教學。

而我最近呢!?最近從稽核員轉換成專管工作了,然後發現住民資料沒有做好管理,所以我幫姊姊設計「住民個人資料管理」的Excel表和公式。

會使用的函數:

  • ◆ 入住、退住換算、生日換算: TEXTDATEMID 函數
  • ◆ 年紀: DATEDIFNOW 函數
  • ◆ 共住天數: IFERROR 函數
  • ◆ 累積天數: IFNOT 函數

完成圖如下:

image

 

  介紹【TEXT】函數  

此函數之前有寫過,請閱讀如下:

68. Excel 教學_將日期數字 轉成 中文大寫 (TEXT)

  介紹【DATE】函數  

此函數之前有寫過,請閱讀如下:

52. EXCEL教學_自動 民國轉西元年, 西元轉民國年 (TEXT,DATE,MID)

  介紹【MID】函數  

此函數之前有寫過,請閱讀如下:

7. EXCEL 教學:擷取、合併文字、分割 不用自己KeyLEFT / RIGHT / CONCATENATE (&)/MID

 

  入住、退住換算、生日換算綜合應用  

image

首先,必須先告訴各位讀者一件事情,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")

我們先個別來看DATETEXT函數,首先是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/32018/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 自動倒數離總統大選、工作截止日天數│附練習檔

 

  年紀換算應用  

image

 

年紀主要換算方式,是用現在的時間減去生日的時間,然後只擷取年份。

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

 
 
延伸閱讀:

 

 
 

    愛偷懶先生 發表在 痞客邦 留言(0) 人氣()