excel公历转农历的四种方法excel公历转农历方法一：使用text函数A列是公历日期，我们在B1单元格输入公式：=TEXT(A1,"[$-130000]yyyy年m月"&I F(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")，下拉完成公历转农历。

excel公历转农历方法二：使用text+MID函数A列仍然是公历，B1输入公式：=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-13 0000]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")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d"))excel公历转农历方法四：使用自定义函数上面介绍的excel公历转农历都是使用excel内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。

如下图所示，我们在B2输入公式：=nongli(A2)，即可。

C列农历简称中C2公式是：=RIGHT(B2,LEN(B2)-8)。

关于自定义函数的使用方法请参考：excel自定义函数添加和使用方法下面是此自定义函数相关的代码：Public Function NongLi(Optional XX_DATE As Date)Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(3 0), MonName(12)Dim curTime, curYear, curMonth, curDayDim GongliStr, NongliStr, NongliDayStrDim i, m, n, k, isEnd, bit, TheDate'获取当前系统时间curTime = XX_DATE'天干名称TianGan(0) = "甲"TianGan(1) = "乙"TianGan(2) = "丙"TianGan(3) = "丁"TianGan(4) = "戊"TianGan(5) = "己"TianGan(6) = "庚"TianGan(7) = "辛"TianGan(8) = "壬"TianGan(9) = "癸"'地支名称DiZhi(0) = "子"DiZhi(1) = "丑"DiZhi(2) = "寅"DiZhi(3) = "卯"DiZhi(4) = "辰"DiZhi(5) = "巳"DiZhi(6) = "午"DiZhi(7) = "未"DiZhi(8) = "申"DiZhi(9) = "酉"DiZhi(10) = "戌" DiZhi(11) = "亥"'属相名称ShuXiang(0) = "鼠" ShuXiang(1) = "牛" ShuXiang(2) = "虎" ShuXiang(3) = "兔" ShuXiang(4) = "龙" ShuXiang(5) = "蛇" ShuXiang(6) = "马" ShuXiang(7) = "羊" ShuXiang(8) = "猴" ShuXiang(9) = "鸡" ShuXiang(10) = "狗" ShuXiang(11) = "猪" '农历日期名DayName(0) = "*" DayName(1) = "初一" DayName(2) = "初二" DayName(3) = "初三" DayName(4) = "初四" DayName(5) = "初五" DayName(6) = "初六" DayName(7) = "初七" DayName(8) = "初八" DayName(9) = "初九" DayName(10) = "初十" DayName(11) = "十一" DayName(12) = "十二" DayName(13) = "十三" DayName(14) = "十四" DayName(15) = "十五" DayName(16) = "十六" DayName(17) = "十七" DayName(18) = "十八" DayName(19) = "十九" DayName(20) = "二十" DayName(21) = "廿一" DayName(22) = "廿二" DayName(23) = "廿三"DayName(24) = "廿四" DayName(25) = "廿五" DayName(26) = "廿六" DayName(27) = "廿七" DayName(28) = "廿八" DayName(29) = "廿九" DayName(30) = "三十" '农历月份名MonName(0) = "*" MonName(1) = "正" MonName(2) = "二" MonName(3) = "三" MonName(4) = "四" MonName(5) = "五" MonName(6) = "六" MonName(7) = "七" MonName(8) = "八" MonName(9) = "九" MonName(10) = "十" MonName(11) = "十一" MonName(12) = "腊" '公历每月前面的天数MonthAdd(0) = 0 MonthAdd(1) = 31 MonthAdd(2) = 59 MonthAdd(3) = 90 MonthAdd(4) = 120 MonthAdd(5) = 151 MonthAdd(6) = 181 MonthAdd(7) = 212 MonthAdd(8) = 243 MonthAdd(9) = 273 MonthAdd(10) = 304 MonthAdd(11) = 334'农历数据NongliData(0) = 2635 NongliData(1) = 333387 NongliData(2) = 1701 NongliData(3) = 1748 NongliData(4) = 267701 NongliData(5) = 694 NongliData(6) = 2391 NongliData(7) = 133423 NongliData(8) = 1175NongliData(10) = 3402 NongliData(11) = 3749 NongliData(12) = 331177 NongliData(13) = 1453 NongliData(14) = 694 NongliData(15) = 201326 NongliData(16) = 2350 NongliData(17) = 465197 NongliData(18) = 3221 NongliData(19) = 3402 NongliData(20) = 400202 NongliData(21) = 2901 NongliData(22) = 1386 NongliData(23) = 267611 NongliData(24) = 605 NongliData(25) = 2349 NongliData(26) = 137515 NongliData(27) = 2709 NongliData(28) = 464533 NongliData(29) = 1738 NongliData(30) = 2901 NongliData(31) = 330421 NongliData(32) = 1242 NongliData(33) = 2651 NongliData(34) = 199255 NongliData(35) = 1323 NongliData(36) = 529706 NongliData(37) = 3733 NongliData(38) = 1706 NongliData(39) = 398762 NongliData(40) = 2741 NongliData(41) = 1206 NongliData(42) = 267438 NongliData(43) = 2647 NongliData(44) = 1318 NongliData(45) = 204070 NongliData(46) = 3477 NongliData(47) = 461653 NongliData(48) = 1386 NongliData(49) = 2413 NongliData(50) = 330077 NongliData(51) = 1197 NongliData(52) = 2637NongliData(54) = 3365 NongliData(55) = 531109 NongliData(56) = 2900 NongliData(57) = 2922 NongliData(58) = 398042 NongliData(59) = 2395 NongliData(60) = 1179 NongliData(61) = 267415 NongliData(62) = 2635 NongliData(63) = 661067 NongliData(64) = 1701 NongliData(65) = 1748 NongliData(66) = 398772 NongliData(67) = 2742 NongliData(68) = 2391 NongliData(69) = 330031 NongliData(70) = 1175 NongliData(71) = 1611 NongliData(72) = 200010 NongliData(73) = 3749 NongliData(74) = 527717 NongliData(75) = 1452 NongliData(76) = 2742 NongliData(77) = 332397 NongliData(78) = 2350 NongliData(79) = 3222 NongliData(80) = 268949 NongliData(81) = 3402 NongliData(82) = 3493 NongliData(83) = 133973 NongliData(84) = 1386 NongliData(85) = 464219 NongliData(86) = 605 NongliData(87) = 2349 NongliData(88) = 334123 NongliData(89) = 2709 NongliData(90) = 2890 NongliData(91) = 267946 NongliData(92) = 2773 NongliData(93) = 592565 NongliData(94) = 1210 NongliData(95) = 2651 NongliData(96) = 395863NongliData(98) = 2707NongliData(99) = 265877'生成当前公历年、月、日==> GongliStrcurYear = Year(curTime)curMonth = Month(curTime)curDay = Day(curTime)GongliStr = curYear & "年"If (curMonth < 10) ThenGongliStr = GongliStr & "0" & curMonth & "月"ElseGongliStr = GongliStr & curMonth & "月"End IfIf (curDay < 10) ThenGongliStr = GongliStr & "0" & curDay & "日"ElseGongliStr = GongliStr & curDay & "日"End If'计算到初始时间1921年2月8日的天数：1921-2-8(正月初一)TheDate = (curYear - 1921) * 365 + Int((curYear - 1921) / 4) + curDay + Month Add(curMonth - 1) - 38If ((curYear Mod 4) = 0 And curMonth > 2) ThenTheDate = TheDate + 1End If'计算农历天干、地支、月、日isEnd = 0m = 0DoIf (NongliData(m) < 4095) Thenk = 11Elsek = 12End Ifn = kDoIf (n < 0) ThenExit DoEnd If'获取NongliData(m)的第n个二进制位的值bit = NongliData(m)For i = 1 To n Step 1bit = Int(bit / 2)Nextbit = bit Mod 2If (TheDate <= 29 + bit) ThenisEnd = 1Exit DoEnd IfTheDate = TheDate - 29 - bitn = n - 1LoopIf (isEnd = 1) ThenExit DoEnd Ifm = m + 1LoopcurYear = 1921 + mcurMonth = k - n + 1curDay = TheDateIf (k = 12) ThenIf (curMonth = (Int(NongliData(m) / 65536) + 1)) ThencurMonth = 1 - curMonthElseIf (curMonth > (Int(NongliData(m) / 65536) + 1)) ThencurMonth = curMonth - 1End IfEnd If'生成农历天干、地支、属相==> NongliStrNongliStr = "农历" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "年"NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ")"'生成农历月、日==> NongliDayStrIf (curMonth < 1) ThenNongliDayStr = "闰" & MonName(-1 * curMonth)ElseNongliDayStr = MonName(curMonth)End IfNongliDayStr = NongliDayStr & "月"NongliDayStr = NongliDayStr & DayName(curDay)NongLi = NongliStr & NongliDayStrEnd Function。