EXCEL公历日期转农历日期方法.docx

EXCEL公历日期转农历日期方法方法1：使用EXCEL内置函数Text-返回日期的阿拉伯数字的农历年月日在A1单元格输入公历日期 如 2001-11-30在B1单元格 按照阿拉伯数字的日期格式显示转换后的农历日期， 在B1单元格输入转农历公式：=TEXT(A1,$-130000yyyy-mm-dd) 回车显示农历日期 2001-10-16 按照中文年月的日期格式显示转换后的农历日期，在B1单元格输入转农历公式：=TEXT(A1,$-130000yyyy年m月&IF(LEN(-TEXT(A1,$-130000dd)=1,初,)&d)回车显示农历日期 2001年10月16方法2：使用EXCEL自定义函数上面介绍的Text函数是Excel内置函数，如果要显示农历的天干地支等信息，用内置函数公式写得就会很长，这种情况下，可以使用自定义函数，在公式栏就可以简化输入。我们可以自定义函数Lunardate, 用以实现 在A1单元格输入公历日期 如 2001-11-30在B2输入公式：=Lunardate(A2)，回车显示农历日期 农历辛巳年(蛇)十月十六在Excel中设定自定义函数Lunardate的步骤：1. 按“Alt+F11”快捷键，出现如下图的空白窗，在框体 1处，粘贴Lunardate的程序（见下面的详细程序），粘贴完毕，点击右上角的2处十字叉关闭关闭框，再点击左上角的3处的Excel图标回到Excel的工作表。2. 要粘贴的程序如下：Public Function Lunardate(Optional XX_DATE As Date)Dim MonthAdd(11), LunardateData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), MonName(12)Dim curTime, curYear, curMonth, curDayDim GongliStr, LunardateStr, LunardateDayStrDim 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) = 0MonthAdd(1) = 31MonthAdd(2) = 59MonthAdd(3) = 90MonthAdd(4) = 120MonthAdd(5) = 151MonthAdd(6) = 181MonthAdd(7) = 212MonthAdd(8) = 243MonthAdd(9) = 273MonthAdd(10) = 304MonthAdd(11) = 334农历数据LunardateData(0) = 2635LunardateData(1) = 333387LunardateData(2) = 1701LunardateData(3) = 1748LunardateData(4) = 267701LunardateData(5) = 694LunardateData(6) = 2391LunardateData(7) = 133423LunardateData(8) = 1175LunardateData(9) = 396438LunardateData(10) = 3402LunardateData(11) = 3749LunardateData(12) = 331177LunardateData(13) = 1453LunardateData(14) = 694LunardateData(15) = 201326LunardateData(16) = 2350LunardateData(17) = 465197LunardateData(18) = 3221LunardateData(19) = 3402LunardateData(20) = 400202LunardateData(21) = 2901LunardateData(22) = 1386LunardateData(23) = 267611LunardateData(24) = 605LunardateData(25) = 2349LunardateData(26) = 137515LunardateData(27) = 2709LunardateData(28) = 464533LunardateData(29) = 1738LunardateData(30) = 2901LunardateData(31) = 330421LunardateData(32) = 1242LunardateData(33) = 2651LunardateData(34) = 199255LunardateData(35) = 1323LunardateData(36) = 529706LunardateData(37) = 3733LunardateData(38) = 1706LunardateData(39) = 398762LunardateData(40) = 2741LunardateData(41) = 1206LunardateData(42) = 267438LunardateData(43) = 2647LunardateData(44) = 1318LunardateData(45) = 204070LunardateData(46) = 3477LunardateData(47) = 461653LunardateData(48) = 1386LunardateData(49) = 2413LunardateData(50) = 330077LunardateData(51) = 1197LunardateData(52) = 2637LunardateData(53) = 268877LunardateData(54) = 3365LunardateData(55) = 531109LunardateData(56) = 2900LunardateData(57) = 2922LunardateData(58) = 398042LunardateData(59) = 2395LunardateData(60) = 1179LunardateData(61) = 267415LunardateData(62) = 2635LunardateData(63) = 661067LunardateData(64) = 1701LunardateData(65) = 1748LunardateData(66) = 398772LunardateData(67) = 2742LunardateData(68) = 2391LunardateData(69) = 330031LunardateData(70) = 1175LunardateData(71) = 1611LunardateData(72) = 200010LunardateData(73) = 3749LunardateData(74) = 527717LunardateData(75) = 1452LunardateData(76) = 2742LunardateData(77) = 332397LunardateData(78) = 2350LunardateData(79) = 3222LunardateData(80) = 268949LunardateData(81) = 3402LunardateData(82) = 3493LunardateData(83) = 133973LunardateData(84) = 1386LunardateData(85) = 464219LunardateData(86) = 605LunardateData(87) = 2349LunardateData(88) = 334123LunardateData(89) = 2709LunardateData(90) = 2890LunardateData(91) = 267946LunardateData(92) = 2773LunardateData(93) = 592565LunardateData(94) = 1210LunardateData(95) = 2651LunardateData(96) = 395863LunardateData(97) = 1323LunardateData(98) = 2707LunardateData(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 2) ThenTheDate = TheDate + 1End If计算农历天干、地支、月、日isEnd = 0m = 0DoIf (LunardateData(m) 4095) Thenk = 11Elsek = 12End Ifn = kDoIf (n 0) ThenExit DoEnd If获取LunardateData(m)的第n个二进制位的值bit = LunardateData(m)For i = 1 To n Step 1bit = Int(bit / 2)Nextbit = bit Mod 2If (TheDate (Int(LunardateData(m) / 65536) + 1) ThencurMonth = curMonth - 1End IfEnd If生成农历天干、地支、属相 = LunardateStrLunardateStr = 农历 & TianGan(curYear - 4) Mod 60) Mod 10) & DiZhi(curYear - 4) Mod 60) Mod 12) & 年LunardateStr = LunardateStr & ( & ShuXiang(curYear - 4) Mod 60) Mod 12) & )生成农历月、日 = LunardateDayStrIf (curMonth 1) ThenLunardateDayStr = 闰 & M