如何在excel中实现阳历转化阴历

1、步骤一，在Excel工作表界面下按VALT+F11组合键打开VBA窗口，进入VBA编辑环境，在其窗口的菜单栏上依次单击插入一模块”可在当前VBA工程中插入模块，并直接进入此模块的代码编辑区域。可在此编辑区域中输入自定义函数的程序代码,如图：PublicFunctionNongLi(OptionalXX_DATEAsDate)II.A12=IlI-.-.-E-DimMonthAdd(11),NongliData(99),TianGan(9),DiZhi(11),ShuXiang(11),DayName(30),MonName(12)iihii山+也DimcurTime,curYear,curMo

2、nth,curDayDimGongliStr,NongliStr,NongliDayStrIDIIII:Dimi,m,n,k,isEnd,bit,TheDate获取当前系统时间1curTime=Xx_DATETianGan(0)=呷TianGan(1)=乙TianGan(2)=丙TianGan(3)=丁TianGan(4)=戊TianGan(5)=己IJ，MLTianGan(6)=庚bTianGan(7)=辛TianGan(8)=壬TianGan(9)=癸地支名称DiZhi(O)=子DiZhi(1)=丑DiZhi(2)=寅DiZhi(3)=卯DiZhi(4)=辰DiZhi(5)=巳DiZhi(

3、6)=午DiZhi(7)=未DiZhi(8)=申DiZhi(9)=酉DiZhi(IO)=戌1DiZhi(11)=亥属相名称ShuXiang(O)=鼠ShuXiang(1)=牛ShuXiang(2)=虎ShuXiang(3)=兔ShuXiang(4)=龙ShuXiang(5)=蛇ShuXiang(6)=马ShuXiang(7)=羊ShuXiang(8)=猴ShuXiang(9)=鸡ShuXiang(10)=狗ShuXiang(11)=猪农历日期名DayName(O)=*DayName(1)=初一DayName(2)=初二DayName(3)=初三DayName(4)=初四DayName(5)=初

4、五DayName(6)=初六DayName(7)=初七DayName(8)=初八DayName(9)=初九DayName(IO)=初十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(2

5、7)=廿七DayName(28)=廿八DayName(29)=廿九DayName(30)=三十农历月份名|MonName(O)=*MonName(1)=正MonName(2)=二MonName(3)=三MonName(4)=四MonName(5)=五MonName(6)=六MonName(7)=七MonName(8)=八MonName(9)=九MonName(10)=十MonName(11)=十,VIMonName(12)=腊公历每月前面的天数MonthAdd(O)=0MonthAdd(1)=31MonthAdd(2)=59MonthAdd(3)=90MonthAdd(4)=120MonthA

6、dd(5)=151MonthAdd(6)=181MonthAdd(7)=212MonthAdd(8)=243MonthAdd(9)=273MonthAdd(10)=304MonthAdd(11)=334农历数据NongliData(O)=2635NongliData(1)=333387NongliData(2)=1701NongliData(3)=1748NongliData(4)=267701NongliData(5)=694NongliData(6)=2391NongliData(7)=133423NongliData(8)=1175NongliData(9)=396438NongliDa

7、ta(10)=3402NongliData(11)=3749NongliData(12)=331177NongliData(13)=1453NongliData(14)=694NongliData(15)=201326NongliData(16)=2350NongliData(17)=465197NongliData(18)=3221NongliData(19)=3402NongliData(20)=400202NongliData(21)=2901NongliData(22)=1386NongliData(23)=267611NongliData(24)=605NongliData(25)=

8、2349NongliData(26)=137515NongliData(27)=2709NongliData(28)=464533NongliData(29)=1738NongliData(30)=2901NongliData(31)=330421NongliData(32)=1242NongliData(33)=2651NongliData(34)=199255NongliData(35)=1323NongliData(36)=529706NongliData(37)=3733NongliData(38)=1706NongliData(39)=398762NongliData(40)=274

9、1NongliData(41)=1206NongliData(42)=267438NongliData(43)=2647NongliData(44)=1318NongliData(45)=204070NongliData(90)=2890NongliData(91)=267946NongliData(92)=2773NongliData(93)=592565NongliData(94)=1210NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)NongliData(51)NongliData(52)Non

10、gliData(53)NongliData(54)NongliData(55)NongliData(56)NongliData(57)NongliData(58)NongliData(59)NongliData(60)NongliData(61)NongliData(62)NongliData(63)NongliData(64)NongliData(65)NongliData(66)NongliData(67)NongliData(68)NongliData(69)NongliData(70)NongliData(71)NongliData(72)NongliData(73)NongliDat

11、a(74)NongliData(75)NongliData(76)NongliData(77)NongliData(78)NongliData(79)NongliData(80)NongliData(81)NongliData(82)NongliData(83)NongliData(84)NongliData(85)NongliData(86)NongliData(87)NongliData(88)347746165313862413330077119726372688773365531109290029223980422395117926741526356610671701174839877

12、227422391330031117516112000103749527717145227423323972350322226894934023493133973138646421960523493341232709NongliData(89)NongliData(95)=2651NongliData(96)=395863NongliData(97)=1323NongliData(98)=2707NongliData(99)=265877生成当前公历年、月、日=GongliStrcurYear=Year(curTime)curMonth=Month(curTime)curDay=Day(cur

13、Time)GongliStr=curYear&”年If(curMonth10)ThenGongliStr=GongliStr&0&curMonth&月ElseGongliStr=GongliStr&curMonth&月EndIfIf(curDay2)ThenTheDate=TheDate+1EndIf计算农历天干、地支、月、日isEnd=0m=0DoIf(NongliData(m)4095)Thenk=11Elsek=12EndIfn=kDoIf(n0)ThenExitDoNongliData(46)NongliData(47)NongliData(48)NongliData(49)Nongl

14、iData(50)EndIf获取NongliData(m)的第n个二进制位的值bit=NongliData(m)Fori=1TonStep1bit=Int(bit/2)Nextbit=bitMod2If(TheDate(Int(NongliData(m)/65536)+1)ThencurMonth=curMonth-1EndIfEndIf生成农历天干、地支、属相=NongliStrNongliStr=农历&TianGan(curYear-4)Mod60)Mod10)&DiZhi(curYear-4)Mod60)Mod12)&年NongliStr=NongliStr&(&ShuXiang(cur

15、Year-4)Mod60)Mod12)&)生成农历月、日=NongliDayStrIf(curMonth1)ThenNongliDayStr=闰&MonName(-1*curMonth)ElseNongliDayStr=MonName(curMonth)EndIfNongliDayStr=NongliDayStr&月NongliDayStr=NongliDayStr&DayName(curDay)NongLi=NongliStr&NongliDayStrEndFunction步骤2,单击窗口右上角的关闭按钮关闭VBA编辑窗口，返回到工作表窗口中。此时即可在当前工作簿中使用刚刚创建的自定义函数，

16、并且可以在函数列表的用户定义类别中找到此自定义函数。如图NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)步骤4,保存后需要在加载项中添加加载宏。单击按钮一选项”打开选项对话框，在左侧类别中选择加载项”然后在右侧下方的管理下拉列表中选择加载项”再单击转到按钮，打开加载宏对话框，在其中勾选之前所保存的包含自定义函数的加载宏前面的复选框，最后单击确定即可完成加载项的添加。NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)然寸亘二臣妻件輛良雅弔翌輛专叢洽沁，时曲W五議+41晁：J込出崔冏三洩NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)*倔门迫,J目诙憩旖醸商E翻翳錚碱T$隘EBTB竖晡|珂稣畫肃絶维