excel 公历转农历的四种方法，分别通过excel内置函数和自定义函数完成公历转农历

　尽管通过很多渠道可以轻松获取公历转农历，但是对excel迷们还是很期待用excel来实现公历转农历。

　　设想一下，IT部落窝会员们的信息都是以阳历注册的，我们也可以使用下面即将介绍的方法实现excel公历转农历。

excel公历转农历方法一：使用text函数

　　A列是公历日期，我们在B1单元格输入公式：=TEXT(A1,"[$-130000]yyyy年m月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")，下拉完成公历转农历。

excel公历转农历方法二：使用text+MID函数

　　A列仍然是公历，B1输入公式：=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-130000]e")-4,12)+1,1)&"年"&TEXT(A1,"[$-130000][DBNum1]m月d日"即可。

excel公历转农历方法三：使用text+MID+ CHOOSE+ YEAR函数

　　A1为公历，B1输入：=CHOOSE(MOD(YEAR(A1)-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(A1)-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")&TEXT(A1,"[dbnum1][$-130000]年m月"&IF(--TEXT(A1,"[$-130000]d") 2) Then     TheDate = TheDate + 1 End If '计算农历天干、地支、月、日 isEnd = 0 m = 0 Do     If (NongliData(m) < 4095) Then      k = 11     Else      k = 12     End If     n = k     Do     If (n < 0) Then         Exit Do     End If     '获取NongliData(m)的第n个二进制位的值     bit = NongliData(m)     For i = 1 To n Step 1      bit = Int(bit / 2)     Next     bit = bit Mod 2     If (TheDate (Int(NongliData(m) / 65536) + 1)) Then        curMonth = curMonth - 1     End If End If '生成农历天干、地支、属相 ==> NongliStr NongliStr = "农历" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "年" NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ")" '生成农历月、日 ==> NongliDayStr If (curMonth < 1) Then     NongliDayStr = "闰" & MonName(-1 * curMonth) Else     NongliDayStr = MonName(curMonth) End If NongliDayStr = NongliDayStr & "月" NongliDayStr = NongliDayStr & DayName(curDay)

NongLi = NongliStr & NongliDayStr

End Function