excel一个单元格是公历一个单元格显示相应的农历
发布网友
发布时间：2022-04-24 01:39
共2个回答
热心网友
时间：2023-07-01 18:22
excel公历转农历方法一：使用text函数
A列是公历日期，我们在B1单元格输入公式：=TEXT(A1,"[$-130000]yyyy年m月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")，下拉完成公历转农历。
excel公历转农历方法二：使用text+MID函数
A列仍然是公历，B1输入公式：=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-130000]e")-4,12)+1,1)&"年"&TEXT(A1,"[$-130000][DBNum1]m月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,"[$-130000]d")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d"))追问非常感谢，但是我输入后一个也不能用，我用的是Excel2003的，能不能我把表格发给您，您帮我添加上发给我呢？
热心网友
时间：2023-07-01 18:22
=TEXT(A1,"[$-130000]e-m-d")
这样试试。07版中有效。
