如何在Excel中实现农历日期转化

L罗乐 2016-10-18

展开全文

农

历与阳历（公历）是两套不同的历法，在Excel中正常显示的阳历日期，但对于我们中国人而言不是很方便，不能马上看出农历日期，因为我们除了过洋节之外还要过中国人的节日嘛。

这里老墨整理一个比较完整的函数公式来转化农历日期。

简单版（数字呈现）

下表中B2单元格就是第一种情况，“2016-9-17”表示农历是九月十七日。该公式是：=TEXT(A2,'[$-130000]YYYY-M-D')。

TEXT()用来转化文本格式，这个公式的关键是：[$-130000]，它是Excel中阳历转化农历的参数，不过它存在一个问题就是没法计算闰月，估计老外无法理解中国人闰月的概念，凡是闰年，它直接表示一年13个月，道理是一样的。

进阶版（中文呈现）

上表C2单元格，相比较B2单元格呈现就有了进阶，以中文呈现，并且以天干地支表现年份。

公式相对之前肯定复杂：=MID(' 甲乙丙丁戊己庚辛壬癸',MOD(TEXT(A2,'[$-130000]e')-4,10) 1,1)&MID('子丑寅卯辰巳午未申酉戌亥',MOD(TEXT(A2,'[$-130000]e')-4,12) 1,1)&'年'&TEXT(A2,' [$-130000][DBNum1]m月d日')

我们可以分解为两大部分：

1）计算年份：MID(' 甲乙丙丁戊己庚辛壬癸',MOD(TEXT(A2,'[$-130000]e')-4,10) 1,1)&MID('子丑寅卯辰巳午未申酉戌亥',MOD(TEXT(A2,'[$-130000]e')-4,12) 1,1)&'年'，这里加了MID()文本提取函数来获取天干地支。

2）计算月日：TEXT(A2,' [$-130000][DBNum1]m月d日')，这里增加了参数[DBNum1]，表示数值以“一、而二、三......”中文格式显示。

两者组合一起的就是C列结果。

完美版

作为中国人，我们知道“一月”不叫“一月”，叫“正月”；“十二月”不叫“十二月”，叫“腊月”；同样每月前10天，是初一、初二......20日开始是廿、廿一......30日是卅等等。

如何更完美地呈现，在这里老墨将进阶版公式再度分解，加上判断，然后再组合就实现了效果，公式：

=MID(' 甲乙丙丁戊己庚辛壬癸',MOD(TEXT(A2,'[$-130000]e')-4,10) 1,1)&MID('子丑寅卯辰巳午未申酉戌亥',MOD(TEXT(A2,'[$-130000]e')-4,12) 1,1)&'年'&IF(TEXT(A2,' [$-130000]m')*1=12,' 腊',IF(TEXT(A2,' [$-130000]m')*1=1,' 正',TEXT(A2,' [$-130000][DBNum1]m')))&'月'&IF(TEXT(A2,' [$-130000]d')-9<=1,'初',IF(TEXT(A2,' [$-130000]d')-29>=1,'卅',IF(TEXT(A2,' [$-130000]d')-19>=1,'廿','十')))&IF(RIGHT(TEXT(A2,' [$-130000]d'),1)*1=0,'',TEXT(RIGHT(TEXT(A2,' [$-130000]d'),1)*1,'[DBNum1]d'))&'日'

1）年份公式：

MID(' 甲乙丙丁戊己庚辛壬癸',MOD(TEXT(A2,'[$-130000]e')-4,10) 1,1)&MID('子丑寅卯辰巳午未申酉戌亥',MOD(TEXT(A2,'[$-130000]e')-4,12) 1,1)&'年'

2）月份公式：

IF(TEXT(A2,' [$-130000]m')*1=12,' 腊',IF(TEXT(A2,' [$-130000]m')*1=1,' 正',TEXT(A2,' [$-130000][DBNum1]m')))&'月'

3）日期公式：

IF(TEXT(A2,' [$-130000]d')-9<=1,'初',IF(TEXT(A2,' [$-130000]d')-29>=1,'卅',IF(TEXT(A2,' [$-130000]d')-19>=1,'廿','十')))&IF(RIGHT(TEXT(A2,' [$-130000]d'),1)*1=0,'',TEXT(RIGHT(TEXT(A2,' [$-130000]d'),1)*1,'[DBNum1]d'))&'日'