在Excel中自定义公历转换农历函数2
Excel自定义求农历函数――nongli(公历日期,显示序号)
Option Base 1Dim rq As Integer '日期Dim y As Date '农历正月月初一的阳历日期Dim yts As Variant '农历每月的天数Dim yy(2) As Integer '农历闰月数、阳历闰年数（闰年为1，不闰年为0）Dim nl(3, 385) As String '阳历日期字符串、农历日期字符串、农历闰月字符串Function NONGLI(glrq As Date, nlr As Integer)Dim X As Integer, i As Integer, k As Integer, n1 As Integer, n2 As IntegerX = Year(glrq)If X < 1900 Or glrq > #1/28/2101# ThenNONGLI = "？"Exit FunctionEnd If'1、将X年的阴阳历等，通过运行程序2，装入数组If X < 2021 Then Call Array1(X, n1, glrq)If X > 2020 Then Call Array2(X, n1, glrq)'2、查找阳历日期所在数组的序号rqdi2bu: rq = 0If X = 1899 Thenrq = Day(glrq)ElseFor i = 1 To n1If nl(1, i) = glrq Then rq = i: Exit ForNext iEnd If'3、填写"农历日期"(包括节日、纪念日)Dim nongli1$, yr$, yuefen$, yf$, rizi$, rz$Dim jr1 As String, jr2 As String, jr3 As Stringnongli1 = nl(2, rq)'农历日期以"2014-2-1"或"2014-闰9-1"的形式表示yr = Strings.Right(nongli1, Strings.Len(nongli1) - 5) '农历日期以"2-1"或"闰9-1"形式表示yuefen = Strings.Left(yr, Strings.InStrRev(yr, "-") - 1) '农历的月份以"2"或"闰9"形式表示rizi = Strings.Right(yr, Strings.Len(yr) - Strings.InStrRev(yr, "-")) '农历的日子以"2"形式表示Dim yuefenB As Variant, yfB As VariantyuefenB = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, "闰2", "闰3", "闰4", "闰5", "闰6", "闰7", "闰8", "闰9", "闰10", "闰11", "闰12")yfB = Array("正月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "冬月", "腊月", "闰二月", "闰三月", "闰四月", "闰五月", "闰六月", "闰七月", "闰八月", "闰九月", "闰十月", "闰冬月", "闰腊月")For i = 1 To 23'农历的月份以汉字形式表示If yuefen = yuefenB(i) Then yf = yfB(i): Exit ForNext iDim rzB As VariantrzB = Array("初一", "初二", "初三", "初四", "初五", "初六", "初七", "初八", "初九", "初十", "十一", "十二", "十三", "十四", "十五", "十六", "十七", "十八", "十九", "二十", "廿一", "廿二","廿三", "廿四", "廿五", "廿六", "廿七", "廿八", "廿九", "三十")For i = 1 To 30If rizi = i Then rz = rzB(i): Exit For'农历的日子以汉字形式表示Next i'农历节日--jr1If nlr = 3 Then Call nljr(rizi, yuefen, yuefenB, yfB, yr, yts, jr1)'24节气--ji2Dim xiaohan As Datexiaohan = Int(365.242646137797 * Year(glrq) - 693953.924646684)If glrq = xiaohan Then jr2 = "小寒"Dim dahan As Datedahan = Int(365.242629416257 * Year(glrq) - 693939.16865395)If Year(glrq) = 2082 Then dahan = dahan + 1If glrq = dahan Then jr2 = "大寒"Dim lichun As Datelichun = Int(365.24259976737 * Year(glrq) - 693924.346732722)If glrq = lichun Then jr2 = "立春"Dim yushui As Dateyushui = Int(365.242502247697 * Year(glrq) - 693909.331831532)If Year(glrq) = 2059 Or Year(glrq) = 2092 Then yushui = yushui + 1If glrq = yushui Then jr2 = "雨水"Dim jingzhe As Datejingzhe = Int(365.242419549484 * Year(glrq) - 693894.233446856) If Year(glrq) = 2047 Then jingzhe = jingzhe + 1If glrq = jingzhe Then jr2 = "惊蛰"Dim chunfen As Datechunfen = Int(365.242305278251 * Year(glrq) - 693878.966116765) If Year(glrq) = 2051 Then chunfen = chunfen - 1If glrq = chunfen Then jr2 = "春分"Dim qingming As Dateqingming = Int(365.242254377632 * Year(glrq) - 693863.694715595) If glrq = qingming Then jr2 = "清明"Dim guyu As Dateguyu = Int(365.242150678344 * Year(glrq) - 693848.193860396)If Year(glrq) = 2045 Then guyu = guyu - 1If glrq = guyu Then jr2 = "谷雨"Dim lixia As Datelixia = Int(365.242041986455 * Year(glrq) - 693832.541539829)If Year(glrq) = 1973 Or Year(glrq) = 2035 Then lixia = lixia - 1If glrq = lixia Then jr2 = "立夏"Dim xiaoman As Datexiaoman = Int(365.241895042148 * Year(glrq) - 693816.712806842)If Year(glrq) = 2070 Then xiaoman = xiaoman - 1If glrq = xiaoman Then jr2 = "小满"Dim mangzhong As Datemangzhong = Int(365.241908822174 * Year(glrq) - 693801.095841903)If Year(glrq) = 2026 Or Year(glrq) = 2055 Or Year(glrq) = 2088 Then mangzhong = mangzhong -1If glrq = mangzhong Then jr2 = "芒种"Dim xiazhi As Datexiazhi = Int(365.242316100823 * Year(glrq) - 693786.181888162)If Year(glrq) = 2019 Or Year(glrq) = 2023 Or Year(glrq) = 2048 Or Year(glrq) = 2052 Or Year(glrq)= 2056 Or Year(glrq) = 2081 Or Year(glrq) = 2085 Or Year(glrq) = 2089 Then xiazhi = xiazhi - 1If glrq = xiazhi Then jr2 = "夏至"Dim xiaoshu As Datexiaoshu = Int(365.241837274251 * Year(glrq) - 693769.530669936)If Year(glrq) = 2078 Then xiaoshu = xiaoshu - 1If glrq = xiaoshu Then jr2 = "小暑"Dim dashu As Datedashu = Int(365.241703595146 * Year(glrq) - 693753.549346385)If glrq = dashu Then jr2 = "大暑"Dim liqiu As Dateliqiu = Int(365.241890113665 * Year(glrq) - 693738.222492901)If Year(glrq) = 2035 Or Year(glrq) = 2068 Or Year(glrq) = 2097 Then liqiu = liqiu - 1If glrq = liqiu Then jr2 = "立秋"Dim chushu As Datechushu = Int(365.242316100823 * Year(glrq) - 693723.45493336)If Year(glrq) = 2020 Or Year(glrq) = 2049 Or Year(glrq) = 2053 Then chushu = chushu - 1If glrq = chushu Then jr2 = "处暑"Dim bailu As Datebailu = Int(365.242316100823 * Year(glrq) - 693707.939588367)If glrq = bailu Then jr2 = "白露"Dim qiufen As Dateqiufen = Int(365.242085926645 * Year(glrq) - 693692.119710911)If glrq = qiufen Then jr2 = "秋分"Dim hanlu As Datehanlu = Int(365.242316100823 * Year(glrq) - 693677.304821888)If Year(glrq) = 2073 Then hanlu = hanlu - 1If glrq = hanlu Then jr2 = "寒露"Dim shuangjiang As Dateshuangjiang = Int(365.242316100823 * Year(glrq) - 693662.177281271)If glrq = shuangjiang Then jr2 = "霜降"Dim lidong As Datelidong = Int(365.242316100823 * Year(glrq) - 693647.185448183)If glrq = lidong Then jr2 = "立冬"Dim xiaoxue As Datexiaoxue = Int(365.242316100823 * Year(glrq) - 693632.293388525)If Year(glrq) = 1912 Then xiaoxue = xiaoxue - 1If glrq = xiaoxue Then jr2 = "小雪"Dim daxue As Datedaxue = Int(365.242199074074 * Year(glrq) - 693617.264427083)If Year(glrq) = 2020 Or Year(glrq) = 2053 Or Year(glrq) = 2082 Then daxue = daxue + 1If glrq = daxue Then jr2 = "大雪"Dim dongzhi As Datedongzhi = Int(365.242615913523 * Year(glrq) - 693603.343641496)If Year(glrq) = 2054 Or Year(glrq) = 2087 Then dongzhi = dongzhi + 1If glrq = dongzhi Then jr2 = "冬至一九第一天"For i = 10 To 73 Step 9IfMonth(glrq)<>12Thendongzhi=Int(365.242615913523*(Year(glrq)-1)-693603.343641496): If Year(glrq) - 1 = 2054 Or Year(glrq) - 1 = 2087 Then dongzhi = dongzhi +1If i = glrq - dongzhi + 1 ThenIf i = 10 Then jr2 = jr2 & "二九第一天": Exit ForIf i = 19 Then jr2 = jr2 & "三九第一天": Exit ForIf i = 28 Then jr2 = jr2 & "四九第一天": Exit ForIf i = 37 Then jr2 = jr2 & "五九第一天": Exit ForIf i = 46 Then jr2 = jr2 & "六九第一天": Exit ForIf i = 55 Then jr2 = jr2 & "七九第一天": Exit ForIf i = 64 Then jr2 = jr2 & "八九第一天": Exit ForIf i = 73 Then jr2 = jr2 & "九九第一天": Exit ForEnd IfNext i'公历节日--jr3If nlr = 3 Then Call gljr(glrq, jr3)'4、日期的天干地支di4bu: If nlr <> 5 Then GoTo di5buDim ganzhiB As VariantganzhiB = Array("甲子", "乙丑", "丙寅", "丁卯", "戊辰", "己巳", "庚午", "辛未", "壬申", "癸酉", "甲戌", "乙亥", "丙子", "丁丑", "戊寅", "己卯", "庚辰", "辛巳", "壬午", "癸未", "甲申", "乙酉", "丙戌", "丁亥", "戊子", "己丑", "庚寅", "辛卯", "壬辰", "癸巳", "甲午", "乙未", "丙申", "丁酉", "戊戌", "己亥", "庚子", "辛丑", "壬寅", "癸卯", "甲辰", "乙巳", "丙午", "丁未", "戊申","己酉", "庚戌", "辛亥", "壬子", "癸丑", "甲寅", "乙卯", "丙辰", "丁巳", "戊午", "己未", "庚申","辛酉", "壬戌", "癸亥")'(1)把农历年份的天干地支赋值于ngz，农历1984年是：甲子年Dim ns%, ngz$If X = Year(glrq) And glrq < lichun Then'春节后立春时，小于立春的日子为上一年：X-1ns = X - 1 - 1983ElseIf X = Year(glrq) - 1 And glrq >= lichun Then ns = X + 1 - 1983 '春节前立春时，大于等于立春的日子为下一年：X+1Elsens = X - 1983'其他日子为当年：XEnd Ifns = ns Mod 60If ns <= 0 Then ns = ns + 60 '年干支数ngz = ganzhiB(ns)'年干支'(2)把农历月份的天干地支赋值于ygzDim yfs%, ntgs%, ytgs%, ydzs%, ygzs%, ygz$If glrq < xiaohan Then'农历月份数'小寒yfs = 11ElseIf glrq < lichun Then yfs = 12'立春ElseIf glrq < jingzhe Then yfs = 1'惊蛰ElseIf glrq < qingming Then yfs = 2'清明ElseIf glrq < lixia Then yfs = 3'立夏ElseIf glrq < mangzhong Then yfs = 4'芒种ElseIf glrq < xiaoshu Then yfs = 5'小暑ElseIf glrq < liqiu Then yfs = 6'立秋ElseIf glrq < bailu Then yfs = 7'白露ElseIf glrq < hanlu Then yfs = 8'寒露ElseIf glrq < lidong Then yfs = 9'立冬ElseIf glrq < daxue Then yfs = 10'大雪Elseyfs = 11End Ifntgs = ns Mod 5: If ntgs = 0 Then ntgs = 5'年天干数（1—5）ytgs = (ntgs * 2 + yfs) Mod 10: If ytgs = 0 Then ytgs = 10'月天干数（口诀：年上起月不麻烦，月干周期为五年。
excel阴阳历转换公式
excel阴阳历转换公式Excel是一款功能强大的电子表格软件，在我们的日常工作和生活中经常会用到。
其中，Excel还提供了一些实用的公式和函数，方便我们进行各种复杂的计算和数据处理。
本文将介绍一种非常实用的公式——阴阳历转换公式，帮助我们在Excel中进行阳历和阴历的转换。
在Excel中，我们可以使用一些公式来进行阳历和阴历的转换。
这些公式可以帮助我们将日期从阳历转换为阴历，或者将日期从阴历转换为阳历。
我们来看一下如何将阳历日期转换为阴历日期。
在Excel中，我们可以使用公式“=阴历转阳历(年,月,日)”来实现这个功能。
其中，年、月和日分别代表阳历日期的年、月和日。
这个公式会将阳历日期转换为阴历日期，并返回一个阴历日期的字符串。
例如，我们要将阳历2022年10月1日转换为阴历日期，可以使用公式“=阴历转阳历(2022,10,1)”。
这个公式会返回一个字符串，表示阴历日期，例如“二零二二年九月初八”。
接下来，我们来看一下如何将阴历日期转换为阳历日期。
在Excel 中，我们可以使用公式“=阳历转阴历(年,月,日)”来实现这个功能。
其中，年、月和日分别代表阴历日期的年、月和日。
这个公式会将阴历日期转换为阳历日期，并返回一个阳历日期的字符串。
例如，我们要将阴历二零二二年九月初八转换为阳历日期，可以使用公式“=阳历转阴历(2022,9,8)”。
这个公式会返回一个字符串，表示阳历日期，例如“2022年10月1日”。
通过这两个公式，我们可以在Excel中方便地进行阳历和阴历的转换。
无论是需要将阳历日期转换为阴历日期，还是将阴历日期转换为阳历日期，只需要输入相应的年、月和日，然后使用相应的公式即可。
除了这两个基本的转换公式，Excel还提供了一些其他的相关公式和函数，用于处理阴阳历转换中的一些特殊情况。
例如，Excel中的公式“=判断闰年(年)”可以判断一个年份是否为闰年；公式“=计算农历年份天数(年)”可以计算一个阴历年份中的天数；公式“=计算农历年份月份天数(年,月)”可以计算一个阴历年份中某个月份的天数。
Excel自定义求农历函数——nongli(公历日期,显示序号)
Option Base 1Dim rq As Integer '日期Dim y As Date '农历正月月初一的阳历日期Dim yts As Variant '农历每月的天数Dim yy(2) As Integer '农历闰月数、阳历闰年数（闰年为1，不闰年为0）Dim nl(3, 385) As String '阳历日期字符串、农历日期字符串、农历闰月字符串Function NONGLI(glrq As Date, nlr As Integer)Dim X As Integer, i As Integer, k As Integer, n1 As Integer, n2 As IntegerX = Year(glrq)If X < 1900 Or glrq > #1/28/2101# ThenNONGLI = "？"Exit FunctionEnd If'1、将X年的阴阳历等，通过运行程序2，装入数组If X < 2021 Then Call Array1(X, n1, glrq)If X > 2020 Then Call Array2(X, n1, glrq)'2、查找阳历日期所在数组的序号rqdi2bu: rq = 0If X = 1899 Thenrq = Day(glrq)ElseFor i = 1 To n1If nl(1, i) = glrq Then rq = i: Exit ForNext iEnd If'3、填写"农历日期"(包括节日、纪念日)Dim nongli1$, yr$, yuefen$, yf$, rizi$, rz$Dim jr1 As String, jr2 As String, jr3 As Stringnongli1 = nl(2, rq) '农历日期以"2014-2-1"或"2014-闰9-1"的形式表示yr = Strings.Right(nongli1, Strings.Len(nongli1) - 5) '农历日期以"2-1"或"闰9-1"形式表示yuefen = Strings.Left(yr, Strings.InStrRev(yr, "-") - 1) '农历的月份以"2"或"闰9"形式表示rizi = Strings.Right(yr, Strings.Len(yr) - Strings.InStrRev(yr, "-")) '农历的日子以"2"形式表示Dim yuefenB As Variant, yfB As VariantyuefenB = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, "闰2", "闰3", "闰4", "闰5", "闰6", "闰7", "闰8", "闰9", "闰10", "闰11", "闰12")yfB = Array("正月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "冬月", "腊月", "闰二月", "闰三月", "闰四月", "闰五月", "闰六月", "闰七月", "闰八月", "闰九月", "闰十月", "闰冬月", "闰腊月")For i = 1 To 23 '农历的月份以汉字形式表示If yuefen = yuefenB(i) Then yf = yfB(i): Exit ForNext iDim rzB As VariantrzB = Array("初一", "初二", "初三", "初四", "初五", "初六", "初七", "初八", "初九", "初十", "十一", "十二", "十三", "十四", "十五", "十六", "十七", "十八", "十九", "二十", "廿一", "廿二", "廿三", "廿四", "廿五", "廿六", "廿七", "廿八", "廿九", "三十")For i = 1 To 30If rizi = i Then rz = rzB(i): Exit For '农历的日子以汉字形式表示Next i'农历节日--jr1If nlr = 3 Then Call nljr(rizi, yuefen, yuefenB, yfB, yr, yts, jr1)'24节气--ji2Dim xiaohan As Datexiaohan = Int(365.242646137797 * Year(glrq) - 693953.924646684)If glrq = xiaohan Then jr2 = "小寒"Dim dahan As Datedahan = Int(365.242629416257 * Year(glrq) - 693939.16865395)If Year(glrq) = 2082 Then dahan = dahan + 1If glrq = dahan Then jr2 = "大寒"Dim lichun As Datelichun = Int(365.24259976737 * Year(glrq) - 693924.346732722)If glrq = lichun Then jr2 = "立春"Dim yushui As Dateyushui = Int(365.242502247697 * Year(glrq) - 693909.331831532)If Year(glrq) = 2059 Or Year(glrq) = 2092 Then yushui = yushui + 1If glrq = yushui Then jr2 = "雨水"Dim jingzhe As Datejingzhe = Int(365.242419549484 * Year(glrq) - 693894.233446856)If Year(glrq) = 2047 Then jingzhe = jingzhe + 1If glrq = jingzhe Then jr2 = "惊蛰"Dim chunfen As Datechunfen = Int(365.242305278251 * Year(glrq) - 693878.966116765)If Year(glrq) = 2051 Then chunfen = chunfen - 1If glrq = chunfen Then jr2 = "春分"Dim qingming As Dateqingming = Int(365.242254377632 * Year(glrq) - 693863.694715595)If glrq = qingming Then jr2 = "清明"Dim guyu As Dateguyu = Int(365.242150678344 * Year(glrq) - 693848.193860396)If Year(glrq) = 2045 Then guyu = guyu - 1If glrq = guyu Then jr2 = "谷雨"Dim lixia As Datelixia = Int(365.242041986455 * Year(glrq) - 693832.541539829)If Year(glrq) = 1973 Or Year(glrq) = 2035 Then lixia = lixia - 1If glrq = lixia Then jr2 = "立夏"Dim xiaoman As Datexiaoman = Int(365.241895042148 * Year(glrq) - 693816.712806842)If Year(glrq) = 2070 Then xiaoman = xiaoman - 1If glrq = xiaoman Then jr2 = "小满"Dim mangzhong As Datemangzhong = Int(365.241908822174 * Year(glrq) - 693801.095841903)If Year(glrq) = 2026 Or Year(glrq) = 2055 Or Year(glrq) = 2088 Then mangzhong = mangzhong - 1If glrq = mangzhong Then jr2 = "芒种"Dim xiazhi As Datexiazhi = Int(365.242316100823 * Year(glrq) - 693786.181888162)If Year(glrq) = 2019 Or Year(glrq) = 2023 Or Year(glrq) = 2048 Or Year(glrq) = 2052 Or Year(glrq) = 2056 Or Year(glrq) = 2081 Or Year(glrq) = 2085 Or Year(glrq) = 2089 Then xiazhi = xiazhi - 1If glrq = xiazhi Then jr2 = "夏至"Dim xiaoshu As Datexiaoshu = Int(365.241837274251 * Year(glrq) - 693769.530669936)If Year(glrq) = 2078 Then xiaoshu = xiaoshu - 1If glrq = xiaoshu Then jr2 = "小暑"Dim dashu As Datedashu = Int(365.241703595146 * Year(glrq) - 693753.549346385)If glrq = dashu Then jr2 = "大暑"Dim liqiu As Dateliqiu = Int(365.241890113665 * Year(glrq) - 693738.222492901)If Year(glrq) = 2035 Or Year(glrq) = 2068 Or Year(glrq) = 2097 Then liqiu = liqiu - 1If glrq = liqiu Then jr2 = "立秋"Dim chushu As Datechushu = Int(365.242316100823 * Year(glrq) - 693723.45493336)If Year(glrq) = 2020 Or Year(glrq) = 2049 Or Year(glrq) = 2053 Then chushu = chushu - 1 If glrq = chushu Then jr2 = "处暑"Dim bailu As Datebailu = Int(365.242316100823 * Year(glrq) - 693707.939588367)If glrq = bailu Then jr2 = "白露"Dim qiufen As Dateqiufen = Int(365.242085926645 * Year(glrq) - 693692.119710911)If glrq = qiufen Then jr2 = "秋分"Dim hanlu As Datehanlu = Int(365.242316100823 * Year(glrq) - 693677.304821888)If Year(glrq) = 2073 Then hanlu = hanlu - 1If glrq = hanlu Then jr2 = "寒露"Dim shuangjiang As Dateshuangjiang = Int(365.242316100823 * Year(glrq) - 693662.177281271)If glrq = shuangjiang Then jr2 = "霜降"Dim lidong As Datelidong = Int(365.242316100823 * Year(glrq) - 693647.185448183)If glrq = lidong Then jr2 = "立冬"Dim xiaoxue As Datexiaoxue = Int(365.242316100823 * Year(glrq) - 693632.293388525)If Year(glrq) = 1912 Then xiaoxue = xiaoxue - 1If glrq = xiaoxue Then jr2 = "小雪"Dim daxue As Datedaxue = Int(365.242199074074 * Year(glrq) - 693617.264427083)If Year(glrq) = 2020 Or Year(glrq) = 2053 Or Year(glrq) = 2082 Then daxue = daxue + 1If glrq = daxue Then jr2 = "大雪"Dim dongzhi As Datedongzhi = Int(365.242615913523 * Year(glrq) - 693603.343641496)If Year(glrq) = 2054 Or Year(glrq) = 2087 Then dongzhi = dongzhi + 1If glrq = dongzhi Then jr2 = "冬至一九第一天"For i = 10 To 73 Step 9If Month(glrq) <> 12 Then dongzhi = Int(365.242615913523 * (Year(glrq) - 1) - 693603.343641496): If Year(glrq) - 1 = 2054 Or Year(glrq) - 1 = 2087 Then dongzhi = dongzhi + 1If i = glrq - dongzhi + 1 ThenIf i = 10 Then jr2 = jr2 & "二九第一天": Exit ForIf i = 19 Then jr2 = jr2 & "三九第一天": Exit ForIf i = 28 Then jr2 = jr2 & "四九第一天": Exit ForIf i = 37 Then jr2 = jr2 & "五九第一天": Exit ForIf i = 46 Then jr2 = jr2 & "六九第一天": Exit ForIf i = 55 Then jr2 = jr2 & "七九第一天": Exit ForIf i = 64 Then jr2 = jr2 & "八九第一天": Exit ForIf i = 73 Then jr2 = jr2 & "九九第一天": Exit ForEnd IfNext i'公历节日--jr3If nlr = 3 Then Call gljr(glrq, jr3)'4、日期的天干地支di4bu: If nlr <> 5 Then GoTo di5buDim ganzhiB As VariantganzhiB = Array("甲子", "乙丑", "丙寅", "丁卯", "戊辰", "己巳", "庚午", "辛未", "壬申", "癸酉", "甲戌", "乙亥", "丙子", "丁丑", "戊寅", "己卯", "庚辰", "辛巳", "壬午", "癸未", "甲申", "乙酉", "丙戌", "丁亥", "戊子", "己丑", "庚寅", "辛卯", "壬辰", "癸巳", "甲午", "乙未", "丙申", "丁酉", "戊戌", "己亥", "庚子", "辛丑", "壬寅", "癸卯", "甲辰", "乙巳", "丙午", "丁未", "戊申", "己酉", "庚戌", "辛亥", "壬子", "癸丑", "甲寅", "乙卯", "丙辰", "丁巳", "戊午", "己未", "庚申", "辛酉", "壬戌", "癸亥")'(1)把农历年份的天干地支赋值于ngz，农历1984年是：甲子年Dim ns%, ngz$If X = Year(glrq) And glrq < lichun Then '春节后立春时，小于立春的日子为上一年：X-1ns = X - 1 - 1983ElseIf X = Year(glrq) - 1 And glrq >= lichun Then ns = X + 1 - 1983 '春节前立春时，大于等于立春的日子为下一年：X+1Elsens = X - 1983 '其他日子为当年：XEnd Ifns = ns Mod 60If ns <= 0 Then ns = ns + 60 '年干支数ngz = ganzhiB(ns) '年干支'(2)把农历月份的天干地支赋值于ygzDim yfs%, ntgs%, ytgs%, ydzs%, ygzs%, ygz$If glrq < xiaohan Then '农历月份数'小寒yfs = 11ElseIf glrq < lichun Then yfs = 12 '立春ElseIf glrq < jingzhe Then yfs = 1 '惊蛰ElseIf glrq < qingming Then yfs = 2 '清明ElseIf glrq < lixia Then yfs = 3 '立夏ElseIf glrq < mangzhong Then yfs = 4 '芒种ElseIf glrq < xiaoshu Then yfs = 5 '小暑ElseIf glrq < liqiu Then yfs = 6 '立秋ElseIf glrq < bailu Then yfs = 7 '白露ElseIf glrq < hanlu Then yfs = 8 '寒露ElseIf glrq < lidong Then yfs = 9 '立冬ElseIf glrq < daxue Then yfs = 10 '大雪Elseyfs = 11End Ifntgs = ns Mod 5: If ntgs = 0 Then ntgs = 5 '年天干数（1—5）ytgs = (ntgs * 2 + yfs) Mod 10: If ytgs = 0 Then ytgs = 10 '月天干数（口诀：年上起月不麻烦，月干周期为五年。
公历转农历EXCEL宏讲解
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) = 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) = 1386NongliData(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) = 398772NongliData(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公历转农历的四种方法excel公历转农历方法一：使用text函数A列是公历日期，我们在B1单元格输入公式：=TEXT(A1,"[$-130000]yyyy年m月"&I F(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")，下拉完成公历转农历。
excel公历转农历方法二：使用text+MID函数A列仍然是公历，B1输入公式：=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-13 0000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-130000]e")-4, 12)+1,1)&"年"&TEXT(A1,"[$-130000][DBNum1]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,"[dbnum1][$-130000]年m月"&IF(--TEXT(A1, "[$-130000]d")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d"))excel公历转农历方法四：使用自定义函数上面介绍的excel公历转农历都是使用excel内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。
Excel中阳历转阴历的处理技巧-日历模板（2）
Excel中阳历转阴历的处理技巧-日历模板（2）使用Excel函数制作一个2023年日历（1）今天我们介绍日历制作中的日程及阴历处理。
日程及节假日安排先来看日程以及节假日安排。
上图是日程数据以及节假日和调休的数据，都记录在超级表中。
剩下的事情就是在日历当中进行展示在全年的日历当中展示很容易，只要用条件格式，将对应日期在表中用VLOOKUP函数检查，只要找到了活动这一列不为空，不返回错误值，那么就表示它有一个日程，就可以给单元格变色。
节假日也是如此，在节假日表中查找相应的日期，返回第二列，如果不返回错误值并且不返回空白单元格，就表该天是节假日，需要变色。
周末上班也是同样的方法。
在单月日历中显示需要用另外的逻辑，我们后面介绍。
阴历处理Excel提供了一种很简单的方式：自定义格式，来帮助我们将阳历转为阴历。
所以，我们可以通过TEXT函数来实现转换：•••lunar_day, TEXT(theday, "[$-130000]d"),lunar_month, TEXT(theday, "[$-13000]m"),lunar_year, TEXT(theday, "[$-13000]yyyy") 我们将一个标准日期转换为阴历的日，月，年。
但是工作并没有完成，因为这么转换后有几个问题：1.日期处理：转换后的日是1，2，3，4，....，但是日历中需要显示为大写，比如11，应该显示为十一，这可以通过隐藏函数NUMBERSTRING来实现。
不过还需要进行仔细处理：a.1~10之间的日期应该显示为初一~初十b.11~19转为大写后，显示为一十一等，应该去掉开始的一，显示为十一c.20~29转为大写后，显示为二十一等，但是中文习惯上写作廿一d.每月1号应该显示为月份，而不是初一2.月份处理：转换后的月份为1，2，3，4，5，... 12，13。
大写后是一，二，......，十，一十一，一十二，一十三等。
excel制作周历 农历阳历转换
步骤一，在Excel工作表界面下按<ALT+F11>组合键打开VBA窗口，进入VBA编辑环境，在其窗口的菜单栏上依次单击“插入”→“模块”，可在当前VBA工程中插入模块，并直接进入此模块的代码编辑区域。
可在此编辑区域中输入自定义函数的程序代码,如图：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) = 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) = 333387NongliData(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) = 204070NongliData(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) = 2709NongliData(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步骤2，单击窗口右上角的“关闭”按钮关闭VBA编辑窗口，返回到工作表窗口中。
Excel实现公历农历转换的三种方法
Excel实现公历农历转换的三种⽅法【导读】:有时候我们在输⼊⽇期数据的时候，往往需要输⼊农历的⽇期，但已经输⼊的⽇期是公历的⽇期，那么该怎么转换呢？接下来我们将为⼤家如何利⽤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()⽂本提取函数来获取天⼲地⽀。
在Excel中自定义公历转换农历函数2
#NAME? #NAME? #NAME?
阳历生日:阳历1975年5月6日出生,今年阳历生日时对应的阳历日期
#NAME?
End Type
Do
Loop
Loop
&HB4A43, &H4B5537 , &HAD54A, &H955ABF , &H4BA53, &HA5B48, &H652BBC , &H52B50, &HA9345, &H474AB9 ,_
&H6AA4C, &HAD541, &H24DAB6 , &H4B64A, &H69573D , &HA4E51, &HD2646, &H5E933A , &HD534D, &H5AA43, _
LunarDat a.leapmo nth = Int(ng / d)
ng = ng Mod d
d= &H80
mdata = Int(ng / d)
ng = ng Mod d
d= &H20
LunarDat a.sp_mon th = Int(ng / d)
LunarDat a.sp_day = ng Mod d
y= a.Month( 13)
Else
l_month = l_month +1
y= a.Month( l_month)
End If Loop
l_day = l_day + 1
lunar = l_year & "-" & l_month & "-" & l_day
excel公历转农历的四种方法
excel公历转农历的四种方法excel公历转农历方法一：使用text函数A列是公历日期，我们在B1单元格输入公式：=TEXT(A1,"[$-130000]yyyy年m月"&I F(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")，下拉完成公历转农历。
excel公历转农历方法二：使用text+MID函数A列仍然是公历，B1输入公式：=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-13 0000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-130000]e")-4, 12)+1,1)&"年"&TEXT(A1,"[$-130000][DBNum1]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,"[dbnum1][$-130000]年m月"&IF(--TEXT(A1, "[$-130000]d")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d"))excel公历转农历方法四：使用自定义函数上面介绍的excel公历转农历都是使用excel内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。
在excel中实现阳历转化阴历
步骤一，在Excel工作表界面下按<ALT+F11>组合键打开VBA窗口，进入VBA编辑环境，在其窗口的菜单栏上依次单击“插入”→“模块”，可在当前VBA工程中插入模块，并直接进入此模块的代码编辑区域。
可在此编辑区域中输入自定义函数的程序代码,如图：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) = 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) = 201326NongliData(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) = 2395NongliData(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'生成当前公历年、月、日==> GongliStr curYear = 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步骤2，单击窗口右上角的“关闭”按钮关闭VBA编辑窗口，返回到工作表窗口中。
Excel公历转农历阳历转阴历word精品文档15页
Excel公历转农历阳历转阴历word精品文档15页参看一：可以这样试一下：EXCEL2007中可以直接做到，例如TEXT(日期,"[$-130000]e-m-d")，可返回日期的阿拉伯数字的农历年月日，中文农历转换公式如下：B2单元格=MID("庚辛壬癸甲乙丙丁戊己",MOD(TEXT(A2,"[$-130000]e"),10)+1,1)&MID("申酉戌亥子丑寅卯辰巳午未",MOD(TEXT(A2,"[$-130000]e"),12)+1,1)&TEXT(A2,"[dbnum1][$-130000]年M月"&IF(-TEXT(A2,"[$-130000]d")<-10,,"初")&"D日")此公式仅EXCEL2007以使用，EXCEL2003及以下不能正常使用上面那个公式好像有问题，可以试一下这个：参看二：日期在A2,这个公式在B2=CHOOSE(MOD(YEAR(LEFT(TEXT(A2,"[$-130000]yyyy年mm 月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A2,"[$-130000]yyyy年mm 月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(LEFT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A2,"[$-130000]yyyy年mm 月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")&TEXT(A2,"[dbnum1][$-130000]年m月"&IF(--TEXT(A2,"[$-130000]d")<11,"初","")&TEXT(A2,"[dbnum1][$-130000]d日")) =CHOOSE(MOD(YEAR(LEFT(TEXT(A11,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A11,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A11,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(LEFT(TEXT(A11,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A11,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&RIGHT(TEXT(A11,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A11,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")&TEXT(A11,"[dbnum1][$-130000]年m月"&IF(--TEXT(A11,"[$-130000]d")<11,"初","")&TEXT(A11,"[dbnum1][$-130000]d"))Excel公历转农历（阳历转阴历）用的是excel中的自定义函数，代码如下：Public Function NongLi(Optional XX_DATE As Date)Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayNam e(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(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(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。
Excel的公历与农历转换
公历与农历的转换
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) = "六"
excel公历转农历的四种方法
excel公历转农历的四种方法excel公历转农历方法一：使用text函数A列是公历日期，我们在B1单元格输入公式：=TEXT(A1,"[$-130000]yyyy年m月"&I F(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")，下拉完成公历转农历。
excel公历转农历方法二：使用text+MID函数A列仍然是公历，B1输入公式：=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-13 0000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-130000]e")-4, 12)+1,1)&"年"&TEXT(A1,"[$-130000][DBNum1]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,"[dbnum1][$-130000]年m月"&IF(--TEXT(A1, "[$-130000]d")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d"))excel公历转农历方法四：使用自定义函数上面介绍的excel公历转农历都是使用excel内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。
阳历转阴历
1.虽然EXCEL中没有专门的阴阳历转换函数，却保留了一种格式“[$-130000]”，可以将阳历转换为阴历，如图使用文本函数TEXT，使用此格式，就可以进行转换：=TEXT(A1,"[$-130000]yyyy年m月d")2.不过这样转换出来的阴历不符合我国使用习惯，再加个格式，转换成汉字：=TEXT(A1,"[$-130000][dbnum1]yyyy年m月d")3.当阴历日小于等于“十”时习惯在前面加个“初”字，才比较顺口，所以再增加个条件判断：=TEXT(A1,"[$-130000][dbnum1]yyyy年m月"&IF((--TEXT(A1,"[$-130000]d"))<11,"初","")&"d")4.当阴历为“一月”时，习惯上不称之为“一月”，而称为“正月”，这样就需要对月份也进行判断，当月份为“一”时，将之改为“正”，不过使用条件函数判断，公式会较长，也可以直接使用替换函数，将“一月”替换为“正月”，但是因为“一月”、“十一月”的后两个字都是“一月”，直接替换会出错，所以我们可以将“年”也带进去，将“年一月”的替换为“年正月”：=SUBSTITUTE(TEXT(A1,"[$-130000][dbnum1]yyyy年m月"&IF((--TEXT(A1,"[$-130000]d"))<11,"初","")&"d"),"年一月","年正月")5. 5另外，习惯上将“十二月”称为“腊月”，可以再加个替换：=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"[$-130000][dbnum1]yyyy年m月"&IF((--TEXT(A1,"[$-130000]d"))<11,"初","")&"d"),"年一月","年正月"),"十二月","腊月")6. 6不过如果遇到阴历闰月，当年闰月后的月份就会增加一个月，这是公式无法妥善处理的。
公历转农历EXCEL宏
NongliData(2) = 1701
NongliData(3) = 1748
NongliData(4) = 267701
NongliData(5) = 694
NongliData(6) = 2391
NongliData(7) = 133423
NongliData(78) = 2350
NongliData(79) = 3222
NongliData(80) = 268949
NongliData(81) = 3402
NongliData(82) = 3493
NongliData(83) = 133973
NongliData(84) = 1386
GongliStr = GongliStr & "0" & curMonth & "月"
Else
GongliStr = GongliStr & curMonth & "月"
End If
If (curDay < 10) Then
GongliStr = GongliStr & "0" & curDay & "日"
公历转农历EXCEL宏.txt当你以为自己一无所有时，你至少还有时间，时间能抚平一切创伤，所以请不要流泪。能满足的期待，才值得期待；能实现的期望，才有价值。保持青春的秘诀，是有一颗不安分的心。不是生活决定何种品位，而是品位决定何种生活。Public Function NongLi(Optional XX_DATE As Date)
excel国历转农历表格
excel国历转农历表格
要将Excel中的公历日期转换为农历日期，通常需要使用宏或
公式来实现。
以下是一种常见的方法：
1. 首先，你需要在Excel中安装农历插件或者使用VBA宏来实
现公历到农历的转换。
如果你不熟悉VBA，可以在网上搜索并下载
农历插件，然后安装到Excel中。
2. 一旦安装好了插件或者准备好了VBA宏，你就可以使用公历
日期的单元格作为输入，然后在相应的农历日期单元格中得到转换
后的结果。
3. 如果你选择使用VBA宏，你可以编写一个简单的VBA函数来
实现公历转农历的功能。
这需要一定的VBA编程知识，但是一旦编
写完成，你就可以在Excel中直接调用这个函数来实现日期的转换。
4. 另一种方法是使用在线的农历转换工具，将公历日期转换为
农历日期，然后手动输入到Excel中。
这种方法虽然不够自动化，
但对于简单的转换任务可能会更加方便。
总之，要在Excel中将公历日期转换为农历日期，你可以选择安装插件、编写VBA宏或者使用在线工具来实现。
希望这些方法能够帮到你。
EXCEL VBA 实例：将日期转换成农历
EXCEL VBA 实例：将日期转换成农历
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) = "二十八" —转换成农历
excell农历和公历转换公式
excell农历和公历转换公式Excel是一款功能强大的电子表格软件，它不仅可以用于数据统计和分析，还可以进行日期转换，包括农历和公历的转换。
在Excel中，我们可以使用一些公式来实现农历和公历的转换。
下面将介绍一些常用的Excel公式，帮助大家在农历和公历之间进行转换。
一、农历转公历公式农历转公历是指将农历日期转换为公历日期。
在Excel中，可以使用以下公式来实现农历转公历的计算：```=DATE(YEAR(公历日期), 2, 1) + 农历天数 - 1```其中，公历日期是指要转换的公历日期，农历天数是指要转换的农历日期对应的天数。
通过这个公式，我们可以将农历日期转换为公历日期。
二、公历转农历公式公历转农历是指将公历日期转换为农历日期。
在Excel中，可以使用以下公式来实现公历转农历的计算：```=公历日期 - DATE(YEAR(公历日期), 2, 1) + 1```其中，公历日期是指要转换的公历日期。
通过这个公式，我们可以将公历日期转换为农历日期。
三、应用示例下面通过一个示例来演示如何在Excel中进行农历和公历的转换。
假设我们要将农历2022年正月初一转换为公历日期。
首先，我们需要知道农历2022年正月初一对应的公历日期是什么。
根据农历日历，我们知道2022年的农历正月初一对应的公历日期是2月1日。
接下来，我们可以使用上述的公式来进行转换。
在一个单元格中输入农历日期2022/1/1，然后在另一个单元格中输入以下公式：```=DATE(YEAR(A1), 2, 1) + A1 - 1```其中，A1是指包含农历日期的单元格。
按下回车键后，我们可以在这个单元格中得到公历日期2022/2/1，这就是农历2022年正月初一对应的公历日期。
类似地，如果我们要将公历日期2022/2/1转换为农历日期，我们可以在一个单元格中输入公历日期2022/2/1，然后在另一个单元格中输入以下公式：```=A1 - DATE(YEAR(A1), 2, 1) + 1```按下回车键后，我们可以在这个单元格中得到农历日期2022/1/1，这就是公历2022/2/1对应的农历日期。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
求阳历2006-11-1日对应的阴历#NAME?求阴历2006年正月初一对应的阳历#NAME?阳历1975年5月6日出生,今年阴历生日时对应的阳历日期#NAME?阳历生日:阳历1975年5月6日出生,今年阳历生日时对应的阳历日期#NAME?Dim d AsDim Month'1901-210LunarCalstartyeard = &H100ng = ng Md = &H80mdata = Ing = ng Md = &H20LunarDataLunarDatad = &H100i = 1DoLunarmdataIf dd = di = iLoopIf LunarD EnFu luna'Part = 0Dim a Asl_year =a = Lunarsp_date =If sp_datl_yeaa = Lsp_daEnd Ifl_day = Sl_month =IS_lunar_y = a.MonDo Whilel_dayIf l_If ISyElselyEnd ILoopl_day = llunar = lIf IS_lunlunar = C EnFu sola'IS_lunarDim a AsLunar_dats_year =For EachIf CNexta = Lunarsp_date =If Lunar_x = Lunartm = LunaFor i = 1x = xIf ixEnd INexts_date =solar = s EnFu lunaIf InquirInquilunarIf CDEnd Iflunarbirt EnFu solaIf InquirInquisolarIf CDEnd Ifsolarbirt En函数作用:阴阳历转换和阴阳历生日' 说明:适用于1901-2100年间示例:=lunar("2006-11-1")=solar("2006-1-1") 求阴历2006年正月初一对应的阳历=lunarbirth("1975-5-6") 阴历生日:阳历1975年5月6日出生,今年阴历生日时对应的阳历日期 =solarbirth("1975-5-6") 阳历生日:阳历1975年5月6日出生,今年阳历生日时对应的阳历日期'################################################################Type ConvDataAleapmonth As IntegerMonth(1 To 13) As Integersp_month As Integer 'Solar month of Spring Festivalsp_day As Integer 'Solar day of Spring FestivalEnd TypePrivate Function LunarData(q_year) As ConvDataADim d As LongDim Month(1 To 13) As Integer'1901-2100LunarCal = Array(&H4AE53, &HA5748, &H5526BD, &HD2650, &HD9544, &H46AAB9, &H56A4D, &H9AD42, &H6A4DBE, &HA4D52, &HD2546, &H5D52BA, &HB544E, &HD6A43, &H296D37, &H95B4B, &H74 &HA4B48, &H5B25BC, &H6A550, &H6D445, &H4ADAB8, &H2B64D, &H95742, &H2497B7, &H49 &HD4A51, &HEA546, &H56D4BA, &H5AD4E, &H2B644, &H393738, &H92E4B, &H7C96BF, &HC9 &H6DA53B, &HB554F, &H56A45, &H4AADB9, &H25D4D, &H92D42, &H2C95B6, &HA954A, &H7B &HB5546, &H555ABB, &H4DA4E, &HA5B43, &H352BB8, &H52B4C, &H8A953F, &HE9552, &H6A &HAB54F, &H4B645, &H4A5739, &HA574D, &H52642, &H3E9335, &HD9549, &H75AABE, &H56 &H54AEBB, &H4AD4F, &HA4D43, &H4D26B7, &HD254B, &H8D52BF, &HB5452, &HB6A47, &H69 &H49B45, &H4A4BB9, &HA4B4D, &HAB25C2, &H6A554, &H6D449, &H6ADA3D, &HAB651, &H93 &H4974F, &H64B44, &H36A537, &HEA54A, &H86B2BF, &H5AC53, &HAB647, &H5936BC, &H92 &H4D4AB8, &HD4A4C, &HDA541, &H25AA36, &H56A49, &H7AADBD, &H25D52, &H92D47, &H5C &HB4A43, &H4B5537, &HAD54A, &H955ABF, &H4BA53, &HA5B48, &H652BBC, &H52B50, &HA9 &H6AA4C, &HAD541, &H24DAB6, &H4B64A, &H69573D, &HA4E51, &HD2646, &H5E933A, &HD5 &H36B537, &H96D4B, &HB4AEBF, &H4AD53, &HA4D48, &H6D25BC, &HD254F, &HD5244, &H5D &H56D41, &H24ADB6, &H49B4A, &H7A4BBE, &HA4B51, &HAA546, &H5B52BA, &H6D24E, &HAD &H9374B, &H8497C1, &H49753, &H64B48, &H66A53C, &HEA54F, &H6B244, &H4AB638, &HAA &H3C9735, &HC9649, &H7D4ABD, &HD4A51, &HDA545, &H55AABA, &H56A4E, &HA6D43, &H45 &H8A95BF, &HA9553, &HB4A47, &H6B553B, &HAD54F, &H55A45, &H4A5D38, &HA5B4C, &H52 &H69349, &H7729BD, &H6AA51, &HAD546, &H54DABA, &H4B64E, &HA5743, &H452738, &HD2 &HD5252, &HDAA47, &H66B53B, &H56D4F, &H4AE45, &H4A4EB9, &HA4D4C, &HD1541, &H2D9 startyear = 1901ng = LunarCal(q_year - startyear)d = &H100000LunarData.leapmonth = Int(ng / d)ng = ng Mod dd = &H80mdata = Int(ng / d)ng = ng Mod dd = &H20LunarData.sp_month = Int(ng / d)LunarData.sp_day = ng Mod dd = &H1000i = 1DoLunarData.Month(i) = 29 + Int(mdata / d)mdata = mdata Mod dIf d = 1 Then Exit Dod = d / 2i = i + 1LoopIf LunarData.leapmonth = 0 Then LunarData.Month(i) = 0End FunctionFunction lunar(Solar_date As Date, Optional Part As Integer = 0) As String 'Part = 0, all; Part = 1, lunar year; Part = 2, lunar month; Part = 3, lunar day Dim a As ConvDataAl_year = Year(Solar_date)a = LunarData(l_year)sp_date = DateSerial(l_year, a.sp_month, a.sp_day)If sp_date > Solar_date Thenl_year = l_year - 1a = LunarData(l_year)sp_date = DateSerial(l_year, a.sp_month, a.sp_day)End Ifl_day = Solar_date - sp_datel_month = 1IS_lunar_leapmonth = Falsey = a.Month(l_month)Do While l_day >= yl_day = l_day - yIf l_month = a.leapmonth Then IS_lunar_leapmonth = (Not IS_lunar_leapmonth)If IS_lunar_leapmonth Theny = a.Month(13)Elsel_month = l_month + 1y = a.Month(l_month)End IfLoopl_day = l_day + 1lunar = l_year & "-" & l_month & "-" & l_dayIf IS_lunar_leapmonth Then lunar = lunar & "-L"lunar = Choose(Part + 1, lunar, l_year, l_month, l_day)End FunctionFunction solar(Lunar_date, Optional IS_lunar_leapmonth As Integer = 0) As String'IS_lunar_leapmonth = 0, No leap month; IS_lunar_leapmonth = 1, is leap monthDim a As ConvDataALunar_date = Split(Lunar_date, "-")s_year = Lunar_date(0)For Each C In Lunar_dateIf C = "L" Then IS_lunar_leapmonth = 1Nexta = LunarData(s_year)sp_date = DateSerial(s_year, a.sp_month, a.sp_day)If Lunar_date(1) <> a.leapmonth Then IS_lunar_leapmonth = 0x = Lunar_date(2)tm = Lunar_date(1) + IS_lunar_leapmonth - 1For i = 1 To tmx = x + a.Month(i)If i = a.leapmonth And IS_lunar_leapmonth = 0 Thenx = x + a.Month(13)End IfNexts_date = sp_date + x - 1solar = s_dateEnd FunctionFunction lunarbirth(Solar_birthday As Date, Optional Inquire_year As Integer) As String If Inquire_year = 0 ThenInquire_year = Left(lunar(Now), 4)lunarbirth = solar(Inquire_year & Mid(lunar(Solar_birthday), 5, 10)) If CDate(lunarbirth) < Now - 1 Then Inquire_year = Inquire_year + 1 End Iflunarbirth = solar(Inquire_year & Mid(lunar(Solar_birthday), 5, 10))End FunctionFunction solarbirth(Solar_birthday As Date, Optional Inquire_year As Integer) As String If Inquire_year = 0 ThenInquire_year = Year(Now)solarbirth = DateSerial(Inquire_year, Month(Solar_birthday), Day(Solar_birthday)) If CDate(solarbirth) < Now - 1 Then Inquire_year = Inquire_year + 1 End Ifsolarbirth = DateSerial(Inquire_year, Month(Solar_birthday), Day(Solar_birthday)) End Function生日时对应的阳历日期生日时对应的阳历日期9, &H56A4D, &H9AD42, &H24AEB6, &H4AE4A, _ 96D37, &H95B4B, &H749BC1, &H49754, _ 5742, &H2497B7, &H4974A, &H664B3E, _2E4B, &H7C96BF, &HC9553, &HD4A48, _C95B6, &HA954A, &H7B4ABD, &H6CA51, _A953F, &HE9552, &H6AA48, &H7AD53C, _ 9549, &H75AABE, &H56A51, &H96D46, _B5452, &HB6A47, &H696D3C, &H95B50, _ADA3D, &HAB651, &H93746, &H5497BB, _B647, &H5936BC, &H92E50, &HC9645, _25D52, &H92D47, &H5C95BA, &HA954E, _52BBC, &H52B50, &HA9345, &H474AB9, _ 2646, &H5E933A, &HD534D, &H5AA43, _D254F, &HD5244, &H5DAA38, &HB5A4C, _B52BA, &H6D24E, &HADA42, &H355B37, _B244, &H4AB638, &HAAE4C, &H92E42, _56A4E, &HA6D43, &H452EB7, &H52D4B, _A5D38, &HA5B4C, &H52B42, &H3A93B6, _ 5743, &H452738, &HD264A, &H8E933E, _4D4C, &HD1541, &H2D92B5, &HD5349)lunar dayer) As Stringer) As String olar_birthday)) _birthday))。