Excel公历转农历阳历转阴历
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
参看一：
可以这样试一下：EXCEL2007中可以直接做到，例如
TEXT(日期,"[$-130000]e-m-d")，可返回日期的阿拉伯数字的农历年月日，
中文农历转换公式如下：
B2单元格
=MID("庚辛壬癸甲乙丙丁戊己",MOD(TEXT(A2,"[$-130000]e"),10)+1,1)&MID("申酉戌亥子丑寅卯辰巳午未",MOD(TEXT(A2,"[$-130000]e"),12)+1,1)&TEXT(A2,"[dbnum1][$-130000]年M月
"&IF(-TEXT(A2,"[$-130000]d")<-10,,"初")&"D日")
此公式仅EXCEL2007以使用，EXCEL2003及以下不能正常使用
上面那个公式好像有问题，可以试一下这个：
参看二：
日期在A2,
这个公式在B2
=CHOOSE(MOD(YEAR(LEFT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己
")&CHOOSE(MOD(YEAR(LEFT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥
")&TEXT(A2,"[dbnum1][$-130000]年m月"&IF(--TEXT(A2,"[$-130000]d")<11,"初
","")&TEXT(A2,"[dbnum1][$-130000]d日"))
=CHOOSE(MOD(YEAR(LEFT(TEXT(A11,"[$-130000]yyyy年mm月"
&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")
&"dd"),4)
&"-"&MID(TEXT(A11,"[$-130000]yyyy年mm月"
&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")
&"dd"),6,2)
&"-"&RIGHT(TEXT(A11,"[$-130000]yyyy年mm月"
&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")
&"dd"),2))-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")
&CHOOSE(MOD(YEAR(LEFT(TEXT(A11,"[$-130000]yyyy年mm月"
&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")
&"dd"),4)&"-"
&MID(TEXT(A11,"[$-130000]yyyy年mm月"
&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")
&"dd"),6,2)
&"-"
&RIGHT(TEXT(A11,"[$-130000]yyyy年mm月"
&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")
&"dd"),2))-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")
&TEXT(A11,"[dbnum1][$-130000]年m月"
&IF(--TEXT(A11,"[$-130000]d")<11,"初","")
&TEXT(A11,"[dbnum1][$-130000]d"))
Excel公历转农历（阳历转阴历）
用的是excel中的自定义函数，代码如下：
Public Function NongLi(Optional XX_DATE As Date)
Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), MonName(12)
Dim curTime, curYear, curMonth, curDay
Dim GongliStr, NongliStr, NongliDayStr
Dim 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) = "子"
DiZhi(1) = "丑"
DiZhi(2) = "寅"
DiZhi(3) = "卯"
DiZhi(4) = "辰"
DiZhi(5) = "巳"
DiZhi(6) = "午"
DiZhi(7) = "未"
DiZhi(8) = "申"
DiZhi(9) = "酉"
DiZhi(10) = "戌"
DiZhi(11) = "亥"
'属相名称
ShuXiang(0) = "鼠"
ShuXiang(1) = "牛"
ShuXiang(2) = "虎"