Excel公歷轉(zhuǎn)農(nóng)歷陽歷轉(zhuǎn)陰歷講課教案

1、Good is good, but better carries it.精益求精，善益求善。Excel公歷轉(zhuǎn)農(nóng)歷陽歷轉(zhuǎn)陰歷-參看一：可以這樣試一下：EXCEL2007中可以直接做到，例如TEXT(日期,$-130000e-m-d)，可返回日期的阿拉伯數(shù)字的農(nóng)歷年月日，中文農(nóng)歷轉(zhuǎn)換公式如下：B2單元格=MID(庚辛壬癸甲乙丙丁戊己,MOD(TEXT(A2,$-130000e),10)+1,1)&MID(申酉戌亥子丑寅卯辰巳午未,MOD(TEXT(A2,$-130000e),12)+1,1)&TEXT(A2,dbnum1$-130000年M月&IF(-TEXT(A2,$-130000d)-10,初

2、)&D日)此公式僅EXCEL2007以使用，EXCEL2003及以下不能正常使用上面那個公式好像有問題，可以試一下這個：參看二：日期在A2,這個公式在B2=CHOOSE(MOD(YEAR(LEFT(TEXT(A2,$-130000yyyy年mm月&IF(LEN(-TEXT(A2,$-130000dd)=1,初,)&dd),4)&-&MID(TEXT(A2,$-130000yyyy年mm月&IF(LEN(-TEXT(A2,$-130000dd)=1,初,)&dd),6,2)&-&RIGHT(TEXT(A2,$-130000yyyy年mm月&IF(LEN(-TEXT(A2,$-130000dd)=

3、1,初,)&dd),2)-1900,10)+1,庚,辛,壬,癸,甲,乙,丙,丁,戊,己)&CHOOSE(MOD(YEAR(LEFT(TEXT(A2,$-130000yyyy年mm月&IF(LEN(-TEXT(A2,$-130000dd)=1,初,)&dd),4)&-&MID(TEXT(A2,$-130000yyyy年mm月&IF(LEN(-TEXT(A2,$-130000dd)=1,初,)&dd),6,2)&-&RIGHT(TEXT(A2,$-130000yyyy年mm月&IF(LEN(-TEXT(A2,$-130000dd)=1,初,)&dd),2)-1900,12)+1,子,丑,寅,卯,辰

4、,巳,午,未,申,酉,戌,亥)&TEXT(A2,dbnum1$-130000年m月&IF(-TEXT(A2,$-130000d)11,初,)&TEXT(A2,dbnum1$-130000d日)=CHOOSE(MOD(YEAR(LEFT(TEXT(A11,$-130000yyyy年mm月&IF(LEN(-TEXT(A11,$-130000dd)=1,初,)&dd),4)&-&MID(TEXT(A11,$-130000yyyy年mm月&IF(LEN(-TEXT(A11,$-130000dd)=1,初,)&dd),6,2)&-&RIGHT(TEXT(A11,$-130000yyyy年mm月&IF(L

5、EN(-TEXT(A11,$-130000dd)=1,初,)&dd),2)-1900,10)+1,庚,辛,壬,癸,甲,乙,丙,丁,戊,己)&CHOOSE(MOD(YEAR(LEFT(TEXT(A11,$-130000yyyy年mm月&IF(LEN(-TEXT(A11,$-130000dd)=1,初,)&dd),4)&-&MID(TEXT(A11,$-130000yyyy年mm月&IF(LEN(-TEXT(A11,$-130000dd)=1,初,)&dd),6,2)&-&RIGHT(TEXT(A11,$-130000yyyy年mm月&IF(LEN(-TEXT(A11,$-130000dd)=1,

6、初,)&dd),2)-1900,12)+1,子,丑,寅,卯,辰,巳,午,未,申,酉,戌,亥)&TEXT(A11,dbnum1$-130000年m月&IF(-TEXT(A11,$-130000d)GongliStrcurYear=Year(curTime)curMonth=Month(curTime)curDay=Day(curTime)GongliStr=curYear&年If(curMonth10)ThenGongliStr=GongliStr&0&curMonth&月ElseGongliStr=GongliStr&curMonth&月EndIfIf(curDay2)ThenTheDate=

7、TheDate+1EndIf計算農(nóng)歷天干、地支、月、日isEnd=0m=0DoIf(NongliData(m)4095)Thenk=11Elsek=12EndIfn=kDoIf(n0)ThenExitDoEndIf獲取NongliData(m)的第n個二進制位的值bit=NongliData(m)Fori=1TonStep1bit=Int(bit/2)Nextbit=bitMod2If(TheDate(Int(NongliData(m)/65536)+1)ThencurMonth=curMonth-1EndIfEndIf生成農(nóng)歷天干、地支、屬相=NongliStrNongliStr=農(nóng)歷&Ti

8、anGan(curYear-4)Mod60)Mod10)&DiZhi(curYear-4)Mod60)Mod12)&年NongliStr=NongliStr&(&ShuXiang(curYear-4)Mod60)Mod12)&)生成農(nóng)歷月、日=NongliDayStrIf(curMonth1)ThenNongliDayStr=閏&MonName(-1*curMonth)ElseNongliDayStr=MonName(curMonth)EndIfNongliDayStr=NongliDayStr&月NongliDayStr=NongliDayStr&DayName(curDay)NongLi=NongliStr&NongliDayStrEndFunctio