excel公歷轉(zhuǎn)農(nóng)歷的四種方法

1、excel公歷轉(zhuǎn)農(nóng)歷的四種方法excel公歷轉(zhuǎn)農(nóng)歷方法一：使用 text函數(shù)A列是公歷日期，我們?cè)?B1單元格輸入公式：=TEXT(A1,"$-130000yyyy 年m月"&IF(LEN(-TEXT(A1,”$-130000dd")=1," 初","")&"d"),下拉完成公歷轉(zhuǎn)農(nóng)歷。A =TEXT(A【T部落窩娜- 萬 ，1 | 2012-2-1 2012年1月 10二22012-1-29屹012年 1 月初？excel公歷轉(zhuǎn)農(nóng)歷方法二：使用 text+MID 函數(shù)A列仍然是公歷，B1

2、輸入公式：=MID("甲乙丙丁戊己庚辛壬癸 ",MOD(TEXT(A1,"$-13 0000e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥”,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公歷轉(zhuǎn)農(nóng)歷方法

3、三：使用 text+MID+ CHOOSE+ YEAR函數(shù)A1 為公歷，B1 輸入：=CHOOSE(MOD(YEAR(A1)-1900,10)+1,"庚","辛"，"壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(A1)-1900,12)+1,"子","丑","寅","卯&

4、quot;,"辰","巳","午"，"未","申"，"酉","戌","亥")&TEXT(A1,"dbnum1$-130000年 m 月"&IF(-TEXT(A1,”$-130000d")<11," 初","")&TEXT(A1,"dbnum1$-130000d")IT部落窩電運(yùn)A =CHOOSE (MOD( b r12

5、012-2-1星辰年一月初十；22012-2-2在辰年一月十一excel公歷轉(zhuǎn)農(nóng)歷方法四：使用自定義函數(shù)上面介紹的excel公歷轉(zhuǎn)農(nóng)歷都是使用excel內(nèi)置函數(shù)完成的，有一個(gè)弊端就是公式太長(zhǎng)，如果使用自定義函數(shù)，在公式欄就可以簡(jiǎn)化輸入。如下圖所示，我們?cè)?B2輸入公式：=nongli(A2)，即可。C列農(nóng)歷簡(jiǎn)稱中C2公式是: =RIGHT(B2,LEN(B2)-8)。關(guān)于自定義函數(shù)的使用方法請(qǐng)參考:excel自定義函數(shù)添加和使用方法E2川 =nong 11(A2) f blwbbs.&wmC燈部落窩歌衣歷筒稱22012-02-08農(nóng)歷壬辰年優(yōu)）正月十個(gè) J正月十七 32012-01-2

6、9農(nóng)歷壬辰年優(yōu)）正月初七正月初七42012-02-03農(nóng)歷壬辰金優(yōu)）正月十二正月十二下面是此自定義函數(shù)相關(guān)的代碼：Public Function NongLi(Optional XX_DATE As Date)Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(3 0), MonName(12)Dim curTime, curYear, curMonth, curDayDim GongliStr, NongliStr, NongliDayStrDim i, m, n, k, isEnd,

7、bit, TheDate獲取當(dāng)前系統(tǒng)時(shí)間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)

8、="子"DiZhi="丑"DiZhi(2)="寅"DiZhi(3)="卯"DiZhi(4)="辰"DiZhi(5)="巳"DiZhi(6)="午" DiZhi="未" DiZhi(8)="申" DiZhi(9)="酉" DiZhi(10)="戌" DiZhi(11)="亥"'屬相名稱ShuXiang(0)="鼠"ShuXiang(

9、1)="牛"ShuXiang(2)="虎" ShuXiang(3)="兔" ShuXiang(4)="龍" ShuXiang(5)="蛇" ShuXiang(6)="馬" ShuXiang(7)="羊"ShuXiang(8)="飛" ShuXiang(9)="雞" ShuXiang(10)="狗" ShuXiang(11)="豬" 農(nóng)歷日期名DayName(0) DayName

10、 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)=A,十一二三四初十十十十DayName(15)="十五DayName(16

11、)="十六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)="廿九

12、"DayName(30)="三十"'農(nóng)歷月份名MonName(0) = "*"MonName="正"MonName(2)="二"MonName(3)="三"MonName(4)="四"MonName(5)="五"MonName(6)="六"MonName="七"MonName(8)="八"MonName(9)="九"MonName(10)="十&qu

13、ot;MonName(11)="十一"MonName(12)="臘"'公歷每月前面的天數(shù)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農(nóng)歷數(shù)據(jù)NongliData(0) = 2635NongliD

14、ata(1) = 333387NongliData(2) = 1701NongliData(3) = 1748NongliData(4) = 267701NongliData(5) = 694NongliData(6) = 2391NongliData=133423NongliData(8) = 1175NongliData(9)=:396438NongliData(10)=3402NongliData(ll)=3749NongliData(12)=331177NongliData(13)=1453NongliData(14)=694NongliData(15)=201326NongliDat

15、a(16)=2350NongliData(17)=465197NongliData(18)=3221NongliData(19)=3402NongliData(20)=400202NongliData(21)=2901NongliData(22)=1386NongliData(23)=267611NongliData(24)=605NongliData(25)=2349NongliData(26)=137515NongliData(27)=2709NongliData(28)=464533NongliData(29)=1738NongliData(30)=2901NongliData(31)=

16、330421NongliData(32)=1242NongliData(33)=2651NongliData(34)=199255NongliData(35)=1323NongliData(36)=529706NongliData(37)=3733NongliData(38)=1706NongliData(39)=398762NongliData(40)=2741NongliData(41)=1206NongliData(42)=267438NongliData(43)=2647NongliData(44)=1318NongliData(45)=204070NongliData(46)=347

17、7NongliData(47)=461653NongliData(48)=1386NongliData(49)=2413NongliData(50)=330077NongliData(51)=1197NongliData(52)=2637NongliData(53)=268877NongliData(54)=3365NongliData(55)=531109NongliData(56)=2900NongliData(57)=2922NongliData(58)=398042NongliData(59)=2395NongliData(60)=1179NongliData(61)=267415No

18、ngliData(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)=2742NongliD

19、ata(77)=332397NongliData(78)=2350NongliData(79)=3222NongliData(80)=268949NongliData(81)=3402NongliData(82)=3493NongliData(83)=133973NongliData(84)=1386NongliData(85)=464219NongliData(86)=605NongliData(87)=2349NongliData(88)=334123NongliData(89)=2709NongliData(90)=2890NongliData(91)=267946NongliData(

20、92)=2773NongliData(93)=592565NongliData(94)=1210NongliData(95)=2651NongliData(96)=395863NongliData(97)=1323NongliData(98)=2707NongliData(99)=265877'生成當(dāng)前公歷年、月、日 => GongliStr curYear = Year(curTime) curMonth = Month(curTime) curDay = Day(curTime) GongliStr = curYear & "年" If (curM

21、onth < 10) Then GongliStr = GongliStr & "0" & curMonth & "月" Else GongliStr = GongliStr & curMonth & "月" End If If (curDay < 10) Then GongliStr = GongliStr & "0" & curDay & "日" Else GongliStr = GongliStr & cu

22、rDay & "日" End If 計(jì)算到初始時(shí)間1921年2月8日的天數(shù)：1921-2-8(正月初一) TheDate = (curYear - 1921) * 365 + Int(curYear - 1921) / 4) + curDay + Month Add(curMonth - 1) - 38 If (curYear Mod 4) = 0 And curMonth > 2) Then TheDate = TheDate + 1 End If 計(jì)算農(nóng)歷天干、地支、月、日 isEnd = 0 m = 0 Do If (NongliData(m) <

23、 4095) Then k = 11 Else k = 12 End If n = k Do If (n < 0) Then Exit Do End If '獲取NongliData(m)的第n個(gè)二進(jìn)制位的值 bit = NongliData(m) For i = 1 To n Step 1 bit = Int(bit / 2)Next bit = bit Mod 2If (TheDate <= 29 + bit) Then isEnd = 1 Exit Do End If TheDate = TheDate - 29 - bit n = n - 1 Loop If (isEnd = 1) Then Exit Do End If m = m + 1 Loop curYear = 1921 + m curMonth = k - n + 1 curDa