excel公历转农历的四种方法.doc

excel公历转农历的四种方法excel公历转农历方法一：使用text函数A列是公历日期，我们在B1单元格输入公式：=TEXT(A1,$-130000yyyy年m月&IF(LEN(-TEXT(A1,$-130000dd)=1,初,)&d)，下拉完成公历转农历。excel公历转农历方法二：使用text+MID函数A列仍然是公历，B1输入公式：=MID(甲乙丙丁戊己庚辛壬癸,MOD(TEXT(A1,$-130000e)-4,10)+1,1)&MID(子丑寅卯辰巳午未申酉戌亥,MOD(TEXT(A1,$-130000e)-4,12)+1,1)&年&TEXT(A1,$-130000DBNum1m月d日即可。excel公历转农历方法三：使用text+MID+ CHOOSE+ YEAR函数A1为公历，B1输入：=CHOOSE(MOD(YEAR(A1)-1900,10)+1,庚,辛,壬,癸,甲,乙,丙,丁,戊,己)&CHOOSE(MOD(YEAR(A1)-1900,12)+1,子,丑,寅,卯,辰,巳,午,未,申,酉,戌,亥)&TEXT(A1,dbnum1$-130000年m月&IF(-TEXT(A1,$-130000d) GongliStrcurYear = Year(curTime)curMonth = Month(curTime)curDay = Day(curTime)GongliStr = curYear & 年If (curMonth 10) ThenGongliStr = GongliStr & 0 & curMonth & 月ElseGongliStr = GongliStr & curMonth & 月End IfIf (curDay 2) ThenTheDate = TheDate + 1End If计算农历天干、地支、月、日isEnd = 0m = 0DoIf (NongliData(m) 4095) Thenk = 11Elsek = 12End Ifn = kDoIf (n 0) ThenExit DoEnd If获取NongliData(m)的第n个二进制位的值bit = NongliData(m)For i = 1 To n Step 1bit = Int(bit / 2)Nextbit = bit Mod 2If (TheDate (Int(NongliData(m) / 65536) + 1) ThencurMonth = curMonth - 1End IfEnd If生成农历天干、地支、属相 = NongliStrNongliStr = 农历 & TianGan(curYear - 4) Mod 60) Mod 10) & DiZhi(curYear - 4) Mod 60) Mod 12) & 年NongliStr = NongliStr & ( & ShuXiang(curYear - 4) Mod 60) Mod 12) & )生成农历月、日 = NongliDayStrIf (curMonth 1) ThenNongliDayStr = 闰 & MonName(-1 * curMonth)ElseNongliDayStr = MonName(curMonth)End IfNongliDayStr = NongliDayStr & 月NongliDayStr = N