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

2021-03-16 04:41:24 字數 7882 閱讀 9921

1樓:

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

doif (nonglidata(m) < 4095) then

k = 11

else

k = 12

end if

n = k

doif (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中如何設定工作表的日期

在工作表中,選擇要填寫日期的單元格,如下圖所示 選中單元格後,右擊滑鼠右鍵,在彈出的右鍵選單選項中,選擇 設定單元格格式 在彈出的 單元格格式 視窗中,依序選擇 日期 2001年3月7日 單元格格式設定好後,再次將該單元格選中,然後選擇函式命令,在彈出的 插入函式 視窗,將函式的類別選擇為 日期函式...

在EXCEL中如何能讓數值為五入

如果是隻要大於某個整數就向上圓整,就不能叫 五入 了,這個是個概念問題,呵呵。這可以直接用函式roundup 如roundup 4.2,0 5 但如果是必須要超出0.1才向上圓整的話,那就可以先把數字減去0.1來實現,也就是公式改為 roundup 4.2 0.1,0 5 這裡公式中是直接給出的數字...

excel中如何統計日期裡面出現的人名次數

這需要看電腦配置了,配置低的話,頂多處理十萬以內單元格。配置高也頂多三十萬個單元格 countifs g g,某某 o o,2014 4 2 countifs g g,某某 o o,2014 4 2 後面的日期是固定的,如果換成動態的就好了 如何統計excel中名字出現的次數 材料 工具 excel...