《excel公历转农历的四种方法》由会员分享，可在线阅读，更多相关《excel公历转农历的四种方法（7页珍藏版）》请在金锄头文库上搜索。
1、excel公历转农历的四种方法excel公历转农历方法一：使用 text函数A列是公历日期，我们在 B1单元格输入公式：=TEXT(A1,$-130000yyyy 年m月&IF(LEN(-TEXT(A1,”$-130000dd)=1, 初,)&d),下拉完成公历转农历。A =TEXT(A【T部落窝娜- 万 ，1 | 2012-2-1 2012年1月 10二22012-1-29屹012年 1 月初？excel公历转农历方法二：使用 text+MID 函数A列仍然是公历，B1输入公式：=MID(甲乙丙丁戊己庚辛壬癸 ,MOD(TEXT(A1,$-13 0000e)-4,10)+1,1)&MID(子
2、丑寅卯辰巳午未申酉戌亥”,MOD(TEXT(A1,”$-130000e)-4,12)+1,1)&年&TEXT(A1,”$-130000DBNum1m 月 d 曰”即可。blwb=MID(甲乙iTEXT(Al,”IT部落窗论坛广-B .1 1 2012-2-I壬辰年一月十日!220127-2(工辰年一月七日1excel公历转农历方法三：使用 text+MID+ CHOOSE+ YEAR函数A1 为公历，B1 输入：=CHOOSE(MOD(YEAR(A1)-1900,10)+1,庚,辛，壬,癸,甲,乙,丙,丁,戊,己)&CHOOSE(MOD(YEAR(A1)-1900,12)+1,子,丑,寅,卯,
3、辰,巳,午，未,申，酉,戌,亥)&TEXT(A1,dbnum1$-130000年 m 月&IF(-TEXT(A1,”$-130000d)11, 初,)&TEXT(A1,dbnum1$-130000d)IT部落窝电运A =CHOOSE (MOD( b r12012-2-1星辰年一月初十；22012-2-2在辰年一月十一excel公历转农历方法四：使用自定义函数上面介绍的excel公历转农历都是使用excel内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。如下图所示，我们在 B2输入公式：=nongli(A2)，即可。C列农历简称中C2公式是: =RIGHT(B
4、2,LEN(B2)-8)。关于自定义函数的使用方法请参考:excel自定义函数添加和使用方法E2川 =nong 11(A2) f blwbbs.&wmC灯部落窝歌衣历筒称22012-02-08农历壬辰年优）正月十个 J正月十七 32012-01-29农历壬辰年优）正月初七正月初七42012-02-03农历壬辰金优）正月十二正月十二下面是此自定义函数相关的代码：Public Function NongLi(Optional XX_DATE As Date)Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11),
5、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)=
6、子DiZhi=丑DiZhi(2)=寅DiZhi(3)=卯DiZhi(4)=辰DiZhi(5)=巳DiZhi(6)=午 DiZhi=未 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) = * DayNa
7、me=初一 DayName(2)=初二 DayName(3)=初三DayName(4)=初四 DayName(5)=初五 DayName(6)=初六 DayName=初七 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)=廿一 DayNam
8、e(22)=廿二 DayName(23)=廿三DayName(24)=廿四DayName(25)=廿五DayName(26)=廿六DayName(27)=廿七DayName(28)=廿八DayName(29)=廿九DayName(30)=三十农历月份名MonName(0) = *MonName=正MonName(2)=二MonName(3)=三MonName(4)=四MonName(5)=五MonName(6)=六MonName=七MonName(8)=八MonName(9)=九MonName(10)=十MonName(11)=十一MonName(12)=腊公历每月前面的天数MonthAdd(
9、0) = 0MonthAdd(1) = 31MonthAdd(2) = 59MonthAdd(3) = 90MonthAdd(4) = 120MonthAdd(5) = 151MonthAdd(6) = 181MonthAdd(7) = 212MonthAdd(8) = 243MonthAdd(9) = 273MonthAdd(10) = 304MonthAdd(11) = 334农历数据NongliData(0) = 2635NongliData(1) = 333387NongliData(2) = 1701NongliData(3) = 1748NongliData(4) = 267701
10、NongliData(5) = 694NongliData(6) = 2391NongliData=133423NongliData(8) = 1175NongliData(9)=:396438NongliData(10)=3402NongliData(ll)=3749NongliData(12)=331177NongliData(13)=1453NongliData(14)=694NongliData(15)=201326NongliData(16)=2350NongliData(17)=465197NongliData(18)=3221NongliData(19)=3402NongliDa
11、ta(20)=400202NongliData(21)=2901NongliData(22)=1386NongliData(23)=267611NongliData(24)=605NongliData(25)=2349NongliData(26)=137515NongliData(27)=2709NongliData(28)=464533NongliData(29)=1738NongliData(30)=2901NongliData(31)=330421NongliData(32)=1242NongliData(33)=2651NongliData(34)=199255NongliData(3
12、5)=1323NongliData(36)=529706NongliData(37)=3733NongliData(38)=1706NongliData(39)=398762NongliData(40)=2741NongliData(41)=1206NongliData(42)=267438NongliData(43)=2647NongliData(44)=1318NongliData(45)=204070NongliData(46)=3477NongliData(47)=461653NongliData(48)=1386NongliData(49)=2413NongliData(50)=33
13、0077NongliData(51)=1197NongliData(52)=2637NongliData(53)=268877NongliData(54)=3365NongliData(55)=531109NongliData(56)=2900NongliData(57)=2922NongliData(58)=398042NongliData(59)=2395NongliData(60)=1179NongliData(61)=267415NongliData(62)=2635NongliData(63)=661067NongliData(64)=1701NongliData(65)=1748NongliData(66)=398772NongliData(67)=2742NongliData(68)=2391NongliData(69)=330031NongliData(70)=1175NongliData(71)=1611NongliData(72)=200010NongliData(73)=3749NongliData(74)=527717NongliData(75)=1452NongliData(76)=2742NongliData(77)=332397NongliData(78)=2350NongliD
