《excel公历日期转农历日期方法》由会员分享，可在线阅读，更多相关《excel公历日期转农历日期方法（7页珍藏版）》请在金锄头文库上搜索。
1、1EXCEL 公历日期转农历日期方法方法 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 年
2、 10 月 16方法 2：使用 EXCEL 自定义函数上面介绍的 Text 函数是 Excel 内置函数，如果要显示农历的天干地支等信息，用内置函数公式写得就会很长，这种情况下，可以使用自定义函数，在公式栏就可以简化输入。我们可以自定义函数 Lunardate, 用以实现 在 A1 单元格输入公历日期 如 2001-11-30在 B2 输入公式：=Lunardate(A2) ，回车显示农历日期 农历辛巳年 (蛇)十月十六在 Excel 中设定自定义函数 Lunardate 的步骤：1. 按“Alt+F11”快捷键，出现如下图的空白窗，在框体 1 处，粘贴 Lunardate 的程序（见下面的详
3、细程序），粘贴完毕，点击右上角的 2 处十字叉关闭关闭框，再点击左上角的 3 处的 Excel 图标回到 Excel 的工作表。2. 要粘贴的程序如下：2Public 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, Lunarda
4、teDayStrDim 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) = 午
5、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) = 初二Day
6、Name(3) = 初三DayName(4) = 初四3DayName(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) = 廿一DayNa
7、me(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) = 十M
8、onName(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) = 3044MonthAdd(11) = 334农历数据LunardateData(0) = 2635LunardateData(1) = 333387Lunarda
9、teData(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) =
10、694LunardateData(15) = 201326LunardateData(16) = 2350LunardateData(17) = 465197LunardateData(18) = 3221LunardateData(19) = 3402LunardateData(20) = 400202LunardateData(21) = 2901LunardateData(22) = 1386LunardateData(23) = 267611LunardateData(24) = 605LunardateData(25) = 2349LunardateData(26) = 137515
11、LunardateData(27) = 2709LunardateData(28) = 464533LunardateData(29) = 1738LunardateData(30) = 2901LunardateData(31) = 330421LunardateData(32) = 1242LunardateData(33) = 2651LunardateData(34) = 199255LunardateData(35) = 1323LunardateData(36) = 529706LunardateData(37) = 3733LunardateData(38) = 1706Luna
12、rdateData(39) = 398762LunardateData(40) = 2741LunardateData(41) = 1206LunardateData(42) = 267438LunardateData(43) = 2647LunardateData(44) = 1318LunardateData(45) = 204070LunardateData(46) = 3477LunardateData(47) = 461653LunardateData(48) = 1386LunardateData(49) = 2413LunardateData(50) = 330077Lunard
13、ateData(51) = 1197LunardateData(52) = 2637LunardateData(53) = 268877LunardateData(54) = 33655LunardateData(55) = 531109LunardateData(56) = 2900LunardateData(57) = 2922LunardateData(58) = 398042LunardateData(59) = 2395LunardateData(60) = 1179LunardateData(61) = 267415LunardateData(62) = 2635Lunardate
14、Data(63) = 661067LunardateData(64) = 1701LunardateData(65) = 1748LunardateData(66) = 398772LunardateData(67) = 2742LunardateData(68) = 2391LunardateData(69) = 330031LunardateData(70) = 1175LunardateData(71) = 1611LunardateData(72) = 200010LunardateData(73) = 3749LunardateData(74) = 527717LunardateDa
15、ta(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) = 2707LunardateDat
