额,这个问题在问答里已经出现了几次,奈何真的没几个人会,可能是真的比较复杂,所以没什么愿意去写吧!我很好奇为什么非要用Excel去做纪年的转换呢……
今天我正好有空,我就给大家简单写一下。
首先,有两种方式能实现公历纪年和农历纪年的转换,在Excel默认的序列填充内容里并不能自动生出农历纪年,我们能实现的仅仅是转换,而不是自动出结果!
至于两种方式嘛,大家都知道,一种用公式做转换,另一种用VBA写自定义函数。
1.公式法:
公式一:如果公历日期在A1单元格,那么我们再B1单元格做转换,B1输入
=TEXT(A1,'[$-130000]yyyy年m月'&IF(LEN(--TEXT(A1,'[$-130000]dd'))=1,'初','')&'d'&'日')
公式得到结果如下:
这种方式转换的农历日期不太完美,仅能保证农历日期是对的,但是显示方式和公历的显示方法差别不大,但是公式简单,有点基础的小伙伴肯定能看懂。
公式二:如果公历日期在A1单元格,那么我们再B1单元格做转换,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日')
公式得到结果如下:
这种方式转换的农历日期似乎已经能满足大多数要求了,及包含里天干地支纪年法,也将数字的日期转换为中文了,但相对来说公式较为复杂,需要点技巧才能读懂,不过还好,我帮大家写好了,复制过去就能用了。弊端是有时候遇到农历闰月,这个公式就不灵了,其他还好!
最后就是大招了,这个大招呢,把公历日期完美转换,但也是最复杂的。如果你用的办公软件是WPS的话,那对不起了,你可能用不了这个方法。
2.自定义函数法:
鉴于很多小伙伴可能不懂,那么我就细致一点的说,自定义函数就是,我们自己根据需求去创建一个新的函数,以供我们计算使用。
步骤一:打开VBA,如果你的Excel功能区中没有【开发工具】,那么你需要在【文件】→【选项】→【自定义功能区】中勾选开发工具即可。
也可用快捷键Alt F11,打开VBA编辑器
步骤二:我们再上图所标识的区域内鼠标右键→插入→模块,得到模块1
步骤三:在【模块1】中录入我们的VBA代码。代码很长,我就不截长图了。这里我们就定义好了一个叫做NongLi的函数。
步骤四:代码也写好了,那么就去用起来吧!!!关闭VBA编辑器,回到Excel,如果公历日期在A1单元格,那么还在再B1单元格做转换。
这个神奇的方法会让你的公式很简单哦,=NongLi(A1)即可,这样的方式转换出的农历日期可以显示农历、天干地支纪年、生肖、农历闰月等等,是不是完美???
你一定会问,那特么代码你也不贴出来,我怎么知道怎么写?我这就贴,这就贴。
代码如下:
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) = 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
'生成当前公历年、月、日 ==> GongliStr
curYear = Year(curTime)
curMonth = Month(curTime)
curDay = Day(curTime)
GongliStr = curYear & '年'
If (curMonth < 10) Then
GongliStr = GongliStr & '0' & curMonth & '月'
Else
GongliStr = GongliStr & curMonth & '月'
End If
If (curDay < 10) Then
GongliStr = GongliStr & '0' & curDay & '日'
Else
GongliStr = GongliStr & curDay & '日'
End If
'计算到初始时间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
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
If (curMonth = (Int(NongliData(m) / 65536) 1)) Then
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文件保存时需要保存为*****.xlsm,这点很重要,千万注意。。。不然一切功亏一篑!!!
好了,我写完了。。。
我就知道会有一万个人赞我!!!
联系客服