首页 / 教程

wps把阳历生日转换成阴历 | 求WPS公历转农历日期公式

2022-10-30 12:04:00

1.求WPS公历转农历日期公式

如下图，需要对日期：2019/11/20转换为农历日期请点击输入图片描述如何快速的计算得到该日期对应的农历呢？请点击输入图片描述首先，需要选中计算填充为农历日期的单元格请点击输入图片描述选择单元格后，输入函数：=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日"请点击输入图片描述输入函数后，回车确定，就能将公历日期转换成农历日期了。

请点击输入图片描述下面就是最后转换为农历日期填充的效果了，如果日期较多，可以批量填充函数即可。请点击输入图片描述。

2.WPS怎么计算农历生日

方法如下：

1.打开WPS表格，其中输入内容，将出生日期输入进去，输入完成后效果如图所示。

2.选中出生日期的栏，右键点击选中设置单元格格式，进入设置页面后。

3.选择数字选项卡，选择单元格格式为日期，类型为2001-3-7。

4.选中年龄栏，右键点击设置单元格格式。设置单元格格式可以更多的是为了不出现失误。

5.在设置单元格格式里，选择数字选项卡，数字格式选择为常规即可。

6.在年龄栏输入=（YEAR(NOW())-YEAR(A2)），这里解释一下A2里的A代表出生日期所在的列，2代表所在的行。

7.输入完成后直接按Enter键或者把鼠标移到其他的格里就可以完成这一步，得出效果如图。

8.重新选择B2格，等到鼠标变成一个十字的时候向下拉窗口即可。效果如图所示。

9.这个时候就可以看到同一年出生的人年纪是一样大，不会出现因为出生的月份不一样而同一年出生却不一样大的情况。

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

Option Explicit Dim WeekName(7), MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), MonName(12) Dim curTime, curYear, curMonth, curDay, curWeekday Dim GongliStr, WeekdayStr, NongliStr, NongliDayStr Dim i, m, n, k, isEnd, bit, TheDate Dim year, month, day, grid, grid_date Sub datetest（)'这是转换样例 Debug.Print calendar("2011-8-15") End Sub Function calendar(grid)'转换函数 grid_date = grid year = 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) = 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 。

4.怎么在EXCEL用函数把公历日期变成阴历

公历转农历模块'原创：互联网'修正：阿勇 2005/1/12'// 农历数据定义 //'先以 H2B 函数还原成长度为 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年）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 StringOn Error GoTo aErr 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) = H2B(Mid(ylData, (tYear - 1900) * 8 + 1, 7)) daList(tYear) = H2B(Mid(ylData, (tYear - 1900 + 1) * 8 + 1, 7)) AddYear = tYearinitYL: 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 The。

5.excel中如何能将日期由公历转换为农历

将下面面这段代码保存在模块中，就可以用公式 =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 。

wps如何把阳历生日转换成阴历