Vlookup Text等6個excel函數合力算農歷(考慮閏月)

  excel中計算農歷的公式在網上可以查找,不少人也會寫:

(第1個函數出馬)

TEXT(日期,”[$-130000]yyyy-mm-dd”)

TEXT可以對單元格的值格式化處理,這裡[]裡的字符 $-130000 是計算農歷的關鍵,至於為什麼這樣寫,微軟也沒有更準確的說法,所以同學們也不必深究,死記就行了,如果你真的很想知道,百度一下就會有答案,不過也只是網友猜測。

只是這個公式就行了嗎?不行!因為農歷也有潤月情況,比如2014年閏九月,即有2個九月。下面是1949年~2020年的潤月表。

年份

潤月

1949

7

1952

5

1955

3

1957

8

1960

6

1963

4

1966

3

1968

7

1971

5

1974

4

1976

8

1979

6

1982

4

1984

10

1987

6

1990

5

1993

3

1995

8

1998

5

2001

4

2004

2

2006

7

2009

5

2012

4

2014

9

2017

6

2020

4

用text函數計算的農歷日期沒考慮到閏月,怎麼把閏月因素也加上呢?

【例】如下圖所示,要求根據B2的陽歷在B3單元格中返回對應的農歷日期。

Vlookup Text等6個excel函數合力算農歷(考慮閏月)

原理應該是這樣的

用B2的年份 (第2個函數)

Year(B2)

從D和E列查找對應的閏月(第3個函數)

VLOOKUP(YEAR(B2),D:E,2,0)

查出出來後和B2的月份進行比較(第4個函數出現)

VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2)

如果閏月大於B2的日期,不需要對日期處理,否則需要處理,為了方便處理,在表達式前添加 – 號,TRUE遇到-變成 -1 ,FALSE變成0

-(VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2))

因為有的年份沒有閏月,VLOOKUP查不到會返回錯誤值,所以需要用IFERROR函數把錯誤值轉換為0 (第5個函數出馬)

IFERROR(-(VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2)),0)

最後如果是潤月後的日期,TEXT計算後的月份再 – 1 ,完成這個功能的函數是EDATE函數(第6個函數),最終的計算農歷的函數也已完成!

=EDATE(TEXT(B2,”[$-130000]yyyy-mm-dd”),IFERROR(-(VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2)),0))