excel公历转农历的四种方法
公历转农历EXCEL宏讲解
Public Function NongLi(Optional XX_DATE As Date)Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), 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) = 1175 NongliData(9) = 396438 NongliData(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) = 1386NongliData(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) = 2637 NongliData(53) = 268877 NongliData(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) = 398772NongliData(68) = 2391NongliData(69) = 330031NongliData(70) = 1175NongliData(71) = 1611NongliData(72) = 200010NongliData(73) = 3749NongliData(74) = 527717NongliData(75) = 1452NongliData(76) = 2742NongliData(77) = 332397NongliData(78) = 2350NongliData(79) = 3222NongliData(80) = 268949NongliData(81) = 3402NongliData(82) = 3493NongliData(83) = 133973NongliData(84) = 1386NongliData(85) = 464219NongliData(86) = 605NongliData(87) = 2349NongliData(88) = 334123NongliData(89) = 2709NongliData(90) = 2890NongliData(91) = 267946NongliData(92) = 2773NongliData(93) = 592565NongliData(94) = 1210NongliData(95) = 2651NongliData(96) = 395863NongliData(97) = 1323NongliData(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 + MonthAdd(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。
【公历年份换算农历干支纪年口诀】（含公元前后两种换算方法公式）简单、实用、易学、好记
【公历年份换算农历干支纪年口诀】（含公元前后两种换算方法公式）简单、实用、易学、好记【公历年份换算农历干支纪年口诀】干支纪年法是中国历法上自古以来就一直使用的纪年方法，采用的是阴阳合历。
干支是天干和地支的总称。
把干支顺序相配正好六十为一周，周而复始，循环记录，这就是俗称的“干支表”。
干支纪年以每年春节换年。
而在历史教学中记录一些事件的发生时间都是采用公元纪年，公元纪年和干支纪年有一个简单的换算口诀如下：一，用数字对应天干地支：自然数： 1 2 3 4 5 6 7 8 9 0天干：甲乙丙丁戊己庚辛壬癸自然数： 1 2 3 4 5 6 7 8 9 10 11 0地支：子丑寅卯辰巳午未申酉戌亥二，举例（以2018为例）：公元年数减去3——2018－3＝201510除余數是天干——2015÷10=201……5（戊）该数再用12除——2015÷12=167……11（戌）余数就把地支显所以2018年按干支纪年就是戊戌年。
以上是公历公元年份换算成农历干支纪年的计算方法。
下面姜毅同志研究发现了另一段年份的换算方法，即：公历公元前年份换算成农历干支纪年的计算方法：一，用數字對應天干地支：0 9 8 7 6 5 4 3 2 1天干：甲乙丙丁戊己庚辛壬癸0 1110 9 8 7 6 5 4 3 2 1地支：子丑寅卯辰巳午未申酉戍亥二，口訣並舉例（以公元前57年為例）：公元前年數加3：57＋3=601.60除10的余數是天干：60÷10=6……0（0对应的天干是甲）；还有一种更简单明了的方法，就是年数加3后的和不用作除10计算，直接用和的个位数与天干对应即可。
2.60除12的余数是地支：60÷12=5……0（0对应的地支是子）余数就把地支显所以公元前57年换算成农历干支纪年就是甲子年。
又如：将公元前1250年换算成干支年：1250＋3＝1253① 和的个位3对应的天干为辛②1253÷12＝104 (5)余数5对应的地支为未故公元前1250年为辛未年。
excel制作周历 农历阳历转换
步骤一，在Excel工作表界面下按<ALT+F11>组合键打开VBA窗口，进入VBA编辑环境，在其窗口的菜单栏上依次单击“插入”→“模块”，可在当前VBA工程中插入模块，并直接进入此模块的代码编辑区域。
可在此编辑区域中输入自定义函数的程序代码,如图：Public Function NongLi(Optional XX_DATE As Date)Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), 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) = 333387NongliData(3) = 1748 NongliData(4) = 267701 NongliData(5) = 694 NongliData(6) = 2391 NongliData(7) = 133423 NongliData(8) = 1175 NongliData(9) = 396438 NongliData(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) = 204070NongliData(47) = 461653 NongliData(48) = 1386 NongliData(49) = 2413 NongliData(50) = 330077 NongliData(51) = 1197 NongliData(52) = 2637 NongliData(53) = 268877 NongliData(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) = 2709NongliData(91) = 267946NongliData(92) = 2773NongliData(93) = 592565NongliData(94) = 1210NongliData(95) = 2651NongliData(96) = 395863NongliData(97) = 1323NongliData(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 + MonthAdd(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步骤2，单击窗口右上角的“关闭”按钮关闭VBA编辑窗口，返回到工作表窗口中。
excel公历转农历的四种方法
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内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。
根据公历日期计算农历年生肖及公历转换农历的方法
日期农历月份[$-130000]格式验证1[$-130000]格式验证2TEXT(A2,"[$-130000]m-d")=1898+MATCH(要转换的公历日期,{0;31;416;770;1125;1508;1862;2217;2601;2955;3310;3694;4048;4432;4786;5140;5524;5878;6233;6617;6972;7356;7710;8064;8448;8802;9156;9541;9895;10250;10634;10988;11371;11725;12080;12464;12819;13173;13557;13911;14295;14649;15003;15387;15742;16096;16481;16835;17189;17573;17927;18311;18665;19020;19404;19758;20113;20497;20851;21234;21589;21943;22327;22682;23036;23420;23775;24128;21900-1-31鼠年春节1899FALSE13-16根据阳历生日1901-2-19牛年春节1901TRUE1-161902-2-8虎年春节1902TRUE1-171903-1-29兔年春节1902FALSE13-181904-2-16龙年春节1904TRUE1-171905-2-4蛇年春节1904FALSE13-171906-1-25马年春节1905FALSE12-161907-2-13羊年春节1907TRUE1-171908-2-2猴年春节1907FALSE13-161909-1-22鸡年春节1908FALSE12-171910-2-10狗年春节1910TRUE1-181911-1-30猪年春节1910FALSE13-181912-2-18鼠年春节1912TRUE1-181914-1-26虎年春节1913FALSE13-16 1915-2-14兔年春节1915TRUE1-17 1916-2-3龙年春节1915FALSE13-16 1917-1-23蛇年春节1916FALSE12-17 1918-2-11马年春节1918TRUE1-17 1919-2-1羊年春节1918FALSE13-18 1920-2-20猴年春节1920TRUE1-18 1921-2-8鸡年春节1921TRUE1-17 1922-1-28狗年春节1921FALSE13-16 1923-2-16猪年春节1923TRUE1-17 1924-2-5鼠年春节1923FALSE13-17 1925-1-24牛年春节1924FALSE12-16 1926-2-13虎年春节1926TRUE1-17 1927-2-2兔年春节1926FALSE13-17 1928-1-23龙年春节1927FALSE12-18 1929-2-10蛇年春节1929TRUE1-17 1930-1-30马年春节1929FALSE13-17 1931-2-17羊年春节1931TRUE1-17 1932-2-6猴年春节1932TRUE1-16 1933-1-26鸡年春节1932FALSE13-17 1934-2-14狗年春节1934TRUE1-17 1935-2-4猪年春节1934FALSE13-17 1936-1-24鼠年春节1935FALSE12-17 1937-2-11牛年春节1937TRUE1-16 1938-1-31虎年春节1937FALSE13-16 1939-2-19兔年春节1939TRUE1-17 1940-2-8龙年春节1940TRUE1-17 1941-1-27蛇年春节1940FALSE13-16 1942-2-15马年春节1942TRUE1-16 1943-2-5羊年春节1942FALSE13-17 1944-1-25猴年春节1943FALSE12-16 1945-2-13鸡年春节1945TRUE1-17 1946-2-2狗年春节1945FALSE13-17 1947-1-22猪年春节1946FALSE12-18 1948-2-10鼠年春节1948TRUE1-18 1949-1-29牛年春节1948FALSE13-17 1950-2-17虎年春节1950TRUE1-17 1951-2-6兔年春节1951TRUE1-16 1952-1-27龙年春节1951FALSE13-17 1953-2-14蛇年春节1953TRUE1-16 1954-2-3马年春节1953FALSE13-16 1955-1-24羊年春节1954FALSE12-18 1956-2-12猴年春节1956TRUE1-18 1957-1-31鸡年春节1956FALSE13-18 1958-2-18狗年春节1958TRUE1-17 1959-2-8猪年春节1959TRUE1-17 1960-1-28鼠年春节1960TRUE1-1 1961-2-15牛年春节1961TRUE1-1 1962-2-5虎年春节1962TRUE1-1 1963-1-25兔年春节1963TRUE1-1 1964-2-13龙年春节1964TRUE1-11966-1-21马年春节1965FALSE12-30 1967-2-9羊年春节1967TRUE1-1 1968-1-30猴年春节1968TRUE1-1 1969-2-17鸡年春节1969TRUE1-1 1970-2-6狗年春节1970TRUE1-1 1971-1-27猪年春节1971TRUE1-1 1972-2-15鼠年春节1972TRUE1-1 1973-2-3牛年春节1973TRUE1-1 1974-1-23虎年春节1974TRUE1-1 1975-2-11兔年春节1975TRUE1-1 1976-1-31龙年春节1976TRUE1-1 1977-2-18蛇年春节1977TRUE1-1 1978-2-7马年春节1978TRUE1-1 1979-1-28羊年春节1979TRUE1-1 1980-2-16猴年春节1980TRUE1-1 1981-2-5鸡年春节1981TRUE1-1 1982-1-25狗年春节1982TRUE1-1 1983-2-13猪年春节1983TRUE1-1 1984-2-2鼠年春节1984TRUE1-1 1985-2-20牛年春节1985TRUE1-1 1986-2-9虎年春节1986TRUE1-1 1987-1-29兔年春节1987TRUE1-1 1988-2-17龙年春节1987FALSE13-30 1989-2-6蛇年春节1989TRUE1-1 1990-1-27马年春节1990TRUE1-1 1991-2-15羊年春节1991TRUE1-1 1992-2-4猴年春节1992TRUE1-1 1993-1-23鸡年春节1993TRUE1-1 1994-2-10狗年春节1994TRUE1-1 1995-1-31猪年春节1995TRUE1-1 1996-2-19鼠年春节1996TRUE1-1 1997-2-7牛年春节1996FALSE12-30 1998-1-28虎年春节1998TRUE1-1 1999-2-16兔年春节1999TRUE1-1 2000-2-5龙年春节2000TRUE1-1 2001-1-24蛇年春节2001TRUE1-1 2002-2-12马年春节2002TRUE1-1 2003-2-1羊年春节2003TRUE1-1 2004-1-22猴年春节2004TRUE1-1 2005-2-9鸡年春节2005TRUE1-1 2006-1-29狗年春节2006TRUE1-1 2007-2-18猪年春节2007TRUE1-1 2008-2-7鼠年春节2008TRUE1-1 2009-1-26牛年春节2009TRUE1-1 2010-2-14虎年春节2010TRUE1-1 2011-2-3兔年春节2011TRUE1-1 2012-1-23龙年春节2012TRUE1-1 2013-2-10蛇年春节2013TRUE1-1 2014-1-31马年春节2014TRUE1-1 2015-2-19羊年春节2015TRUE1-1 2016-2-8猴年春节2016TRUE1-12018-2-16狗年春节2018TRUE1-1 2019-2-5猪年春节2019TRUE1-1 2020-1-25鼠年春节2020TRUE1-1 2021-2-12牛年春节2021TRUE1-1 2022-2-1虎年春节2022TRUE1-1 2023-1-22兔年春节2023TRUE1-1 2024-2-10龙年春节2024TRUE1-1 2025-1-29蛇年春节2025TRUE1-1 2026-2-17马年春节2026TRUE1-1 2027-2-6羊年春节2026FALSE12-30 2028-1-26猴年春节2027FALSE12-30 2029-2-13鸡年春节2029TRUE1-1 2030-2-3狗年春节2030TRUE1-1 2031-1-23猪年春节2031TRUE1-1 2032-2-11鼠年春节2032TRUE1-1 2033-1-31牛年春节2033TRUE1-1 2034-2-19虎年春节2034TRUE1-1 2035-2-8兔年春节2035TRUE1-1 2036-1-28龙年春节2036TRUE1-1 2037-2-15蛇年春节2037TRUE1-1 2038-2-4马年春节2038TRUE1-1 2039-1-24羊年春节2039TRUE1-1 2040-2-12猴年春节2040TRUE1-1 2041-2-1鸡年春节2041TRUE1-1 2042-1-22狗年春节2042TRUE1-1 2043-2-10猪年春节2043TRUE1-1 2044-1-30鼠年春节2044TRUE1-1 2045-2-17牛年春节2045TRUE1-1 2046-2-6虎年春节2046TRUE1-1 2047-1-26兔年春节2047TRUE1-1 2048-2-14龙年春节2048TRUE1-1 2049-2-2蛇年春节2049TRUE1-1 2050-1-23马年春节2050TRUE1-1 2051-2-11羊年春节2051TRUE1-15 2052-2-1猴年春节2051FALSE13-15 2053-2-19鸡年春节2053TRUE1-15 2054-2-8狗年春节2054TRUE1-15 2055-1-28猪年春节2054FALSE13-14 2056-2-15鼠年春节2056TRUE1-14 2057-2-4牛年春节2057TRUE1-14 2058-1-24虎年春节2057FALSE13-14 2059-2-12兔年春节2059TRUE1-14 2060-2-2龙年春节2059FALSE13-15 2061-1-21蛇年春节2060FALSE12-15 2062-2-9马年春节2062TRUE1-14 2063-1-29羊年春节2062FALSE13-14 2064-2-17猴年春节2064TRUE1-15 2065-2-5鸡年春节2065TRUE1-14 2066-1-26狗年春节2065FALSE13-15 2067-2-14猪年春节2067TRUE1-15 2068-2-3鼠年春节2067FALSE13-152070-2-11虎年春节2070TRUE1-15 2071-1-31兔年春节2070FALSE13-14 2072-2-19龙年春节2072TRUE1-15 2073-2-7蛇年春节2073TRUE1-14 2074-1-27马年春节2073FALSE13-14 2075-2-15羊年春节2075TRUE1-14 2076-2-5猴年春节2075FALSE13-15 2077-1-24鸡年春节2076FALSE12-16 2078-2-12狗年春节2078TRUE1-15 2079-2-2猪年春节2078FALSE13-15 2080-1-22鼠年春节2079FALSE12-14 2081-2-9牛年春节2081TRUE1-14 2082-1-29虎年春节2081FALSE13-14 2083-2-17兔年春节2083TRUE1-14 2084-2-6龙年春节2084TRUE1-15 2085-1-26蛇年春节2084FALSE13-16 2086-2-14马年春节2086TRUE1-15 2087-2-3羊年春节2086FALSE13-15 2088-1-24猴年春节2087FALSE12-15 2089-2-10鸡年春节2089TRUE1-14 2090-1-30狗年春节2089FALSE13-13 2091-2-18猪年春节2091TRUE1-14 2092-2-7鼠年春节2092TRUE1-15 2093-1-27牛年春节2092FALSE13-16 2094-2-15虎年春节2094TRUE1-15 2095-2-5兔年春节2095TRUE1-15 2096-1-25龙年春节2095FALSE13-15 2097-2-12蛇年春节2097TRUE1-15 2098-2-1马年春节2097FALSE13-14 2099-1-21羊年春节2098FALSE12-14 2100-2-9猴年春节2100TRUE1-15农历日期顺序初一初二初三初四初五初六初七初八初九初十十一十二十三十五十六十七十八十九二十廿一廿二廿三廿四廿五廿六廿七廿八廿九三十。
Excel实现公历农历转换的三种方法
Excel实现公历农历转换的三种⽅法【导读】:有时候我们在输⼊⽇期数据的时候，往往需要输⼊农历的⽇期，但已经输⼊的⽇期是公历的⽇期，那么该怎么转换呢？接下来我们将为⼤家如何利⽤excel函数进⾏将公历⽇期转化为农历⽇期！⼀、简单版下表中B2单元格就是第⼀种情况，“2016-9-17”表⽰农历是九⽉⼗七⽇。
该公式是：=TEXT(A2,"[$-130000]YYYY-M-D")。
TEXT()⽤来转化⽂本格式，这个公式的关键是：[$-130000]，它是Excel中阳历转化农历的参数，不过它存在⼀个问题就是没法计算闰⽉，估计⽼外⽆法理解中国⼈闰⽉的概念，凡是闰年，它直接表⽰⼀年13个⽉，道理是⼀样的。
⼆、进阶版（中⽂呈现）上表C2单元格，相⽐较B2单元格呈现就有了进阶，以中⽂呈现，并且以天⼲地⽀表现年份。
公式相对之前肯定复杂：=MID(" 甲⼄丙丁戊⼰庚⾟壬癸",MOD(TEXT(A2,"[$-130000]e")-4,10)+1,1)&MID("⼦丑寅卯⾠巳午未申⾣戌亥",MOD(TEXT(A2,"[$-130000]e")-4,12)+1,1)&"年"&TEXT(A2," [$-130000] [DBNum1]m⽉d⽇")我们可以分解为两⼤部分：1）计算年份：MID(" 甲⼄丙丁戊⼰庚⾟壬癸",MOD(TEXT(A2,"[$-130000]e")-4,10)+1,1)&MID("⼦丑寅卯⾠巳午未申⾣戌亥",MOD(TEXT(A2,"[$-130000]e")-4,12)+1,1)&"年"，这⾥加了MID()⽂本提取函数来获取天⼲地⽀。
EXCEL VBA 实例：将日期转换成农历
Ehi(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) = "二十八" — 第 2 页 共 7 页 —
在Excel中自定义公历转换农历函数2
#NAME? #NAME? #NAME?
阳历生日:阳历1975年5月6日出生,今年阳历生日时对应的阳历日期
#NAME?
End Type
Do
Loop
Loop
&HB4A43, &H4B5537 , &HAD54A, &H955ABF , &H4BA53, &HA5B48, &H652BBC , &H52B50, &HA9345, &H474AB9 ,_
&H6AA4C, &HAD541, &H24DAB6 , &H4B64A, &H69573D , &HA4E51, &HD2646, &H5E933A , &HD534D, &H5AA43, _
LunarDat a.leapmo nth = Int(ng / d)
ng = ng Mod d
d= &H80
mdata = Int(ng / d)
ng = ng Mod d
d= &H20
LunarDat a.sp_mon th = Int(ng / d)
LunarDat a.sp_day = ng Mod d
y= a.Month( 13)
Else
l_month = l_month +1
y= a.Month( l_month)
End If Loop
l_day = l_day + 1
lunar = l_year & "-" & l_month & "-" & l_day
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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)。
关于自定义函数的使用方法请参考：下面是此自定义函数相关的代码：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。