excel阴阳历转换（电子表格中怎样将农历转换为公历）

本文目录

• 电子表格中怎样将农历转换为公历
• 如何在EXCEL中实现生日阳历到阴历的批量转换
• WPS表格怎么将公历转化成农历日期
• Excel 中阳历日期如何转换成阴历日Ɯ

电子表格中怎样将农历转换为公历

1、农历转换为公历，首先需要一个农历和公历的对照表2、假设对照表的格式为：A列为农历，B列为公历。3、此时可以用VLOOKUP函数进行转换，公式为：=VLOOKUP(需要转换的农历日期,A:B,2,0)4、特别注意：需要转换的农历日期必须与对照表中A列格式一致。

如何在EXCEL中实现生日阳历到阴历的批量转换

Option ExplicitDim WeekName(7), MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), MonName(12)Dim curTime, curYear, curMonth, curDay, curWeekdayDim GongliStr, WeekdayStr, NongliStr, NongliDayStrDim i, m, n, k, isEnd, bit, TheDateDim year, month, day, grid, grid_dateSub datetest()’这是转换样例 Debug.Print calendar("2011-8-15")End SubFunction calendar(grid)’转换函数grid_date = gridyear = Mid(grid_date, 1, 4)month = Mid(grid_date, 6, 1)day = Mid(grid_date, 8, 2)’星期名WeekName(0) = " * "WeekName(1) = "星期日"WeekName(2) = "星期一"WeekName(3) = "星期二"WeekName(4) = "星期三"WeekName(5) = "星期四"WeekName(6) = "星期五"WeekName(7) = "星期六"’天干名称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) = 0MonthAdd(1) = 31MonthAdd(2) = 59MonthAdd(3) = 90MonthAdd(4) = 120MonthAdd(5) = 151MonthAdd(6) = 181MonthAdd(7) = 212MonthAdd(8) = 243MonthAdd(9) = 273MonthAdd(10) = 304MonthAdd(11) = 334’农历数据NongliData(0) = 2635NongliData(1) = 333387NongliData(2) = 1701NongliData(3) = 1748NongliData(4) = 267701NongliData(5) = 694NongliData(6) = 2391NongliData(7) = 133423NongliData(8) = 1175NongliData(9) = 396438NongliData(10) = 3402NongliData(11) = 3749NongliData(12) = 331177NongliData(13) = 1453NongliData(14) = 694NongliData(15) = 201326NongliData(16) = 2350NongliData(17) = 465197NongliData(18) = 3221NongliData(19) = 3402NongliData(20) = 400202NongliData(21) = 2901NongliData(22) = 1386NongliData(23) = 267611NongliData(24) = 605NongliData(25) = 2349NongliData(26) = 137515NongliData(27) = 2709NongliData(28) = 464533NongliData(29) = 1738NongliData(30) = 2901NongliData(31) = 330421NongliData(32) = 1242NongliData(33) = 2651NongliData(34) = 199255NongliData(35) = 1323NongliData(36) = 529706NongliData(37) = 3733NongliData(38) = 1706NongliData(39) = 398762NongliData(40) = 2741NongliData(41) = 1206NongliData(42) = 267438NongliData(43) = 2647NongliData(44) = 1318NongliData(45) = 204070NongliData(46) = 3477NongliData(47) = 461653NongliData(48) = 1386NongliData(49) = 2413NongliData(50) = 330077NongliData(51) = 1197NongliData(52) = 2637NongliData(53) = 268877NongliData(54) = 3365NongliData(55) = 531109NongliData(56) = 2900NongliData(57) = 2922NongliData(58) = 398042NongliData(59) = 2395NongliData(60) = 1179NongliData(61) = 267415NongliData(62) = 2635NongliData(63) = 661067NongliData(64) = 1701NongliData(65) = 1748NongliData(66) = 398772NongliData(67) = 2742NongliData(68) = 2391NongliData(69) = 330031NongliData(70) = 1175NongliData(71) = 1611NongliData(72) = 200010NongliData(73) = 3749NongliData(74) = 527717NongliData(75) = 1452NongliData(76) = 2742NongliData(77) = 332397NongliData(78) = 2350NongliData(79) = 3222NongliData(80) = 268949NongliData(81) = 3402NongliData(82) = 3493NongliData(83) = 133973NongliData(84) = 1386NongliData(85) = 464219NongliData(86) = 605NongliData(87) = 2349NongliData(88) = 334123NongliData(89) = 2709NongliData(90) = 2890NongliData(91) = 267946NongliData(92) = 2773NongliData(93) = 592565NongliData(94) = 1210NongliData(95) = 2651NongliData(96) = 395863NongliData(97) = 1323NongliData(98) = 2707NongliData(99) = 265877’生成当前公历年、月、日 ==》 GongliStrcurYear = yearcurMonth = monthcurDay = dayGongliStr = curYear & "年"If (curMonth 《 10) Then GongliStr = GongliStr & "0" & curMonth & "月"Else GongliStr = GongliStr & curMonth & "月"End IfIf (curDay 《 10) Then GongliStr = GongliStr & "0" & curDay & "日"Else GongliStr = GongliStr & curDay & "日"End If’生成当前公历星期 ==》 WeekdayStrcurWeekday = Weekday(curTime)WeekdayStr = WeekName(curWeekday)’计算到初始时间1921年2月8日的天数：1921-2-8(正月初一)TheDate = (curYear - 1921) * 365 + Int((curYear - 1921) / 4) + curDay + MonthAdd(curMonth - 1) - 38If ((curYear Mod 4) = 0 And curMonth 》 2) Then TheDate = TheDate + 1End If’计算农历天干、地支、月、日isEnd = 0m = 0Do 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 + 1LoopcurYear = 1921 + mcurMonth = k - n + 1curDay = TheDateIf (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 IfEnd If’生成农历天干、地支、属相 ==》 NongliStrNongliStr = "农历" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "年"NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ""’生成农历月、日 ==》 NongliDayStrIf (curMonth 《 1) Then NongliDayStr = "闰" & MonName(-1 * curMonth)Else NongliDayStr = MonName(curMonth)End IfNongliDayStr = NongliDayStr & "月"NongliDayStr = NongliDayStr & DayName(curDay)calendar = NongliStr & NongliDayStr & ")"End Function

WPS表格怎么将公历转化成农历日期

excel公历转农历方法一：使用text函数

假设A列是公历日期，A1是公历日期，我们在B1单元格输入公式：=TEXT(A1,"dd"))=1,"初","")&"d")，下拉完成公历转农历。

excel公历转农历方法二：使用text+MID函数实现更完整的转换

A列仍然是公历，B1输入公式：

=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"m月d日")

即可。

这里增加了参数，表示数值以“一、二、三......”中文格式显示。

excel公历转农历方法三：使用text+MID+ CHOOSE+ YEAR函数

　　A1为公历，B1输入：

=CHOOSE(MOD(YEAR(A1)-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(A1)-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")&TEXT(A1,"d"))

这里可以对初几进行显示

excel公历转农历方法四：使用自定义函数

　上面介绍的excel公历转农历都是使用excel内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。

　　如下图所示，我们在B2输入公式：=nongli(A1)，即可。C列农历简称中C1公式是：=RIGHT(B1,LEN(B1)-8)。

　　下面是此自定义函数相关的代码：

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, curDayDim GongliStr, NongliStr, NongliDayStrDim 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) = 0MonthAdd(1) = 31MonthAdd(2) = 59MonthAdd(3) = 90MonthAdd(4) = 120MonthAdd(5) = 151MonthAdd(6) = 181MonthAdd(7) = 212MonthAdd(8) = 243MonthAdd(9) = 273MonthAdd(10) = 304MonthAdd(11) = 334’农历数据NongliData(0) = 2635NongliData(1) = 333387NongliData(2) = 1701NongliData(3) = 1748NongliData(4) = 267701NongliData(5) = 694NongliData(6) = 2391NongliData(7) = 133423NongliData(8) = 1175NongliData(9) = 396438NongliData(10) = 3402NongliData(11) = 3749NongliData(12) = 331177NongliData(13) = 1453NongliData(14) = 694NongliData(15) = 201326NongliData(16) = 2350NongliData(17) = 465197NongliData(18) = 3221NongliData(19) = 3402NongliData(20) = 400202NongliData(21) = 2901NongliData(22) = 1386NongliData(23) = 267611NongliData(24) = 605NongliData(25) = 2349NongliData(26) = 137515NongliData(27) = 2709NongliData(28) = 464533NongliData(29) = 1738NongliData(30) = 2901NongliData(31) = 330421NongliData(32) = 1242NongliData(33) = 2651NongliData(34) = 199255NongliData(35) = 1323NongliData(36) = 529706NongliData(37) = 3733NongliData(38) = 1706NongliData(39) = 398762NongliData(40) = 2741NongliData(41) = 1206NongliData(42) = 267438NongliData(43) = 2647NongliData(44) = 1318NongliData(45) = 204070NongliData(46) = 3477NongliData(47) = 461653NongliData(48) = 1386NongliData(49) = 2413NongliData(50) = 330077NongliData(51) = 1197NongliData(52) = 2637NongliData(53) = 268877NongliData(54) = 3365NongliData(55) = 531109NongliData(56) = 2900NongliData(57) = 2922NongliData(58) = 398042NongliData(59) = 2395NongliData(60) = 1179NongliData(61) = 267415NongliData(62) = 2635NongliData(63) = 661067NongliData(64) = 1701NongliData(65) = 1748NongliData(66) = 398772NongliData(67) = 2742NongliData(68) = 2391NongliData(69) = 330031NongliData(70) = 1175NongliData(71) = 1611NongliData(72) = 200010NongliData(73) = 3749NongliData(74) = 527717NongliData(75) = 1452NongliData(76) = 2742NongliData(77) = 332397NongliData(78) = 2350NongliData(79) = 3222NongliData(80) = 268949NongliData(81) = 3402NongliData(82) = 3493NongliData(83) = 133973NongliData(84) = 1386NongliData(85) = 464219NongliData(86) = 605NongliData(87) = 2349NongliData(88) = 334123NongliData(89) = 2709NongliData(90) = 2890NongliData(91) = 267946NongliData(92) = 2773NongliData(93) = 592565NongliData(94) = 1210NongliData(95) = 2651NongliData(96) = 395863NongliData(97) = 1323NongliData(98) = 2707NongliData(99) = 265877’生成当前公历年、月、日 ==》 GongliStrcurYear = Year(curTime)curMonth = Month(curTime)curDay = Day(curTime)GongliStr = curYear & "年"If (curMonth 《 10) ThenGongliStr = GongliStr & "0" & curMonth & "月"ElseGongliStr = GongliStr & curMonth & "月"End IfIf (curDay 《 10) ThenGongliStr = GongliStr & "0" & curDay & "日"ElseGongliStr = GongliStr & curDay & "日"End If’计算到初始时间1921年2月8日的天数：1921-2-8(正月初一)TheDate = (curYear - 1921) * 365 + Int((curYear - 1921) / 4) + curDay + MonthAdd(curMonth - 1) - 38If ((curYear Mod 4) = 0 And curMonth 》 2) ThenTheDate = TheDate + 1End If’计算农历天干、地支、月、日isEnd = 0m = 0DoIf (NongliData(m) 《 4095) Thenk = 11Elsek = 12End Ifn = kDoIf (n 《 0) ThenExit DoEnd If’获取NongliData(m)的第n个二进制位的值bit = NongliData(m)For i = 1 To n Step 1bit = Int(bit / 2)Nextbit = bit Mod 2If (TheDate 《= 29 + bit) ThenisEnd = 1Exit DoEnd IfTheDate = TheDate - 29 - bitn = n - 1LoopIf (isEnd = 1) ThenExit DoEnd Ifm = m + 1LoopcurYear = 1921 + mcurMonth = k - n + 1curDay = TheDateIf (k = 12) ThenIf (curMonth = (Int(NongliData(m) / 65536) + 1)) ThencurMonth = 1 - curMonthElseIf (curMonth 》 (Int(NongliData(m) / 65536) + 1)) ThencurMonth = curMonth - 1End IfEnd If’生成农历天干、地支、属相 ==》 NongliStrNongliStr = "农历" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "年"NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ")"’生成农历月、日 ==》 NongliDayStrIf (curMonth 《 1) ThenNongliDayStr = "闰" & MonName(-1 * curMonth)ElseNongliDayStr = MonName(curMonth)End IfNongliDayStr = NongliDayStr & "月"NongliDayStr = NongliDayStr & DayName(curDay)NongLi = NongliStr & NongliDayStrEnd Function

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

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

一、使用工具：excel2010

二、操作步骤：

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

=CHOOSE(MOD(YEAR(LEFT(TEXT(A1,"d日"))

如图：

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