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

EXCEL公历日期转农历日期方法方法1:使用EXCEL内置函数Text---返回日期的阿拉伯数字的农历年月日在A1单元格输入公历日期 女口 2001-11-30在B1单元格•按照阿拉伯数字的日期格式显示转换后的农历日期，在 B1单元格输入转农历公式：=TEXT(A1,"[$-130000]yyyy-mm-dd")回车显示农历日期 2001-10-16•按照中文年月的日期格式显示转换后的农历日期，在 B1单元格输入转农历公式：=TEXT(A1,"[$-130000]yyyy 年 m月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=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 Fun ction Lun ardate(Optio nal XX_DATE As Date)Dim MonthAdd(11), LunardateData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), Mo nN ame(12)Dim curTime, curYear, curM on th, curDayDim Gon gliStr, Lun ardateStr, Lun ardateDayStrDim i, m, n, k, isE nd, bit, TheDate'获取当前系统时间curTime = XX_DATE'天干名称TianGan(O)="甲”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)="羊”ShuXia ng(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)n n= " 十六 "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) =,n —nMonName(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)= 304#MonthAdd(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)= 3365#LunardateData(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。