这个需要VBA了.
按ALT+F11再插入模块
把下面的代码制复制到模块中,然后在B1中输入:
=calendar(A1)
假设日期在A1
OptionExplicit
DimWeekName(7),MonthAdd(11),NongliData(120),TianGan(9),DiZhi(11),ShuXiang(11),DayName(30),MonName(12)
DimcurTime,curYear,curMonth,curDay,curWeekday
DimGongliStr,WeekdayStr,NongliStr,NongliDayStr
Dimi,m,n,k,isEnd,bit,TheDate,num1,num2
Dimyear,month,day,grid,grid_date
Functioncalendar(grid)
grid_date=grid
year=Mid(grid_date,1,4)
num1=InStr(grid_date,"-")
num2=InStr(6,grid_date,"-")
If(num2-num1)=3Then
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)
EndIf
'星期名
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)=
NongliData(2)=1701
NongliData(3)=1748
NongliData(4)=
NongliData(5)=694
NongliData(6)=2391
NongliData(7)=
NongliData(8)=1175
NongliData(9)=
NongliData(10)=3402
NongliData(11)=3749
NongliData(12)=
NongliData(13)=1453
NongliData(14)=694
NongliData(15)=
NongliData(16)=2350
NongliData(17)=
NongliData(18)=3221
NongliData(19)=3402
NongliData(20)=
NongliData(21)=2901
NongliData(22)=1386
NongliData(23)=
NongliData(24)=605
NongliData(25)=2349
NongliData(26)=
NongliData(27)=2709
NongliData(28)=
NongliData(29)=1738
NongliData(30)=2901
NongliData(31)=
NongliData(32)=1242
NongliData(33)=2651
NongliData(34)=
NongliData(35)=1323
NongliData(36)=
NongliData(37)=3733
NongliData(38)=1706
NongliData(39)=
NongliData(40)=2741
NongliData(41)=1206
NongliData(42)=
NongliData(43)=2647
NongliData(44)=1318
NongliData(45)=
NongliData(46)=3477
NongliData(47)=
NongliData(48)=1386
NongliData(49)=2413
NongliData(50)=
NongliData(51)=1197
NongliData(52)=2637
NongliData(53)=
NongliData(54)=3365
NongliData(55)=
NongliData(56)=2900
NongliData(57)=2922
NongliData(58)=
NongliData(59)=2395
NongliData(60)=1179
NongliData(61)=
NongliData(62)=2635
NongliData(63)=
NongliData(64)=1701
NongliData(65)=1748
NongliData(66)=
NongliData(67)=2742
NongliData(68)=2391
NongliData(69)=
NongliData(70)=1175
NongliData(71)=1611
NongliData(72)=
NongliData(73)=3749
NongliData(74)=
NongliData(75)=1452
NongliData(76)=2742
NongliData(77)=
NongliData(78)=2350
NongliData(79)=3222
NongliData(80)=
NongliData(81)=3402
NongliData(82)=3493
NongliData(83)=
NongliData(84)=1386
NongliData(85)=
NongliData(86)=605
NongliData(87)=2349
NongliData(88)=
NongliData(89)=2709
NongliData(90)=2890
NongliData(91)=
NongliData(92)=2773
NongliData(93)=
NongliData(94)=1210
NongliData(95)=2651
NongliData(96)=
NongliData(97)=1323
NongliData(98)=2707
NongliData(99)=
'生成当前公历年、月、日==>GongliStr
curYear=year
curMonth=month
curDay=day
GongliStr=curYear"年"
If(curMonth<10)Then
GongliStr=GongliStr"0"curMonth"月"
Else
GongliStr=GongliStrcurMonth"月"
EndIf
If(curDay<10)Then
GongliStr=GongliStr"0"curDay"日"
Else
GongliStr=GongliStrcurDay"日"
EndIf
'生成当前公历星期==>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((curYearMod4)=0AndcurMonth>2)Then
TheDate=TheDate+1
EndIf
'计算农历天干、地支、月、日
isEnd=0
m=0
Do
If(NongliData(m)<4095)Then
k=11
Else
k=12
EndIf
n=k
Do
If(n<0)Then
ExitDo
EndIf
'获取NongliData(m)的第n个二进制位的值
bit=NongliData(m)
Fori=1TonStep1
bit=Int(bit/2)
Next
bit=bitMod2
If(TheDate<=29+bit)Then
isEnd=1
ExitDo
EndIf
TheDate=TheDate-29-bit
n=n-1
Loop
If(isEnd=1)Then
ExitDo
EndIf
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
EndIf
EndIf
'生成农历天干、地支、属相==>NongliStr
NongliStr="农历"TianGan(((curYear-4)Mod60)Mod10)DiZhi(((curYear-4)Mod60)Mod12)"年"
NongliStr=NongliStr"("ShuXiang(((curYear-4)Mod60)Mod12)""
'生成农历月、日==>NongliDayStr
If(curMonth<1)Then
NongliDayStr="闰"MonName(-1*curMonth)
Else
NongliDayStr=MonName(curMonth)
EndIf
NongliDayStr=NongliDayStr"月"
NongliDayStr=NongliDayStrDayName(curDay)")"
calendar=NongliStrNongliDayStr
EndFunction
——————————————PS在网上借鉴的相关资料
