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 日期中間要短橫...