如何添加农历日期自动填充 excel阴历填充

一篇好的文章需要好好的打磨，你现在浏览的文章是一篇关于如何添加农历日期自动填充 excel阴历填充的文章，本文对文章如何添加农历日期自动填充 excel阴历填充好好的分析和解答，希望你能喜欢，只有你喜欢的内容存在，只有你来光临，我们才能继续前行。

excel中做成自动填表 要求左方是年月日期 右方农历日期

已发送你邮箱。

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

Private Sub Form_Load()

x = 2

Do While Cells(x, 1) <> ""

'获取当前系统时间

curTime = Cells(x, 1)

'星期名

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) = "十?quot;"

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

'生成当前公历星期 ==> WeekdayStr

curWeekday = Weekday(curTime)

WeekdayStr = WeekName(curWeekday)

'计算到初始时间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)

Cells(x, 2) = NongliStr & NongliDayStr

x = x + 1

Loop

End Sub

如何用Excel计算阴历生日?

简单版：

B2单元格就是第一种情况，“2023-9-17”表示农历是九月十七日。该公式是：=TEXT(A2,'[$-]YYYY-M-D')。

TEXT()用来转化文本格式，这个公式的关键是：[$-]，它是Excel中阳历转化农历的参数，不过它存在一个问题就是没法计算闰月，估计老外无法理解中国人闰月的概念，凡是闰年，它直接表示一年13个月，道理是一样的。

进阶版：

C2单元格，相比较B2单元格呈现就有了进阶，以中文呈现，并且以天干地支表现年份。

公式相对之前肯定复杂：=MID(' 甲乙丙丁戊己庚辛壬癸'拆没,MOD(TEXT(A2,'[$-]e')-4,10) 1,1)&MID('子丑寅卯辰巳旅宽纳午未申酉 戌亥',MOD(TEXT(A2,'[$-]e')-4,12) 1,1)&'年'&TEXT(A2,' [$-][DBNum1]m月d日')。

可以分解为两大部分：

1、计算年份：MID(' 甲乙丙丁戊己庚辛壬癸',MOD(TEXT(A2,'[$-]e')-4,10) 1,1)&MID('子丑寅卯辰巳午未申酉 戌亥',MOD(TEXT(A2,'[$-]e')-4,12) 1,1)&'年'，这里加了MID()文本提取函数来获取天干地支。

2、计算月日：TEXT(A2,' [$-][DBNum1]m月d日')，这里增加了参数[DBNum1]，表示数值以“一、而二、三......”中文格式显示。两者组合一起的就是C列结果。完美版作巧培为中国人，我们知道“一月”不叫“一月”，叫“正月”。

“十二月”不叫“十二月”，叫“腊月”；同样每月前10天，是初一、初二......20日开始是廿、廿一......30日是卅等等。

在EXCEL表中,怎样通过下拉来填充农历日历,比如,A1里是正月初一,在A2里...

这个，2个方式来完成，一种，笨点的，但不需要辅助列颤罩，需要自动填充的前期，是在EXCEL有“自定义序列”，比如1 23之所以可以填充，就是因为系统默认中就有。

所以，

方法一：设置自定义序列

2003版：工具-选项-自定义序列

2023或10：Office按钮---excel选项----常用/高级---编辑自定义列表----输入序列----确定

方法二：公式，需要设置某个扒洞如日期，比如2023-1-23，就是初一，你写在A2中，在B2输入

=TEXT(A2,"[dbnum1][$-]m月"&IF(--TEXT(A2,"[$-]d")<11,"初","")&TEXT(A2,"[dbnum1][$-]d"))

就OK了，不过是春启一月初一 ，呵呵~~

以上内容是小编精心整理的关于如何添加农历日期自动填充 excel阴历填充的精彩内容，好的文章需要你的分享，喜欢如何添加农历日期自动填充 excel阴历填充这篇精彩文章的，请您经常光顾吧！