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

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

excel公历转农历方法一：使用text函数Eqfoffice教程网

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

Eqfoffice教程网

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

　　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日"即可。Eqfoffice教程网

Eqfoffice教程网

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

　　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) ThenEqfoffice教程网    TheDate = TheDate + 1Eqfoffice教程网End IfEqfoffice教程网'计算农历天干、地支、月、日Eqfoffice教程网isEnd = 0Eqfoffice教程网m = 0Eqfoffice教程网DoEqfoffice教程网    If (NongliData(m) < 4095) ThenEqfoffice教程网     k = 11Eqfoffice教程网    ElseEqfoffice教程网     k = 12Eqfoffice教程网    End IfEqfoffice教程网    n = kEqfoffice教程网    DoEqfoffice教程网    If (n < 0) ThenEqfoffice教程网        Exit DoEqfoffice教程网    End IfEqfoffice教程网    '获取NongliData(m)的第n个二进制位的值Eqfoffice教程网    bit = NongliData(m)Eqfoffice教程网    For i = 1 To n Step 1Eqfoffice教程网     bit = Int(bit / 2)Eqfoffice教程网    NextEqfoffice教程网    bit = bit Mod 2Eqfoffice教程网    If (TheDate (Int(NongliData(m) / 65536) + 1)) ThenEqfoffice教程网       curMonth = curMonth - 1Eqfoffice教程网    End IfEqfoffice教程网End IfEqfoffice教程网'生成农历天干、地支、属相 ==> NongliStrEqfoffice教程网NongliStr = "农历" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "年"Eqfoffice教程网NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ")"Eqfoffice教程网'生成农历月、日 ==> NongliDayStrEqfoffice教程网If (curMonth < 1) ThenEqfoffice教程网    NongliDayStr = "闰" & MonName(-1 * curMonth)Eqfoffice教程网ElseEqfoffice教程网    NongliDayStr = MonName(curMonth)Eqfoffice教程网End IfEqfoffice教程网NongliDayStr = NongliDayStr & "月"Eqfoffice教程网NongliDayStr = NongliDayStr & DayName(curDay)Eqfoffice教程网

NongLi = NongliStr & NongliDayStrEqfoffice教程网

End FunctionEqfoffice教程网