excel中如何能將日期由公曆轉換為農曆

2021-03-16 04:41:23 字數 7891 閱讀 6090

1樓:

將下面面這段**儲存在模組中,就可以用公式 =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

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

'計算到初始時間2023年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

希望能幫助你

如何在Excel中快速輸入日期和時間

excel快速輸入時間日期 有大量日期或時間資料需要錄入的話,我都是用的笨辦法 1 分析資料結構和型別,決定快速輸入的方法,一般都是輸入4 6位數字,比如今天日期,現在時間,我分別輸入為 170608 和 1940 2 利用 20 170608 的形式可以補齊所缺少的資料部分3 利用自定義資料格式 ...

如何統計EXCEL表中某個區域日期出現個數

如果a列和e列是日期格式 sumproduct n text a1 g3,emm 201408 excel中,如果統計某個時間區間段出現的個數 f2中輸入。countifs a a,d1,a a,回車確認後下拉填充。如何統計excel帶有日期的個數 1 在excel裡統計同乙個日期的個數,可以用條件...

在EXCEL中如何對指定日期,對滿足條件的資料求和

1 開啟下圖所示的工作表,將公式設定為f5單元格,並輸入 sumifs 2 選擇求和所需的資料範圍,即數量為 c2到c11的單元格,因此選擇或輸入 c2 c11。3 輸入sumifs函式第乙個條件的範圍 即日期小於2月25日 即單元格a2至a11,選擇或直接輸入 a2 a11。4 輸入要滿足的第乙個...