在EXCEL中怎样把公历转换成农历 excel中如何能将日期由公历转换为农历

\u5982\u4f55\u5c06excel\u516c\u5386\u8f6c\u6362\u4e3a\u519c\u5386

Excel\u5c0f\u6280\u5de7

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

'\u83b7\u53d6\u5f53\u524d\u7cfb\u7edf\u65f6\u95f4
curTime = XX_DATE

'\u5929\u5e72\u540d\u79f0
TianGan(0) = "\u7532"
TianGan(1) = "\u4e59"
TianGan(2) = "\u4e19"
TianGan(3) = "\u4e01"
TianGan(4) = "\u620a"
TianGan(5) = "\u5df1"
TianGan(6) = "\u5e9a"
TianGan(7) = "\u8f9b"
TianGan(8) = "\u58ec"
TianGan(9) = "\u7678"
'\u5730\u652f\u540d\u79f0
DiZhi(0) = "\u5b50"
DiZhi(1) = "\u4e11"
DiZhi(2) = "\u5bc5"
DiZhi(3) = "\u536f"
DiZhi(4) = "\u8fb0"
DiZhi(5) = "\u5df3"
DiZhi(6) = "\u5348"
DiZhi(7) = "\u672a"
DiZhi(8) = "\u7533"
DiZhi(9) = "\u9149"
DiZhi(10) = "\u620c"
DiZhi(11) = "\u4ea5"
'\u5c5e\u76f8\u540d\u79f0
ShuXiang(0) = "\u9f20"
ShuXiang(1) = "\u725b"
ShuXiang(2) = "\u864e"
ShuXiang(3) = "\u5154"
ShuXiang(4) = "\u9f99"
ShuXiang(5) = "\u86c7"
ShuXiang(6) = "\u9a6c"
ShuXiang(7) = "\u7f8a"
ShuXiang(8) = "\u7334"
ShuXiang(9) = "\u9e21"
ShuXiang(10) = "\u72d7"
ShuXiang(11) = "\u732a"
'\u519c\u5386\u65e5\u671f\u540d
DayName(0) = "*"
DayName(1) = "\u521d\u4e00"
DayName(2) = "\u521d\u4e8c"
DayName(3) = "\u521d\u4e09"
DayName(4) = "\u521d\u56db"
DayName(5) = "\u521d\u4e94"
DayName(6) = "\u521d\u516d"
DayName(7) = "\u521d\u4e03"
DayName(8) = "\u521d\u516b"
DayName(9) = "\u521d\u4e5d"
DayName(10) = "\u521d\u5341"
DayName(11) = "\u5341\u4e00"
DayName(12) = "\u5341\u4e8c"
DayName(13) = "\u5341\u4e09"
DayName(14) = "\u5341\u56db"
DayName(15) = "\u5341\u4e94"
DayName(16) = "\u5341\u516d"
DayName(17) = "\u5341\u4e03"
DayName(18) = "\u5341\u516b"
DayName(19) = "\u5341\u4e5d"
DayName(20) = "\u4e8c\u5341"
DayName(21) = "\u5eff\u4e00"
DayName(22) = "\u5eff\u4e8c"
DayName(23) = "\u5eff\u4e09"
DayName(24) = "\u5eff\u56db"
DayName(25) = "\u5eff\u4e94"
DayName(26) = "\u5eff\u516d"
DayName(27) = "\u5eff\u4e03"
DayName(28) = "\u5eff\u516b"
DayName(29) = "\u5eff\u4e5d"
DayName(30) = "\u4e09\u5341"
'\u519c\u5386\u6708\u4efd\u540d
MonName(0) = "*"
MonName(1) = "\u6b63"
MonName(2) = "\u4e8c"
MonName(3) = "\u4e09"
MonName(4) = "\u56db"
MonName(5) = "\u4e94"
MonName(6) = "\u516d"
MonName(7) = "\u4e03"
MonName(8) = "\u516b"
MonName(9) = "\u4e5d"
MonName(10) = "\u5341"
MonName(11) = "\u5341\u4e00"
MonName(12) = "\u814a"
'\u516c\u5386\u6bcf\u6708\u524d\u9762\u7684\u5929\u6570
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
'\u519c\u5386\u6570\u636e
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
'\u751f\u6210\u5f53\u524d\u516c\u5386\u5e74\u3001\u6708\u3001\u65e5 ==> GongliStr
curYear = Year(curTime)
curMonth = Month(curTime)
curDay = Day(curTime)
GongliStr = curYear & "\u5e74"
If (curMonth < 10) Then
GongliStr = GongliStr & "0" & curMonth & "\u6708"
Else
GongliStr = GongliStr & curMonth & "\u6708"
End If
If (curDay < 10) Then
GongliStr = GongliStr & "0" & curDay & "\u65e5"
Else
GongliStr = GongliStr & curDay & "\u65e5"
End If
'\u8ba1\u7b97\u5230\u521d\u59cb\u65f6\u95f41921\u5e742\u67088\u65e5\u7684\u5929\u6570\uff1a1921-2-8(\u6b63\u6708\u521d\u4e00)
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
'\u8ba1\u7b97\u519c\u5386\u5929\u5e72\u3001\u5730\u652f\u3001\u6708\u3001\u65e5
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
'\u83b7\u53d6NongliData(m)\u7684\u7b2cn\u4e2a\u4e8c\u8fdb\u5236\u4f4d\u7684\u503c
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
'\u751f\u6210\u519c\u5386\u5929\u5e72\u3001\u5730\u652f\u3001\u5c5e\u76f8 ==> NongliStr
NongliStr = "\u519c\u5386" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "\u5e74"
NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ")"
'\u751f\u6210\u519c\u5386\u6708\u3001\u65e5 ==> NongliDayStr
If (curMonth < 1) Then
NongliDayStr = "\u95f0" & MonName(-1 * curMonth)
Else
NongliDayStr = MonName(curMonth)
End If
NongliDayStr = NongliDayStr & "\u6708"
NongliDayStr = NongliDayStr & DayName(curDay)

NongLi = NongliStr & NongliDayStr

End Function

\u5c06\u4e0a\u9762\u8fd9\u6bb5\u4ee3\u7801\u4fdd\u5b58\u5728\u6a21\u5757\u4e2d\uff0c\u5c31\u53ef\u4ee5\u7528\u516c\u5f0f
=nongli(A1)
\u8c03\u7528\u4e86\u3002

在EXCEL中把公历转换成农历的方法及具体操作步骤如下，以WPS中EXCEL为例做演示。

1、在电脑中打开需要操作的EXCEL表格，如需要将A1的日期转成农历，如下图所示。

2、点击上方菜单栏中的开始选项，进入下一页面。

3、点击右侧的求和选项下拉菜单，选择“其他函数”选项，点击进入下一页面。

4、在弹出的对话框中将选择类型选为“文本”，选择函数选择“TEXT”，点击确定进入下一页面。

5、在弹出的对话框中的输入栏中分别输入要转换日期的位置和"[$-130000]YYYY-M-D"，如下图所示，点击确定即完成设置。

6、这时在表格中就可以看到原先的日期已经转换成农历了，如下图所示。

Excel小技巧

用函数：=TEXT(阳历时间,"[$-130000]YYYY-M-D")

 

注：这个公式只适用于2010以后的版本，以前的版本不行，而且遇到闰年的时候月份可能会多一个月，即可可能出现13月的情况，这个情况要改回12月：

Dim WeekName(7), MonthAdd(11), NongliData(99), 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
Private Sub Form_Load()
'获取当前系统时间
curTime = Now()
'星期名
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) = "十?quot;"
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
'生成当前公历星期 ==> 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 ((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)
MsgBox NongliStr & NongliDayStr
End Sub
参考资料：http://www.wangchao.net.cn/bbsshowlist.jsp?area_id=02&board_id=01&parent_id=23361

这个需要用VBA才能解决的

excel涓浣曞皢鍏巻鍐滃巻杞崲
绛旓細鎵撳紑excel琛ㄦ牸杩涘叆椤甸潰锛岄変腑浠绘剰涓涓┖鐧藉崟鍏冩牸锛屽垏鎹㈠埌鍏紡閫夐」鏍锛屽崟鍑绘彃鍏ュ嚱鏁 寮瑰嚭绐楀彛锛屽皢绫诲埆璁剧疆涓烘枃鏈紝閫夋嫨鍑芥暟涓篢EXT锛岀偣鍑荤‘瀹 鍦╒alue鏍忚緭鍏ラ渶瑕佽浆鎹㈢殑鍏巻鎵鍦ㄥ崟鍏冩牸锛屽湪Format_text鏍忚緭鍏"[$-130000]YYYY-M-D"锛屽崟鍑荤‘瀹氬嵆鍙 浠ヤ笂灏辨槸Excel鍏巻涓庡啘鍘嗙殑杞崲鏂规硶鍐呭锛屽笇鏈涘澶у鏈...

excel鍏巻杞鍐滃巻鐨鍥涚鏂规硶鍒嗗埆閫氳繃excel鍐呯疆鍑芥暟鍜岃嚜瀹氫箟鍑芥暟瀹屾垚鍏巻...
绛旓細excel鍏巻杞啘鍘嗘柟娉曚竴:浣跨敤text鍑芥暟 A鍒楁槸鍏巻鏃ユ湡,鎴戜滑鍦˙1鍗曞厓鏍艰緭鍏ュ叕寮:=TEXT(A1,"[$-130000]yyyy骞磎鏈"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"鍒","")&"d"),涓嬫媺瀹屾垚鍏巻杞啘鍘嗐 excel鍏巻杞啘鍘嗘柟娉曚簩:浣跨敤text+MID鍑芥暟 A鍒椾粛鐒舵槸鍏巻,B1杈撳叆鍏紡:=MID("鐢蹭箼涓欎竵鎴婂繁搴氳緵澹櫢",MOD(...

濡備綍鍦‥xcel涓叕鍘嗚浆鍐滃巻
绛旓細涓姝ワ細鍙屽嚮闇瑕佹搷浣鐨凟xcel琛ㄦ牸锛岀偣鍑婚渶瑕佺紪杈戠殑鍗曞厓鏍硷細绗簩姝ワ細鍦ㄥ啘鍘嗕竴鏍忎腑锛岃緭鍏=text(闇瑕佺紪杈戠殑鍗曞厓鏍,鈥漑$-130000]yyyy-m-d鈥)锛堝潎鍦ㄨ嫳鏂囪緭鍏ユ硶涓嬭繘琛岋級锛岀劧鍚庣偣鍑荤‘瀹氾細绗笁姝ワ細鍗冲彲鐪嬪埌鏁堟灉鍙樺寲锛屼篃鍙繘琛岃嚜鍔ㄥ～鍏咃細鏈暀绋嬮傜敤鐗堟湰锛歐PS Office 2019 PC鐗  

Excel鍏ラ棬鏁欑▼浣跨敤鍑芥暟灏嗗叕鍘嗘棩鏈熻浆鎹负鍐滃巻
绛旓細鈶㈠鏋滄兂瑕佸畬鍏ㄤ互鍐滃巻褰㈠紡鏄剧ず鍑烘潵锛岄偅涔堝彲浠ヨ緭鍏ヤ笅鍒楀叕寮忥細=MID(" 鐢蹭箼涓欎竵鎴婂繁搴氳緵澹櫢",MOD(TEXT(NOW(),"[$-130000]e")-4,10)+1,1)&MID("瀛愪笐瀵呭嵂杈板烦鍗堟湭鐢抽厜 鎴屼亥",MOD(TEXT(NOW(),"[$-130000]e")-4,12)+1,1)&"骞"&TEXT(NOW()," [$-130000][DBNum1]m鏈坉鏃")杈撳叆...

Excel鎬庝箞灏嗗叕鍘鏃ユ湡杞负鍐滃巻
绛旓細鍦ㄥ啘鍘嗙殑鍗曞厓鏍间腑濉叆涓涓嬪嚱鏁板叕寮忋=TEXT(A2,[-130000]yyyy-m-d)褰撲腑A2琛ㄧず瑕杞崲鐨勫叕鍘鍗曞厓鏍硷紝yyyy-m-d琛ㄧず杞崲鏃ユ湡鐨勬牸寮忥紝鑰孾-130000]灏辨槸鍐滃巻杞崲鐨勫叧閿傛渶鍚庡洖杞︾劧鍚庡～鍏呭嵆鍙傝嫢浣犺涓鸿繖涓啘鍘嗙殑鏍煎紡涓嶅ソ鐪嬶紝娌℃湁涓浗浼犵粺鏄剧ず鐨勭壒鑹诧紝閭ｅ叕寮忓彲浠ユ敼涓鏀癸細=TEXT(A2,[-130000]yyyy骞磎鏈圛F(...

鍦‥XCEL涓妸鍏巻杞崲鎴鍐滃巻鐨勬柟娉曞強鎿嶄綔姝ラ鏄粈
绛旓細鍦‥XCEL涓妸鍏巻杞崲鎴鍐滃巻鐨勬柟娉曞強鍏蜂綋鎿嶄綔姝ラ濡備笅锛屼互WPS涓璄XCEL涓轰緥鍋氭紨绀恒1銆佸湪鐢佃剳涓墦寮闇瑕佹搷浣滅殑EXCEL琛ㄦ牸锛屽闇瑕佸皢A1鐨勬棩鏈熻浆鎴愬啘鍘嗭紝濡備笅鍥炬墍绀恒2銆佺偣鍑讳笂鏂硅彍鍗曟爮涓殑寮濮嬮夐」锛岃繘鍏ヤ笅涓椤甸潰銆3銆佺偣鍑诲彸渚х殑姹傚拰閫夐」涓嬫媺鑿滃崟锛岄夋嫨鈥滃叾浠栧嚱鏁扳濋夐」锛岀偣鍑昏繘鍏ヤ笅涓椤甸潰銆4銆佸湪寮瑰嚭鐨勫璇...

鐢佃剳Excel琛ㄦ牸鎬庝箞鍊熷姪鍑芥暟灏嗗叕鍘鏃ユ湡鍙樻垚闃村巻鏃ユ湡
绛旓細鍏蜂綋濡備笅锛1. 棣栧厛鎴戜滑鎵撳紑鐢佃剳涓殑涓浠excel琛ㄦ牸銆備互鍥句腑琛ㄦ牸涓轰緥銆2.鎴戜滑鍗曟満B2鍗曞厓鏍硷紝鍦ㄥ叾涓緭鍏hlunardate銆傚綋鎴戜滑鍙互鐩存帴鐐瑰嚮鍔熻兘鏍忎腑鐨勫嚱鏁伴夐」鐒跺悗鎵惧埌骞朵娇鐢ㄨ繖涓嚱鏁般3. 鐒朵箣鍚庢垜浠変腑A2鍗曞厓鏍笺4. 杈撳叆瀹屾垚涔嬪悗鎸変笅鍥炶溅閿嵆鍙垚鍔熶簡銆5.鎴戜滑鍙互鎵撳紑鐧惧害杩涜鎼滅储锛岃鐪嬫槸涓嶆槸鍗存槸濡傛锛6...

excel鎬庝箞杞崲鍐滃巻涓哄叕鍘
绛旓細Excel灏鍐滃巻杞崲涓哄叕鍘嗙殑鏂规硶濡備笅锛氭柟娉曚竴锛氫娇鐢═EXT鍑芥暟 1. 鍋囪A鍒楁槸鍐滃巻鏃ユ湡锛孉1鏄啘鍘嗘棩鏈燂紝鎴戜滑鍦˙1鍗曞厓鏍艰緭鍏ュ叕寮忥細=TEXT(A1,"[$-130000]yyyy骞磎鏈"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"鍒","")&"d")锛屼笅鎷夊畬鎴愬啘鍘杞叕鍘銆2. 姝ゅ叕寮忓皢鍐滃巻鏃ユ湡杞崲涓哄叕鍘嗘棩鏈燂紝濡傛灉鏃ユ湡鏄垵...

EXCEL鍏巻杞鍐滃巻鍏紡?
绛旓細Function iNlStr(iDate As Date) As String '鍏巻杞鍐滃巻 If Not tf Then Call 鍔犺浇鏁扮粍浠 Dim i%, t&, k%, m%, n%, ext%, bit& '璁＄畻浠1921-2-8(鍗1921骞寸殑姝ｆ湀鍒濅竴)鑷崇洰鏍囨棩鏈熺殑鎬诲ぉ鏁 t = iDate - #2/8/1921# + 1 '璁＄畻鍐滃巻骞存湀鏃ョ殑鏁板 Do If Val(sNlShu(m)) < 4095...

浣跨敤鍑芥暟鍦‥xcel2010涓皢鍏巻鏃ユ湡杞崲涓鍐滃巻
绛旓細鈶犲惎鍔Excel2010锛屽湪浠绘剰涓涓崟鍏冩牸杈撳叆涓嬪垪鍑芥暟锛氣=TEXT(NOW(),"[$-130000]YYYY-M-D")鈥濄   鈶¤鍗曞厓鏍肩珛鍗冲緱鍒颁竴涓棩鏈燂紝濡備笅鍥炬墍绀猴紝鍐滃巻鏃ユ湡涓哄叚鏈堝垵涓銆傝櫧鐒跺湪閬囧埌闂版湀鏃讹紝鏄剧ず缁撴灉鏈夌偣宸己浜烘剰锛屽涓嬪浘涓紝2010骞2鏈9鏃ョ殑鍐滃巻搴旀槸鍗佷簩鏈堜簩鍗佸叚锛屼絾鍥犱负...

扩展阅读：小学生自制日历2024 ... 农历公历转换器 ... 表格2020-03-01怎么设置 ... 表格1900年日期怎么改 ... 小学生手工制作日历 ... excel阴历转今年阳历 ... 怎么把年月日转成横杠 ... 公历转农历的方法 ... 农历转换公历小技巧 ...