在excel中怎樣制公曆和轉換成農曆

2021-03-16 04:41:24 字數 8682 閱讀 9638

1樓:挑澄流浪

這個需要vba了.

按alt+f11 再 插入 模組

把下面的**制複製到模組中,然後 在b1中輸入:

=calendar(a1)

假設日期在a1

option explicit

dim weekname(7), monthadd(11), nonglidata(120), 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, num1, num2

dim year, month, day, grid, grid_date

function calendar(grid)

grid_date = grid

year = mid(grid_date, 1, 4)

num1 = instr(grid_date, "-")

num2 = instr(6, grid_date, "-")

if (num2 - num1) = 3 then

month = mid(grid_date, 6, 2)

day = mid(grid_date, 9, 2)

else

month = mid(grid_date, 6, 1)

day = mid(grid_date, 8, 2)

end if

'星期名

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) = "十一"

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

curmonth = month

curday = day

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)

'計算到初始時間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) & ")"

calendar = nonglistr & nonglidaystr

end function

——————————————ps 在網上借鑑的相關資料

農曆日期和公曆日期怎麼轉換,Excel 中陽曆日期如何轉換成陰曆日期?

1 電腦開啟excel 列好日期。2 然後在陰曆中輸入公式 text a2,130000 yyyy m d 3 輸入公式後,按回車鍵就可以把陽曆轉換為陰曆了。4 下拉複製公式就可以了。2007版的excel可用下面公式轉換 text a1,130000 yyyy年m月初 d 農曆日期和公曆日期怎麼轉...

怎樣把Excel中的文件轉換成word

操作如下 1 可以右鍵點選桌面左下角的windows徽標鍵,然後在彈出選單中選擇 設定 選單項。2 接下來就會開啟windows設定視窗,點選視窗中的 應用 圖示。3 在開啟的windows應用和功能視窗中,點選 應用和功能 選單項,然後在右側是的視窗中找到 程式和功能 快捷連結。4 這時就會開啟w...

在excel中數值轉換成日期的公式

既然都再同一列裡面可以使用資料 分列 固定寬度 將不同的單位區分開啊。選擇須設定的單元格或者區域 右鍵 設定單元格格式 數字 選項卡 自定義 在 型別 下面的框裡輸入 0000 00 00 注 均在半形或英文狀態下輸入 確定就ok了。text a1,00000000 00 00 00 日期中間要短橫...