怎樣在excel秀出來農歷初幾

『壹』 在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, 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) = 46
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) = 2709
NongliData(90) = 2890
NongliData(91) = 267946
NongliData(92) = 2773
NongliData(93) = 592565
NongliData(94) = 1210
NongliData(95) = 2651
NongliData(96) = 395863
NongliData(97) = 1323
NongliData(98) = 2707
NongliData(99) = 265877
'生成當前公歷年、月、日 ==> 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

將上面這段代碼保存在模塊中，就可以用公式
=nongli(A1)
調用了。

『貳』 求Excel里顯示農歷的函數

使用自定義函數才能夠實現。
或者用這個公式:
=TEXT(A1,"[$-130000]yyyy年m月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")

用法：A1有公歷日期，形如2013/2/14
在其它單元格中輸入上面的公式，將得到結果，如上面的日期可得到：2013年1月初5

『叄』 excel 怎麼拖出農歷的日期

在Excel使用農歷日期只有兩種辦法：要麼手動輸入；要麼自行做一個模板；雖然後者涉及到使用Excel里的函數，但卻沒辦法，因為這是必須的。

另外，關於這個模板的程序代碼，你可以到這里看看：

『肆』 怎樣可以在excel中自動填充農歷日期呢

1、可以點擊單元格，直接輸入日期。

『伍』 在EXCEL中怎樣把公歷轉換成農歷

在EXCEL中把公歷轉換成農歷的方法及具體操作步驟如下，以WPS中EXCEL為例做演示。

1、在電腦中打開需要操作的EXCEL表格，如需要將A1的日期轉成農歷，如下圖所示。

『陸』 excel里將時間顯視為農歷用什麼公式

1、VBA
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()
'獲取當前系統時間
curTime = Now()
'星期名
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) = 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) = 46
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) = 2709
NongliData(90) = 2890
NongliData(91) = 267946
NongliData(92) = 2773
NongliData(93) = 592565
NongliData(94) = 1210
NongliData(95) = 2651
NongliData(96) = 395863
NongliData(97) = 1323
NongliData(98) = 2707
NongliData(99) = 265877
'生成當前公歷年、月、日 ==> 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)
MsgBox NongliStr & NongliDayStr
End Sub
2、請到下面的網址下載載入宏文件

『柒』 Excel 有什麼辦法可將當前日期顯示變成農歷時間

辦法肯定有，就看你能不能接受了。我給出的方法，不需要寫公式，只需要調用命令就可以了。看，只要這一個函數，就出來農歷了，非常簡單。這是一個Excel的載入宏，裡麵包含了天數計算，節氣、公歷轉農歷、農歷轉公歷等一系列函數，很強大。補上下載地址:密碼:u3at

『捌』 excel里如何得到當天的農歷日期

'函數作用:陰陽歷轉換和陰陽歷生日

'說明:適用於1901-2100年間

'示例:=lunar("2006-11-1")求陽歷2006-11-1日對應的陰歷

'=solar("2006-1-1")求陰歷2006年正月初一對應的陽歷

'=lunarbirth("1975-5-6")陰歷生日:陽歷1975年5月6日出生,今年陰歷生日時對應的陽歷日期

'=solarbirth("1975-5-6")陽歷生日:陽歷1975年5月6日出生,今年陽歷生日時對應的陽歷日期

TypeConvDataA
leapmonthAsInteger
Month(1To13)AsInteger
sp_monthAsInteger'SolarmonthofSpringFestival
sp_dayAsInteger'SolardayofSpringFestival
EndType

PrivateFunctionLunarData(q_year)AsConvDataA
DimdAsLong
DimMonth(1To13)AsInteger
'1901-2100
LunarCal=Array(&H4AE53,&HA5748,&H5526BD,&HD2650,&HD9544,&H46AAB9,&H56A4D,&H9AD42,&H24AEB6,&H4AE4A,_
&H6A4DBE,&HA4D52,&HD2546,&H5D52BA,&HB544E,&HD6A43,&H296D37,&H95B4B,&H749BC1,&H49754,_
&HA4B48,&H5B25BC,&H6A550,&H6D445,&H4ADAB8,&H2B64D,&H95742,&H2497B7,&H4974A,&H664B3E,_
&HD4A51,&HEA546,&H56D4BA,&H5AD4E,&H2B644,&H393738,&H92E4B,&H7C96BF,&HC9553,&HD4A48,_
&H6DA53B,&HB554F,&H56A45,&H4AADB9,&H25D4D,&H92D42,&H2C95B6,&HA954A,&H7B4ABD,&H6CA51,_
&HB5546,&H555ABB,&H4DA4E,&HA5B43,&H352BB8,&H52B4C,&H8A953F,&HE9552,&H6AA48,&H7AD53C,_
&HAB54F,&H4B645,&H4A5739,&HA574D,&H52642,&H3E9335,&HD9549,&H75AABE,&H56A51,&H96D46,_
&H54AEBB,&H4AD4F,&HA4D43,&H4D26B7,&HD254B,&H8D52BF,&HB5452,&HB6A47,&H696D3C,&H95B50,_
&H49B45,&H4A4BB9,&HA4B4D,&HAB25C2,&H6A554,&H6D449,&H6ADA3D,&HAB651,&H93746,&H5497BB,_
&H4974F,&H64B44,&H36A537,&HEA54A,&H86B2BF,&H5AC53,&HAB647,&H5936BC,&H92E50,&HC9645,_
&H4D4AB8,&HD4A4C,&HDA541,&H25AA36,&H56A49,&H7AADBD,&H25D52,&H92D47,&H5C95BA,&HA954E,_
&HB4A43,&H4B5537,&HAD54A,&H955ABF,&H4BA53,&HA5B48,&H652BBC,&H52B50,&HA9345,&H474AB9,_
&H6AA4C,&HAD541,&H24DAB6,&H4B64A,&H69573D,&HA4E51,&HD2646,&H5E933A,&HD534D,&H5AA43,_
&H36B537,&H96D4B,&HB4AEBF,&H4AD53,&HA4D48,&H6D25BC,&HD254F,&HD5244,&H5DAA38,&HB5A4C,_
&H56D41,&H24ADB6,&H49B4A,&H7A4BBE,&HA4B51,&HAA546,&H5B52BA,&H6D24E,&HADA42,&H355B37,_
&H9374B,&H8497C1,&H49753,&H64B48,&H66A53C,&HEA54F,&H6B244,&H4AB638,&HAAE4C,&H92E42,_
&H3C9735,&HC9649,&H7D4ABD,&HD4A51,&HDA545,&H55AABA,&H56A4E,&HA6D43,&H452EB7,&H52D4B,_
&H8A95BF,&HA9553,&HB4A47,&H6B553B,&HAD54F,&H55A45,&H4A5D38,&HA5B4C,&H52B42,&H3A93B6,_
&H69349,&H7729BD,&H6AA51,&HAD546,&H54DABA,&H4B64E,&HA5743,&H452738,&HD264A,&H8E933E,_
&HD5252,&HDAA47,&H66B53B,&H56D4F,&H4AE45,&H4A4EB9,&HA4D4C,&HD1541,&H2D92B5,&HD5349)
startyear=1901
ng=LunarCal(q_year-startyear)
d=&H100000
LunarData.leapmonth=Int(ng/d)
ng=ngModd
d=&H80
mdata=Int(ng/d)
ng=ngModd
d=&H20
LunarData.sp_month=Int(ng/d)
LunarData.sp_day=ngModd
d=&H1000
i=1
Do
LunarData.Month(i)=29+Int(mdata/d)
mdata=mdataModd
Ifd=1ThenExitDo
d=d/2
i=i+1
Loop
IfLunarData.leapmonth=0ThenLunarData.Month(i)=0
EndFunction

Functionlunar(Solar_dateAsDate,OptionalPartAsInteger=0)AsString
'Part=0,all;Part=1,lunaryear;Part=2,lunarmonth;Part=3,lunarday
DimaAsConvDataA
l_year=Year(Solar_date)
a=LunarData(l_year)
sp_date=DateSerial(l_year,a.sp_month,a.sp_day)
Ifsp_date>Solar_dateThen
l_year=l_year-1
a=LunarData(l_year)
sp_date=DateSerial(l_year,a.sp_month,a.sp_day)
EndIf
l_day=Solar_date-sp_date
l_month=1
IS_lunar_leapmonth=False
y=a.Month(l_month)
DoWhilel_day>=y
l_day=l_day-y
Ifl_month=a.leapmonthThenIS_lunar_leapmonth=(NotIS_lunar_leapmonth)
IfIS_lunar_leapmonthThen
y=a.Month(13)
Else
l_month=l_month+1
y=a.Month(l_month)
EndIf
Loop
l_day=l_day+1
lunar=l_year&"-"&l_month&"-"&l_day
IfIS_lunar_leapmonthThenlunar=lunar&"-L"
lunar=Choose(Part+1,lunar,l_year,l_month,l_day)
EndFunction

Functionsolar(Lunar_date,OptionalIS_lunar_leapmonthAsInteger=0)AsString
'IS_lunar_leapmonth=0,Noleapmonth;IS_lunar_leapmonth=1,isleapmonth
DimaAsConvDataA
Lunar_date=Split(Lunar_date,"-")
s_year=Lunar_date(0)
ForEachCInLunar_date
IfC="L"ThenIS_lunar_leapmonth=1
Next
a=LunarData(s_year)
sp_date=DateSerial(s_year,a.sp_month,a.sp_day)
IfLunar_date(1)<>a.leapmonthThenIS_lunar_leapmonth=0
x=Lunar_date(2)
tm=Lunar_date(1)+IS_lunar_leapmonth-1
Fori=1Totm
x=x+a.Month(i)
Ifi=a.leapmonthAndIS_lunar_leapmonth=0Then
x=x+a.Month(13)
EndIf
Next
s_date=sp_date+x-1
solar=s_date
EndFunction

Functionlunarbirth(Solar_birthdayAsDate,OptionalInquire_yearAsInteger)AsString
IfInquire_year=0Then
Inquire_year=Left(lunar(Now),4)
lunarbirth=solar(Inquire_year&Mid(lunar(Solar_birthday),5,10))
IfCDate(lunarbirth)<Now-1ThenInquire_year=Inquire_year+1
EndIf
lunarbirth=solar(Inquire_year&Mid(lunar(Solar_birthday),5,10))
EndFunction

Functionsolarbirth(Solar_birthdayAsDate,OptionalInquire_yearAsInteger)AsString
IfInquire_year=0Then
Inquire_year=Year(Now)
solarbirth=DateSerial(Inquire_year,Month(Solar_birthday),Day(Solar_birthday))
IfCDate(solarbirth)<Now-1ThenInquire_year=Inquire_year+1
EndIf
solarbirth=DateSerial(Inquire_year,Month(Solar_birthday),Day(Solar_birthday))
EndFunction

『玖』 怎麼樣在excel身份證怎麼算出陰歷（農歷）生日，員工太多了，一個個查也太費勁了，網上搜過沒有解決方案

A2是一個身份證號碼，

B2提取陽歷生日，公式為：

=TEXT(MID(A2,7,8),"####-##-##")

C2轉化陰歷生日，公式為：

=TEXT(B2,"[$-130000][DBNum1]yyyy年m月"&IF(--TEXT(B2,"[$-130000][DBNum4]d")<11,"初","")&"d")

『拾』 excel怎麼設置去年同期農歷時間

=TEXT(EDATE(A1,-12),"[$-130000]YYYY-M-D")

不過2020年有閏月（閏4月），閏月後面的農歷月份顯示的都不對，臘月會顯示13月，推算公式好像挺復雜，建議通過後台寫VBA/從網上抓取結果