农历大小月公式电子表格 农历计算公式

一篇好的文章需要好好的打磨，你现在浏览的文章是一篇关于农历大小月公式电子表格 农历计算公式的文章，本文对文章农历大小月公式电子表格 农历计算公式好好的分析和解答，希望你能喜欢，只有你喜欢的内容存在，只有你来光临，我们才能继续前行。

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

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

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

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

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

=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-]e")-4,12)+1,1)&"年"&TEXT(A1,"[$-][DBNum1]m月d日")

即可。

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

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

A1为公历，B1输入：

=CHOOSE(MOD(YEAR(A1)-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(A1)-1900,12)+1,"子","丑","寅","卯"空燃姿,"辰","巳","午","未","申","酉","戌","亥")&TEXT(A1,"[dbnum1][$-]年m月"&IF(--TEXT(A1,"[$-]d")<11,"初","")&TEXT(A1,"[dbnum1][$-]d"))

这里可以对初几进行显示

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

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

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

PublicFunctionNongLi(OptionalXX_DATEAsDate)

DimMonthAdd(11),NongliData(99),TianGan(9),DiZhi(11),ShuXiang(11),DayName(30),MonName(12)

DimcurTime,curYear,curMonth,curDay

DimGongliStr,NongliStr,NongliDayStr

Dimi,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)=

NongliData(2)=1701

NongliData(3)=1748

NongliData(4)=

NongliData(5)=694

NongliData(6)=2391

NongliData(7)=

NongliData(8)=1175

NongliData(9)=

NongliData(10)=3402

NongliData(11)=3749

NongliData(12)=

NongliData(13)=1453

NongliData(14)=694

NongliData(15)=

NongliData(16)=2350

NongliData(17)=

NongliData(18)=3221

NongliData(19)=3402

NongliData(21)=2901

NongliData(22)=1386

NongliData(23)=

NongliData(24)=605

NongliData(25)=2349

NongliData(26)=

NongliData(27)=2709

NongliData(28)=

NongliData(29)=1738

NongliData(30)=2901

NongliData(31)=

NongliData(32)=1242

NongliData(33)=2651

NongliData(34)=

NongliData(35)=1323

NongliData(36)=

NongliData(37)=3733

NongliData(38)=1706

NongliData(39)=

NongliData(40)=2741

NongliData(41)=1206

NongliData(42)=

NongliData(43)=2647

NongliData(44)=1318

NongliData(45)=

NongliData(46)=3477

NongliData(47)=

NongliData(48)=1386

NongliData(49)=2413

NongliData(50)=

NongliData(51)=1197

NongliData(52)=2637

NongliData(53)=

NongliData(54)=3365

NongliData(55)=

NongliData(56)=2900

NongliData(57)=2922

NongliData(58)=

NongliData(59)=2395

NongliData(60)=1179

NongliData(61)=

NongliData(62)=2635

NongliData(63)=

NongliData(64)=1701

NongliData(65)=1748

NongliData(66)=

NongliData(67)=2742

NongliData(68)=2391

NongliData(69)=

NongliData(70)=1175

NongliData(71)=1611

NongliData(72)=

NongliData(73)=3749

NongliData(74)=

NongliData(75)=1452

NongliData(76)=2742

NongliData(77)=

NongliData(78)=2350

NongliData(79)=3222

NongliData(80)=

NongliData(81)=3402

NongliData(82)=3493

NongliData(83)=

NongliData(84)=1386

NongliData(85)=

NongliData(86)=605

NongliData(87)=2349

NongliData(88)=

NongliData(89)=2709

NongliData(90)=2890

NongliData(91)=

NongliData(92)=2773

NongliData(93)=

NongliData(94)=1210

NongliData(95)=2651

NongliData(96)=

NongliData(97)=1323

NongliData(98)=2707

NongliData(99)=

'生成当前公历年、月、日==>GongliStr

curYear=Year(curTime)

curMonth=Month(curTime)

curDay=Day(curTime)

GongliStr=curYear&"年"

If(curMonth<10)Then

GongliStr=GongliStr&"0"&curMonth&"月"

Else

GongliStr=GongliStr&curMonth&"月"

EndIf

If(curDay<10)Then

GongliStr=GongliStr&"0"&curDay&"日"

Else

GongliStr=GongliStr&curDay&"日"

EndIf

'计算到初始时间1921年2月8日的天数：1921-2-8(正月初一)

TheDate=(curYear-1921)*365+Int((curYear-1921)/4)+curDay+MonthAdd(curMonth-1)-38

If((curYearMod4)=0AndcurMonth>2)Then

TheDate=TheDate+1

EndIf

'计算农历天干、地支、月、日

isEnd=0

m=0

Do

If(NongliData(m)<4095)Then

k=11

Else

k=12

EndIf

n=k

Do

If(n<0)Then

ExitDo

EndIf

'获取NongliData(m)的第n个二进制位的值

bit=NongliData(m)

Fori=1TonStep1

bit=Int(bit/2)

Next

bit=bitMod2

If(TheDate<=29+bit)Then

isEnd=1

ExitDo

EndIf

TheDate=TheDate-29-bit

n=n-1

Loop

If(isEnd=1)Then

ExitDo

EndIf

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

EndIf

EndIf

'生成农历天干、地支、属相==>NongliStr

NongliStr="农历"&TianGan(((curYear-4)Mod60)Mod10)&DiZhi(((curYear-4)Mod60)Mod12)&"年"

NongliStr=NongliStr&"("&ShuXiang(((curYear-4)Mod60)Mod12)&")"

'生成农历月、日==>NongliDayStr

If(curMonth<1)Then

NongliDayStr="闰"&MonName(-1*curMonth)

Else

NongliDayStr=MonName(curMonth)

EndIf

NongliDayStr=NongliDayStr&"月"

NongliDayStr=NongliDayStr&DayName(curDay)

NongLi=NongliStr&NongliDayStr

EndFunction

EXCEL公历转农历公式?

excel内置函数是无法完美实现的，至少闰月的情况会有问题。

只能考虑用VBA编程的方式，自定义一个函数。

图一：效果展示图：

效果展示图

图二：步骤示意思图：

步骤示意图

图三：完整代码图：

【步骤】：

1、如果文件是.xlsx格式，须另存为 .xlsm格式 即启用宏的工作簿。

2、按Alt+F11打开代码编辑器，

3、点“插入”--“模块”，

4、在左侧栏中双击模块，

5、在右侧的编辑区中粘贴代码，

6、检查代码是否报错变红，

7、关闭代码编辑器。

【重要说明】：

说明1、从这里复制的代码，行与行之间会被百度额外插入空白行，如果导致代码报错变红，你就把行与行之间的空白行删掉即可。

说明2、此代码也不是万能的，它有日期范围的限制，只能转化 1921年的正月初一 至 2100年的腊月三十 这个范围，对应的公历范围是 1921/2/8 至 2100/2/8

说明3、在表格中使用公式时，可以直接在公式中写明一个日期，也可以引用某单元格中的日期。

例1，转化具体的日期：

=iNlStr(DATE(2023,8,18))

例2，永远转化今天当天的日期：

=iNlStr(TODAY())

例3，从单元格引用日期进行侍则转化：

=iNlStr(A1)

Option Explicit

Public tf As Boolean '

Public sZhouJ As Variant '星期数组

Public sTiGan As Variant '十个天干数组

Public sDiZhi As Variant '十二地支数组

Public sSXiao As Variant '十二生肖数组

Public sNlMon As Variant '农历月份数组

Public sNlDay As Variant '农历日份数组

Public sNlShu As Variant '农历数据数组

Sub 加载数组们()

sZhouJ = Split("*/一/二/三/四/五/六/日", "/") '星期数组

sTiGan = Split("甲/乙/丙/丁/戊/己/庚/辛/壬/癸", "/") '天干数组

sDiZhi = Split("子/丑/寅/卯/辰/巳/午/未/申/酉/戌/亥", "/") '地支数组

sSXiao = Split("鼠或谈含/牛/虎/兔/龙/蛇/马/羊/猴/鸡/狗/猪", "/") '生肖数组

sNlMon = Split("*/正月/二月/三月/四月/五月/六月/七月/八月/九月/十月/冬月/腊月", "/") '月份数组

sNlDay = Split( _

"*/初一/初二/初三/初四/初五/初六/初七/初八/初九/初十" _

衫笑 & "/十一/十二/十三/十四/十五/十六/十七/十八/十九/二十" _

& "/廿一/廿二/廿三/廿四/廿五/廿六/廿七/廿八/廿九/三十", "/") '日份数组

sNlShu = Split( _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"//////////" & _

"////////", "/") '农历数据

tf = True '打标记,数组已加载

End Sub

Function iNlStr(iDate As Date) As String '公历转农历

If Not tf Then Call 加载数组们

Dim i%, t&, k%, m%, n%, ext%, bit&

'计算从1921-2-8(即1921年的正月初一)至目标日期的总天数

t = iDate - #2/8/1921# + 1

'计算农历年月日的数值

Do

If Val(sNlShu(m)) < 4095 Then k = 11 Else k = 12

n = k

Do

'获取sNlShu(m)的第n个二进制位的值

bit = Val(sNlShu(m))

For i = 1 To n

bit = bit \ 2

Next

bit = bit Mod 2

'计算农历天的数值

If t <= 29 + bit Then

ext = 1

Exit Do

End If

t = t - 29 - bit

n = n - 1

Loop Until n < 0

If ext Then Exit Do

m = m + 1

Loop Until False

'计算农历叁个数值

Dim cy%, cm%, cd%

cy = 1921 + m '农历年的数值

cm = k - n + 1 '农历月的数值

cd = t '农历日的数值

If k = 12 Then

Select Case Val(sNlShu(m)) \ 65536 + 1 '修正cm的数值

Case Is = cm

cm = 1 - cm

Case Is < cm

cm = cm - 1

End Select

End If

'计算汉化文本

Dim ar(1 To 5) As String

m = ((cy - 4) Mod 60) Mod 10 '天干索引值

n = ((cy - 4) Mod 60) Mod 12 '地支和生肖索引值

If cm < 1 Then ar(1) = "闰"

ar(1) = ar(1) & sNlMon(Abs(cm)) '农历月份

ar(2) = sNlDay(cd) '农历日份

ar(3) = sTiGan(m) & sDiZhi(n) '农历干支

ar(4) = sSXiao(n) '农历生肖

ar(5) = sZhouJ(Weekday(iDate, 2)) '星期汉字

'搭配输出结果(提示:数组ar(1 to 5)共5个元素，可根据需要自由搭配输出结果)

iNlStr = ar(3) & "年 生肖" & ar(4) & " " & ar(1) & ar(2) & " 周" & ar(5) '输出结果

End Function

excel 用函数编出农历大小月份

农历函数：

Sub 农历()

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) =

NongliData(2) = 1701

NongliData(3) = 1748

NongliData(4) =

NongliData(5) = 694

NongliData(6) = 2391

NongliData(7) =

NongliData(8) = 1175

NongliData(9) =

NongliData(10) = 3402

NongliData(11) = 3749

NongliData(12) =

NongliData(13) = 1453

NongliData(14) = 694

NongliData(15) =

NongliData(16) = 2350

NongliData(17) =

NongliData(18) = 3221

NongliData(19) = 3402

NongliData(21) = 2901

NongliData(22) = 1386

NongliData(23) =

NongliData(24) = 605

NongliData(25) = 2349

NongliData(26) =

NongliData(27) = 2709

NongliData(28) =

NongliData(29) = 1738

NongliData(30) = 2901

NongliData(31) =

NongliData(32) = 1242

NongliData(33) = 2651

NongliData(34) =

NongliData(35) = 1323

NongliData(36) =

NongliData(37) = 3733

NongliData(38) = 1706

NongliData(39) =

NongliData(40) = 2741

NongliData(41) = 1206

NongliData(42) =

NongliData(43) = 2647

NongliData(44) = 1318

NongliData(45) =

NongliData(46) = 3477

NongliData(47) =

NongliData(48) = 1386

NongliData(49) = 2413

NongliData(50) =

NongliData(51) = 1197

NongliData(52) = 2637

NongliData(53) =

NongliData(54) = 3365

NongliData(55) =

NongliData(56) = 2900

NongliData(57) = 2922

NongliData(58) =

NongliData(59) = 2395

NongliData(60) = 1179

NongliData(61) =

NongliData(62) = 2635

NongliData(63) =

NongliData(64) = 1701

NongliData(65) = 1748

NongliData(66) =

NongliData(67) = 2742

NongliData(68) = 2391

NongliData(69) =

NongliData(70) = 1175

NongliData(71) = 1611

NongliData(72) =

NongliData(73) = 3749

NongliData(74) =

NongliData(75) = 1452

NongliData(76) = 2742

NongliData(77) =

NongliData(78) = 2350

NongliData(79) = 3222

NongliData(80) =

NongliData(81) = 3402

NongliData(82) = 3493

NongliData(83) =

NongliData(84) = 1386

NongliData(85) =

NongliData(86) = 605

NongliData(87) = 2349

NongliData(88) =

NongliData(89) = 2709

NongliData(90) = 2890

NongliData(91) =

NongliData(92) = 2773

NongliData(93) =

NongliData(94) = 1210

NongliData(95) = 2651

NongliData(96) =

NongliData(97) = 1323

NongliData(98) = 2707

NongliData(99) =

'生成当前公历年、月、日 ==> 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

以上内容是小编精心整理的关于农历大小月公式电子表格 农历计算公式的精彩内容，好的文章需要你的分享，喜欢农历大小月公式电子表格 农历计算公式这篇精彩文章的，请您经常光顾吧！