Excel教程：公歷日期轉農歷的6個公式

有小伙伴在后臺留言詢問，公歷日期如何轉為農歷日期。

比如，下面的Excel表格，將B列的日期，轉成對應的農歷。

C2單元格輸入公式：=TEXT(B2,"[$-130000]yyyy-m-d")，下拉即可。

公式解釋：

B2：表示要轉化的公歷日期；

yyyy-m-d：表示輸入日期的格式；

[$-130000]：表示公歷轉陰歷的特殊參數(shù)。

除了上面的公式寫法，再給大家發(fā)幾個不同的格式，對應的函數(shù)公式肯定也是不一樣的哈?？梢允詹貍溆谩?br>

D列公式：

=IF(--TEXT(B2,"[$-130000]d")<11,TEXT(B2,"[$-130000][dbnum1]m月初d"),SUBSTITUTE(TEXT(B2,"[$-130000][dbnum1]m月d"),IF(--TEXT(B2,"[$-130000]d")>20,"二十",""),"廿"))

E列公式：

=TEXT(B2,"[$-130000]yyyy年m月"&IF(LEN(--TEXT(B2,"[$-130000]dd"))=1,"初","")&"d")

F列公式：

=TEXT(B2," [$-130000][DBNum1]m月d日")

G列公式：

=MID(" 甲乙丙丁戊己庚辛壬癸",MOD(TEXT(B2,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(B2,"[$-130000]e")-4,12)+1,1)&"年"&IF(TEXT(B2," [$-130000]m")*1=12," 臘",IF(TEXT(B2," [$-130000]m")*1=1," 正",TEXT(B2," [$-130000][DBNum1]m")))&"月"&IF(TEXT(B2," [$-130000]d")-9<=1,"初",IF(TEXT(B2," [$-130000]d")-29>=1,"卅",IF(TEXT(B2," [$-130000]d")-19>=1,"廿","十")))&IF(RIGHT(TEXT(B2," [$-130000]d"),1)*1=0,"",TEXT(RIGHT(TEXT(B2," [$-130000]d"),1)*1,"[DBNum1]d"))&"日"

H列公式：

=IF(TEXT(B2," [$-130000]m")*1=12," 臘",IF(TEXT(B2," [$-130000]m")*1=1," 正",TEXT(B2," [$-130000][DBNum1]m")))&"月"&IF(TEXT(B2," [$-130000]d")-9<=1,"初",IF(TEXT(B2," [$-130000]d")-29>=1,"卅",IF(TEXT(B2," [$-130000]d")-19>=1,"廿","十")))&IF(RIGHT(TEXT(B2," [$-130000]d"),1)*1=0,"",TEXT(RIGHT(TEXT(B2," [$-130000]d"),1)*1,"[DBNum1]d"))&"日"

本站僅提供存儲服務，所有內容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權內容，請點擊舉報。

开心六月综合激情婷婷|欧美精品成人动漫二区|国产中文字幕综合色|亚洲人在线成视频