Excel 中阳历日期如何转换成阴历日Ɯ

Question

如题所述

Answer 1

Excel可以实现中阳历日期转换成阴历日期，具体操作如下：

一、使用工具：excel2010

二、操作步骤：

1、假设A1为阳历日期，B1为转换后的阴历日期值，点击B1单元格输入以下代码：

=CHOOSE(MOD(YEAR(LEFT(TEXT(A1,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A1,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A1,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(LEFT(TEXT(A1,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A1,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A1,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")&TEXT(A1,"[dbnum1][$-130000]年m月"&IF(--TEXT(A1,"[$-130000]d")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d日"))

如图：

2、输入代码后按回车键，这时B1返回A1对应的阴历日期，如图：