最近有个同事问我，公历与农历换算在Excel中有公式吗？他认为我是教授Office高级办公应用的教师，所以问起这件事情。我直言回答他：很遗憾，有是有(比如：SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(日期,"[$-130000][dbnum1]yyyy年m月"&IF((--TEXT(日期,"[$-130000]d"))<11,"初","")&"d"),"年一月","年正月"),"十二月","腊月"),"十一月","冬月")，但是不是很完美，而且不完全正确，只能转换个大慨，对于完全精确的农历或阴历，这种工作表函数就显得有些拙劣。那是因为公历（现在用的是格里历，以前还曾用儒列历）和中国农历都不是严格按一个公式计算得到的，所以两个历法没有直接的公式。

试图用一个简单的公式，而不利用许多像万年历一样的数据（尽管可以简化），就达到公农历转换的目的，这是不可能的。我们能见到的所有公农历转换的软件，都存有多少不等的一些年份的历法数据。

这些数据之中，最重要的是农历的每月天数。因为农历是阴阳历，阴历部分（按月相编的部分）决定月日，阳历部分（按太阳高度编的部分）就是节气。节气基本上是与公历直接对应的，可以较容易的推算；但月相周期与地球公转的关系相对复杂，一般都需要天文观测数据授时，所以农历每月是大月还是小月没有简单的公式。

至于公历和农历的置闰，倒都有一定的方式推算，并不困难。

如果只是天文数据，其实都还是可以算的——因为天文周期十分稳定，我们只要严格地按天文周期推算就可以了。可是还有更为麻烦的一点，就是公历和农历这些历法，并不是完全严格地按照天文上的实际周期进行的，还有许多人为的因素。最著名的就是罗马皇帝几次修改2、8等月份长短，以及儒列历中因计算不准确而人为地去掉了十天等事。中国农历也有因为古代天文观测不准确造成的政府颁布的历法与天象不吻合的（我们不能苛求古人观测的精度）。所以这两部历法都是与一个时期社会有关的东西，也就没有一定的规律可循了。

鉴于以上情况，我用自己熟悉的VBA技术奋力自行设计了公历农历之间的互转函数，分享给各位，算是弥补它们之间自由精确转换的空白，也算是抛砖引玉吧！

一、我们从VBA后台看看关于公历<---->农历互转的VBA代码吧

模块1中的代码：

'强势自定义“公历”<---->“农历”互转函数

'原创：互联网

'修正：今日头条号作者“跟我学Office高级办公应用” 2019/10/12

'---农历数据定义---

'先以 Hexadecimal_To_Binary 函数还原成长度为 18 的字符串，其定义如下：

'前12个字节代表1-12月：1为大月，0为小月；压缩成十六进制(1-3位)

'第13位为闰月的情况，1为大月30天，0为小月29天；(4位)

'第14位为闰月的月份，如果不是闰月为0，否则给出月份(5位)

'最后4位为当年农历新年的公历日期，如0131代表1月31日；当作数值转十六进制(6-7位)

'定义如下农历(阴历)日期常量(1899～2100，共202年，但是事实上我们只需要用到1900～2100这201年即可）

Private Const ylData = "AB500D2,4BD0883," _

& "4AE00DB,A5700D0,54D0581,D2600D8,D9500CC,655147D,56A00D5,9AD00CA,55D027A,4AE00D2," _

& "A5B0682,A4D00DA,D2500CE,D25157E,B5500D6,56A00CC,ADA027B,95B00D3,49717C9,49B00DC," _

& "A4B00D0,B4B0580,6A500D8,6D400CD,AB5147C,2B600D5,95700CA,52F027B,49700D2,6560682," _

& "D4A00D9,EA500CE,6A9157E,5AD00D6,2B600CC,86E137C,92E00D3,C8D1783,C9500DB,D4A00D0," _

& "D8A167F,B5500D7,56A00CD,A5B147D,25D00D5,92D00CA,D2B027A,A9500D2,B550781,6CA00D9," _

& "B5500CE,535157F,4DA00D6,A5B00CB,457037C,52B00D4,A9A0883,E9500DA,6AA00D0,AEA0680," _

& "AB500D7,4B600CD,AAE047D,A5700D5,52600CA,F260379,D9500D1,5B50782,56A00D9,96D00CE," _

& "4DD057F,4AD00D7,A4D00CB,D4D047B,D2500D3,D550883,B5400DA,B6A00CF,95A1680,95B00D8," _

& "49B00CD,A97047D,A4B00D5,B270ACA,6A500DC,6D400D1,AF40681,AB600D9,93700CE,4AF057F," _

& "49700D7,64B00CC,74A037B,EA500D2,6B50883,5AC00DB,AB600CF,96D0580,92E00D8,C9600CD," _

& "D95047C,D4A00D4,DA500C9,755027A,56A00D1,ABB0781,25D00DA,92D00CF,CAB057E,A9500D6," _

& "B4A00CB,BAA047B,B5500D2,55D0983,4BA00DB,A5B00D0,5171680,52B00D8,A9300CD,795047D," _

& "6AA00D4,AD500C9,5B5027A,4B600D2,96E0681,A4E00D9,D2600CE,EA6057E,D5300D5,5AA00CB," _

& "76A037B,96D00D3,4AB0B83,4AD00DB,A4D00D0,D0B1680,D2500D7,D5200CC,DD4057C,B5A00D4," _

& "56D00C9,55B027A,49B00D2,A570782,A4B00D9,AA500CE,B25157E,6D200D6,ADA00CA,4B6137B," _

& "93700D3,49F08C9,49700DB,64B00D0,68A1680,EA500D7,6AA00CC,A6C147C,AAE00D4,92E00CA," _

& "D2E0379,C9600D1,D550781,D4A00D9,DA400CD,5D5057E,56A00D6,A6C00CB,55D047B,52D00D3," _

& "A9B0883,A9500DB,B4A00CF,B6A067F,AD500D7,55A00CD,ABA047C,A5A00D4,52B00CA,B27037A," _

& "69300D1,7330781,6AA00D9,AD500CE,4B5157E,4B600D6,A5700CB,54E047C,D1600D2,E960882," _

& "D5200DA,DAA00CF,6AA167F,56D00D7,4AE00CD,A9D047D,A2D00D4,D1500C9,F250279,D5200D1"

'定义农历 (阴历)每月的汉字大写日期“天”

Private Const ylMd0 = "初一初二初三初四初五初六初七初八初九初十十一十二十三十四十五" _

& "十六十七十八十九二十廿一廿二廿三廿四廿五廿六廿七廿八廿九三十 "

'定义农历 (阴历)一年中的汉字大写日期“月”

Private Const ylMn0 = "正二三四五六七八九十冬腊"

'定义农历 (阴历)年中的“天干”(如：甲乙丙丁......等)

Private Const ylTianGan0 = "甲乙丙丁戊已庚辛壬癸"

'定义农历 (阴历)年中的“地支”(如：子丑寅卯辰......等)

Private Const ylDiZhi0 = "子丑寅卯辰巳午未申酉戌亥"

'定义农历 (阴历)年中的“属相”(如：鼠牛虎兔龙......等)

Private Const ylShu0 = "鼠牛虎兔龙蛇马羊猴鸡狗猪"

'自定义“公历转农历”日期函数

Function GetYLDate(ByVal strDate As String) As String

On Error GoTo ExitFunction_Label

If Not IsDate(strDate) Then Exit Function

Dim setDate As Date, tYear As Integer, tMonth As Integer, tDay As Integer

setDate = CDate(strDate)

tYear = Year(setDate): tMonth = Month(setDate): tDay = Day(setDate)

'如果不是有效有日期，退出

If tYear > 2100 Or tYear < 1900 Then Exit Function

Dim daList() As String * 18, conDate As Date, thisMonths As String

Dim AddYear As Integer, AddMonth As Integer, AddDay As Integer, getDay As Integer

Dim YLyear As String, YLShuXing As String

Dim dd0 As String, mm0 As String, ganzhi(0 To 59) As String * 2

Dim RunYue As Boolean, RunYue1 As Integer, mDays As Integer, i As Integer

'加载2年内的农历数据

ReDim daList(tYear - 1 To tYear)

daList(tYear - 1) = Hexadecimal_To_Binary(Mid(ylData, (tYear - 1900) * 8 + 1, 7))

daList(tYear) = Hexadecimal_To_Binary(Mid(ylData, (tYear - 1900 + 1) * 8 + 1, 7))

AddYear = tYear

initYL:

AddMonth = CInt(Mid(daList(AddYear), 15, 2))

AddDay = CInt(Mid(daList(AddYear), 17, 2))

conDate = DateSerial(AddYear, AddMonth, AddDay) '农历新年日期

getDay = DateDiff("d", conDate, setDate) + 1 '相差天数

If getDay < 1 Then AddYear = AddYear - 1: GoTo initYL

thisMonths = Left(daList(AddYear), 14)

RunYue1 = Val("&H" & Right(thisMonths, 1)) '闰月月份

If RunYue1 > 0 Then '有闰月

thisMonths = Left(thisMonths, RunYue1) & Mid(thisMonths, 13, 1) & Mid(thisMonths, RunYue1 + 1)

End If

thisMonths = Left(thisMonths, 13)

For i = 1 To 13 '计算天数

mDays = 29 + CInt(Mid(thisMonths, i, 1))

If getDay > mDays Then

getDay = getDay - mDays

Else

If RunYue1 > 0 Then

If i = RunYue1 + 1 Then RunYue = True

If i > RunYue1 Then i = i - 1

End If

AddMonth = i

AddDay = getDay

Exit For

End If

Next

dd0 = Mid(ylMd0, (AddDay - 1) * 2 + 1, 2)

mm0 = Mid(ylMn0, AddMonth, 1) + "月"

For i = 0 To 59

ganzhi(i) = Mid(ylTianGan0, (i Mod 10) + 1, 1) + Mid(ylDiZhi0, (i Mod 12) + 1, 1)

Next

YLyear = ganzhi((AddYear - 4) Mod 60)

YLShuXing = Mid(ylShu0, ((AddYear - 4) Mod 12) + 1, 1)

If RunYue Then mm0 = "闰" & mm0

GetYLDate = "农历" & YLyear & "(" & YLShuXing & ")年" & mm0 & dd0

ExitFunction_Label:

End Function

'自定义“农历转公历”日期函数

'secondMonth 为真，则表示当tMonth是闰月时，取第二个月

Function GetDate(ByVal tYear As Integer, tMonth As Integer, tDay As Integer, Optional secondMonth As Boolean = False) As String

On Error GoTo ExitFunction_Label

If tYear > 2100 Or tYear < 1899 Or tMonth > 12 Or tMonth < 1 Or tDay > 30 Or tDay < 1 Then Exit Function

Dim thisMonths As String, ylNewYear As Date, toMonth As Integer

Dim mDays As Integer, RunYue1 As Integer, i As Integer

thisMonths = Hexadecimal_To_Binary(Mid(ylData, (tYear - 1899) * 8 + 1, 7))

If tDay > 29 + CInt(Mid(thisMonths, tMonth, 1)) Then Exit Function

ylNewYear = DateSerial(tYear, CInt(Mid(thisMonths, 15, 2)), CInt(Mid(thisMonths, 17, 2))) '农历新年日期

thisMonths = Left(thisMonths, 14)

RunYue1 = Val("&H" & Right(thisMonths, 1)) '闰月月份

toMonth = tMonth - 1

If RunYue1 > 0 Then '有闰月

thisMonths = Left(thisMonths, RunYue1) & Mid(thisMonths, 13, 1) & Mid(thisMonths, RunYue1 + 1)

If tMonth > RunYue1 Or (secondMonth And tMonth = RunYue1) Then toMonth = tMonth

End If

thisMonths = Left(thisMonths, 13)

mDays = 0

For i = 1 To toMonth

mDays = mDays + 29 + CInt(Mid(thisMonths, i, 1))

Next

mDays = mDays + tDay

GetDate = ylNewYear + mDays - 1

ExitFunction_Label:

End Function

'将压缩的阴历字符还原

Private Function Hexadecimal_To_Binary(ByVal strHex As String) As String '十六进制转二进制

Dim i As Integer, i1 As Integer, tmpV As String

Const hStr = "0123456789ABCDEF"

Const bStr = "0000000100100011010001010110011110001001101010111100110111101111"

tmpV = UCase(Left(strHex, 3))

'以下是十六进制转二进制的具体操作

For i = 1 To Len(tmpV)

i1 = InStr(hStr, Mid(tmpV, i, 1))

Hexadecimal_To_Binary = Hexadecimal_To_Binary & Mid(bStr, (i1 - 1) * 4 + 1, 4)

Next

Hexadecimal_To_Binary = Hexadecimal_To_Binary & Mid(strHex, 4, 2)

'十六进制转十进制

Hexadecimal_To_Binary = Hexadecimal_To_Binary & "0" & CStr(Val("&H" & Right(strHex, 2)))

End Function

'自定义“公历转农历”日期函数

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) = "虎": ShuXiang(3) = "兔": ShuXiang(4) = "龙": ShuXiang(5) = "蛇"

ShuXiang(6) = "马": ShuXiang(7) = "羊": ShuXiang(8) = "猴": ShuXiang(9) = "鸡": ShuXiang(10) = "狗": ShuXiang(11) = "猪"

'农历日期名

DayName(0) = "*": DayName(1) = "初一": DayName(2) = "初二": DayName(3) = "初三": DayName(4) = "初四": DayName(5) = "初五"

DayName(6) = "初六": DayName(7) = "初七": DayName(8) = "初八": DayName(9) = "初九": DayName(10) = "初十": DayName(11) = "十一"

DayName(12) = "十二": DayName(13) = "十三": DayName(14) = "十四": DayName(15) = "十五": DayName(16) = "十六": DayName(17) = "十七"

DayName(18) = "十八": DayName(19) = "十九": DayName(20) = "二十": DayName(21) = "廿一": DayName(22) = "廿二"

DayName(23) = "廿三": DayName(24) = "廿四": DayName(25) = "廿五": DayName(26) = "廿六": DayName(27) = "廿七"

DayName(28) = "廿八": DayName(29) = "廿九": DayName(30) = "三十"

'农历月份名

MonName(0) = "*": MonName(1) = "正": MonName(2) = "二": MonName(3) = "三": MonName(4) = "四": MonName(5) = "五"

MonName(6) = "六": MonName(7) = "七": MonName(8) = "八": MonName(9) = "九": MonName(10) = "十": MonName(11) = "冬"

MonName(12) = "腊"

'公历每月前面的天数(在这里，MonthAdd(0)代表的是1月前面的天数、MonthAdd(1)代表的是2月前面的天数、、MonthAdd(2)代表的是3月 _

前面的天数等等)

MonthAdd(0) = 0: MonthAdd(1) = 31: MonthAdd(2) = 59: MonthAdd(3) = 90: MonthAdd(4) = 120: MonthAdd(5) = 151

MonthAdd(6) = 181: MonthAdd(7) = 212: MonthAdd(8) = 243: MonthAdd(9) = 273: MonthAdd(10) = 304: MonthAdd(11) = 334

'农历数据(NongliData保存了从1921年（NongliData(0)）以来到2021年（NongliData(99)）这100年的农历数据)。必须说明下这些数据 _

的含义，比如NongliData(0)=2635是1921年的数据，二进制是101001001011，这12位二进制的各位，分别对应农历的12个月：从左往右的 _

第一位是1，那么1921年正月是30天；第二位是0，那么1921年二月是29天；第三位是1，那么1921年三月是30天；第四位是0，那么1921年 _

四月是29天......以此类推。这些数据不是通过某种算法算出来的，而是根据以1921年作为基准情况下从1921年～2021年这100年的每年的 _

农历1～12个月中各月的天数在30或者29跳变情况而形成的12位或13位二进制(考虑到有可能某些年份要闰月的情况)+闰月的信息二进制形成 _

的序列，由这个序列再形成简化的十进制。 _

下面，我们举个例子详细说明下：在下面的NongliData上，我们会发现有很多大于4095的数值，比如1922年是NongliData(1)=333387，二 _

进制是1010001011001001011，1922年农历有13个月（包括农历闰月），去掉头101000，1011001001011是13个月份数据，而闰几月 _

就在101000中保存，删掉后3个0，101就是1922年闰月的月份，101是十进制的5。至于中间的三个零是为了编程的时候好对齐才多出来的。 _

多亏了网上的好心人总结出来了，我们才得以方便使用这些又原12位的二进制转化为简化的十进制各年农历数据

NongliData(0) = 2635: NongliData(1) = 333387: NongliData(2) = 1701: NongliData(3) = 1748: NongliData(4) = 267701

NongliData(5) = 694: NongliData(6) = 2391: NongliData(7) = 133423: NongliData(8) = 1175: NongliData(9) = 396438

NongliData(10) = 3402: NongliData(11) = 3749: NongliData(12) = 331177: NongliData(13) = 1453: NongliData(14) = 694

NongliData(15) = 201326: NongliData(16) = 2350: NongliData(17) = 465197: NongliData(18) = 3221: NongliData(19) = 3402

NongliData(20) = 400202: NongliData(21) = 2901: NongliData(22) = 1386: NongliData(23) = 267611: NongliData(24) = 605

NongliData(25) = 2349: NongliData(26) = 137515: NongliData(27) = 2709: NongliData(28) = 464533: NongliData(29) = 1738

NongliData(30) = 2901: NongliData(31) = 330421: NongliData(32) = 1242: NongliData(33) = 2651: NongliData(34) = 199255

NongliData(35) = 1323: NongliData(36) = 529706: NongliData(37) = 3733: NongliData(38) = 1706: NongliData(39) = 398762

NongliData(40) = 2741: NongliData(41) = 1206: NongliData(42) = 267438: NongliData(43) = 2647: NongliData(44) = 1318

NongliData(45) = 204070: NongliData(46) = 3477: NongliData(47) = 461653: NongliData(48) = 1386: NongliData(49) = 2413

NongliData(50) = 330077: NongliData(51) = 1197: NongliData(52) = 2637: NongliData(53) = 268877: NongliData(54) = 3365

NongliData(55) = 531109: NongliData(56) = 2900: NongliData(57) = 2922: NongliData(58) = 398042: NongliData(59) = 2395

NongliData(60) = 1179: NongliData(61) = 267415: NongliData(62) = 2635: NongliData(63) = 661067: NongliData(64) = 1701

NongliData(65) = 1748: NongliData(66) = 398772: NongliData(67) = 2742: NongliData(68) = 2391: NongliData(69) = 330031

NongliData(70) = 1175: NongliData(71) = 1611: NongliData(72) = 200010: NongliData(73) = 3749: NongliData(74) = 527717

NongliData(75) = 1452: NongliData(76) = 2742: NongliData(77) = 332397: NongliData(78) = 2350: NongliData(79) = 3222

NongliData(80) = 268949: NongliData(81) = 3402: NongliData(82) = 3493: NongliData(83) = 133973: NongliData(84) = 1386

NongliData(85) = 464219: NongliData(86) = 605: NongliData(87) = 2349: NongliData(88) = 334123: NongliData(89) = 2709

NongliData(90) = 2890: NongliData(91) = 267946: NongliData(92) = 2773: NongliData(93) = 592565: NongliData(94) = 1210

NongliData(95) = 2651: NongliData(96) = 395863: NongliData(97) = 1323: NongliData(98) = 2707: NongliData(99) = 265877

'生成当前公历年、月、日 ==> GongliStr

curYear = Year(curTime)

curMonth = Month(curTime)

curDay = Day(curTime)

GongliStr = curYear & "年"

If (curMonth < 10) Then '判断当前月是否小于10月，如果小于10月的话，当前月的位数应该补零占位，形式上形成两位的月份

GongliStr = GongliStr & "0" & curMonth & "月"

Else '否则，当前月大于等于10，就直接形成两位的月份

GongliStr = GongliStr & curMonth & "月"

End If

If (curDay < 10) Then '判断当前日子是否小于10日，如果小于10日的话，当前日子的位数应该补零占位，形式上形成两位的日子

GongliStr = GongliStr & "0" & curDay & "日"

Else '否则，当前日子大于等于10，就直接形成两位的日子

GongliStr = GongliStr & curDay & "日"

End If

'用日期差函数返回相差天数的形式计算到初始时间1921年2月8日的天数：1921-2-8(正月初一)

If curTime >= CDate("1921-2-8") And curTime <= CDate("2021-2-8") Then

TheDate = DateDiff("d", CDate("1921-2-8"), curTime) + 1

Else

NongLi = ""

Exit Function

End If

If ((curYear Mod 4) = 0 And curMonth > 2) Then

TheDate = TheDate + 1

End If

'计算农历天干、地支、月、日

isEnd = 0 '是否结束，为1表示结束，为0表示未结束

m = 0

Do

If (NongliData(m) < 4095) Then '以农历数据为4095这个农历年份为基准判断当前年份相比之下农历数据换算成二进制位数是否 _

低于12位的情况

k = 11 '小于4095，则该年份的农历数据换算成二进制数11位(不足12位)

Else

k = 12 '大于等于4095，则该年份的农历数据换算成二进制数12位(满满的12位)

End If

n = k

Do

If (n < 0) Then

Exit Do

End If

'获取NongliData(m)的第n个二进制位的值

bit = NongliData(m)

For i = 1 To n Step 1

bit = Int(bit / 2) '辗转除2取整

Next

bit = bit Mod 2 '除2取余

If (TheDate <= 29 + bit) Then '日期差的天数都严重小于等于(29+bit)天，则立即置结束标志isEnd为1，并退出Do循环

isEnd = 1

Exit Do

End If

TheDate = TheDate - 29 - bit '修正日期差天数

n = n - 1 '遍历操作一次获取每位二进制位值，n修正一次自身(自减一次)

Loop

If (isEnd = 1) Then

Exit Do

End If

m = m + 1 'm为农历数据数组的下标，每循环一次，增加一次，轮转到下一年份的农历数据准备处理

Loop

'修正当前的年、月、日

curYear = 1921 + m

curMonth = k - n + 1

curDay = TheDate

'如果该年份的农历数据对应的二进制位数为12位，则进一步做如下处理

If (k = 12) Then

If (curMonth = (Int(NongliData(m) / 65536) + 1)) Then 'If (curMonth=(Int(NongliData(m)/65536)+1))润 _

几月的问题，比如1922年年份的农历数据是333387，由333387/65536取整得5，表达1922年是闰5月

curMonth = 1 - curMonth '修正当前月

ElseIf (curMonth > (Int(NongliData(m) / 65536) + 1)) Then

curMonth = curMonth - 1 '修正当前月

End If

End If

'生成农历天干、地支、属相 ==> NongliStr

NongliStr = "农历" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "年"

NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ")"

'生成农历月、日 ==> NongliDayStr

If (curMonth < 1) Then

NongliDayStr = "闰" & MonName(-1 * curMonth)

Else

NongliDayStr = MonName(curMonth)

End If

NongliDayStr = NongliDayStr & "月"

NongliDayStr = NongliDayStr & DayName(curDay)

NongLi = NongliStr & NongliDayStr

End Function

二、在Excel前端进行公历-->农历的转换

(一)用工作表函数的形式实现

可以用如下公式实现

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(日期,"[$-130000][dbnum1]yyyy年m月"&IF((--TEXT(日期,"[$-130000]d"))<11,"初","")&"d"),"年一月","年正月"),"十二月","腊月"),"十一月","冬月")

比如，下图即是用该公式解决的公历到农历的转换

在E2单元格输入数据2019-2-5，然后在选择E11单元格，在公式编辑栏中输入公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(E2,"[$-130000][dbnum1]yyyy年m月"&IF((--TEXT(E2,"[$-130000]d"))<11,"初","")&"d"),"年一月","年正月"),"十二月","腊月"),"十一月","冬月")。如下图所示

图1 输入上述的公式

在上面的公式编辑栏输入公式后，点击“√”输入确认按钮，完成农历求值。如下图所示

图2 用“"[$-130000][dbnum1]yyyy年m月"”格式完成的公历-->农历转换

尽快该公式初步可以解决公历和农历的转换，但是对于前面我们说过的闰月的情况，是无法解决实现的，所以上面的公式是有缺陷的，鉴于这种情况，我们还得在VBA环境下自定义公历转农历的函数实现更靠谱一些。

下面，我们就以刚才VBA后台里自定义的函数形式应用于公历转农历的的操作吧。

(二)运用自定义公历转农历函数实现公历-->农历转换

1、在Excel前端运用自定义GetYLDate()函数实现公历-->农历的转换

在E2单元格输入数据2019-2-5，然后在选择F3单元格，在公式编辑栏中输入公式=GetYLDate(E2)。如下图所示

图3 输入自定义函数=GetYLDate(E2)准备求农历

在上面的公式编辑栏输入公式后，点击“√”输入确认按钮，完成农历求值。如下图所示

图4 用自定义GetYLDate()函数求得的农历值

2、在Excel前端运用自定义NongLi()函数实现公历-->农历的转换

选择F6单元格，在公式编辑栏中输入公式=NongLi(F6)。如下图所示

图5 输入自定义函数=NongLi(F6)准备求农历

在上面的公式编辑栏输入公式后，点击“√”输入确认按钮，完成农历求值。如下图所示

图6 用自定义NongLi()函数求得的农历值

三、在Excel前端进行农历-->公历的转换

关于农历转公历，不是简单的将农历字符串粘贴到农历转公历的函数参数中去，而是要实现人工拆解(当然认为麻烦，可以用查找表的方法进行将农历的汉字字符串翻译成为阿拉伯数字的形式也可)为数字的年、月、日。比如“二○一九年正月初一”可以拆解为"2019,2,5"的形式。下面我们就运用农历转公历的自定义函数来实现农历转公历吧。如下图所示

图7 用自定义GetDate()函数求得的公历值

前面，我们已经给大家分享了万年历的制作，有粉丝评论留言说没有农历的效果，下期我们将为大家分享用本期的方法实现完美的带农历的万年历作品，希望大家多多关注。

好了，本期的公历农历互转问题就给大家分享到这里，希望对大家的工作有所帮助哦。

最后，非常感谢给为粉丝的长期关注(头条号：跟我学Office高级办公)、推广和点评哦！