如何将公历转换为农历，这一直是一个大难题，没有什么好的办法，之前利用PQ通过获取API来得到公历对用的农历，但是无奈效率太低了

最近又有学员问到类似的问题，跟Deepseek沟通可下，竟然一次就得到正确的代码了，跟大家分享一下！

一、代码展示

我们没有将代码的结果设置为2025年六月初一这样的格式，为了方便计算，将结果改成了Excel可以识别的日期格式：2025/6/1 如果遇到闰月的话，则会在最前面添加的闰字，结果为：闰 2025/6/1

代码比较长，请大家在电脑端复制粘贴，手机端复制可能会不完整出现错误的情况

Public Function NongLi(Optional Gong As Date) As String
' If parameter is empty, return blank
If Gong = 0 Then
NongLi = ""
Exit Function
End If

Dim MonthAdd(11), NongliData(119)
Dim curTime, curYear, curMonth, curDay
Dim i, m, n, k, isEnd, bit, TheDate
Dim isLeapMonth As Boolean

' 获取当前系统时间
curTime = Gong

' 公历每月前面的天数
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(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
NongliData(100) = 1706
NongliData(101) = 2773
NongliData(102) = 133557
NongliData(103) = 1206
NongliData(104) = 398510
NongliData(105) = 2638
NongliData(106) = 3366
NongliData(107) = 335142
NongliData(108) = 3411
NongliData(109) = 1450
NongliData(110) = 200042
NongliData(111) = 2413
NongliData(112) = 723293
NongliData(113) = 1197
NongliData(114) = 2637
NongliData(115) = 399947
NongliData(116) = 3365
NongliData(117) = 3410
NongliData(118) = 334676
NongliData(119) = 2906

' 生成当前公历年、月、日
curYear = Year(curTime)
curMonth = Month(curTime)
curDay = Day(curTime)

' 计算到初始时间1921年2月8日的天数: 1921-2-8(正月初一)
TheDate = (curYear - 1921) * 365 + Int((curYear - 1921) / 4) + curDay + MonthAdd(curMonth - 1) - 38
If ((curYear Mod 4) = 0 And curMonth > 2) Then
TheDate = TheDate + 1
End If

' 计算农历天干、地支、月、日
isEnd = 0
m = 0
isLeapMonth = False

Do
If (NongliData(m) < 4095) Then
k = 11
Else
k = 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)
Next
bit = bit Mod 2

If (TheDate <= 29 + bit) Then
isEnd = 1
Exit Do
End If
TheDate = TheDate - 29 - bit
n = n - 1
Loop

If (isEnd = 1) Then
Exit Do
End If
m = m + 1
Loop

curYear = 1921 + m
curMonth = k - n + 1
curDay = TheDate

' 判断是否为闰月
If (k = 12) Then
Dim leapMonth As Integer
leapMonth = Int(NongliData(m) / 65536) + 1

If (curMonth = leapMonth) Then
isLeapMonth = True
curMonth = 1 - curMonth
ElseIf (curMonth > leapMonth) Then
curMonth = curMonth - 1
End If
End If

curMonth = Abs(curMonth)

' 返回农历日期 (格式: "[闰]YYYY/M/D")
If isLeapMonth Then
NongLi = "闰 " & curYear & "/" & curMonth & "/" & curDay
Else
NongLi = curYear & "/" & curMonth & "/" & curDay
End If
End Function

二、使用代码

首先打开Excel，按下快捷键ALT+F11调出VB窗口，之后点击【插入】找到【模块】新建一个模块，在右侧的空白区域，按下【Ctrl+V】直接粘贴即可

三、展示结果

使用方法就跟常规的函数是一样的输入函数的名称【NongLi】，然后点击对应的单元格，想向下填充即可，可以准确的识别是6月的闰月的

以上就是今天分享的全部内容，想要保存vba代码需要将文件的格式另存为XLSM格式才行，或者利用之前分享过的【加载宏】的方法永久保存代码，大家可以翻一下，之前都是发过的

想要提高工作效率，不想再求同事帮你解决各种Excel问题，可以了解下我的专栏，WPS用户也能使用，讲解了函数、图表、透视表、数据看板、AI做表格等功能，后期都会免费更新的