电子表格中怎样将农历转换为公历

在EXCEL中怎样把公历转换成农历

Excel小技巧

这个需要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) =
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 = 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) ")"
calendar = NongliStr NongliDayStr
End Function

——————————————PS 在网上借鉴的相关资料

1、农历转换为公历，首先需要一个农历和公历的对照表
2、假设对照表的格式为：A列为农历，B列为公历。
3、此时可以用VLOOKUP函数进行转换，公式为：=VLOOKUP(需要转换的农历日期,A:B,2,0)
4、特别注意：需要转换的农历日期必须与对照表中A列格式一致。

tg65hhytrdgtvttg

在EXCEL中把公历转换成农历的方法及操作步骤是什
答：在EXCEL中把公历转换成农历的方法及具体操作步骤如下，以WPS中EXCEL为例做演示。1、在电脑中打开需要操作的EXCEL表格，如需要将A1的日期转成农历，如下图所示。2、点击上方菜单栏中的开始选项，进入下一页面。3、点击右侧的求和选项下拉菜单，选择“其他函数”选项，点击进入下一页面。4、在弹出的对话...

Excel怎么将公历日期转为农历
答：以下面这个表格为例子，左边是公历，右边是农历。在农历的单元格中填入一下函数公式。=TEXT(A2,[-130000]yyyy-m-d)当中A2表示要转换的公历单元格，yyyy-m-d表示转换日期的格式，而[-130000]就是农历转换的关键。最后回车然后填充即可。若你认为这个农历的格式不好看，没有中国传统显示的特色，那公式...

2010版excel农历转换公历怎样使用呢
答：不要对照表呀：如果公历在A1，B1=TEXT(A1,"[$-130000]e-m-d") 今天农历是：2018-7-24

农历转换
答：电子表格中怎样将农历转换为公历 1、农历转换为公历,首先需要一个农历和公历的对照表 2、假设对照表的格式为:A列为农历,B列为公历。 3、此时可以用VLOOKUP函数进行转换,公式为:=VLO畅KUP(需要转换的农历日期,A:B,2,0) 4、特别注意:需要转换的农历日期必须与对照表中A列格式一致。 在EXCEL中怎样把公历...

农历换算成阳历
答：农历转换成公历的计算公式? 因为公历(现在用的是格里历,以前还曾用儒列历)和中国农历都不是严格按一个公式计算得到的,所以两个历法没有直接的公式。 试图用一个简单的公式,而不利用许多像万年历一样的数据(尽管可以简化),就达到公农历转换的目的,这是不可能的。我们能见到的所有公农历转换的软件,都存有多少不...

在EXCEl中阴阳历生日转换及提醒
答：1.比如今天是2010年9月2日，用以下公式就可以得出农历七月二十四：2.虽然在遇到闰月时，显示结果有点差强人意，如下图中，2010年2月9日的农历应是十二月二十六，但因为那年有个闰五月，就无辜多出来一个月了。不过总比编代码要快捷多了，而且闰月也不是天天有。3.假如把公式改成：=MID("甲乙丙...

使用函数在Excel2010中将公历日期转换为农历
答：①启动Excel2010，在任意一个单元格输入下列函数：“=TEXT(NOW(),"[$-130000]YYYY-M-D")”。   ②该单元格立即得到一个日期，如下图所示，农历日期为六月初一。虽然在遇到闰月时，显示结果有点差强人意，如下图中，2010年2月9日的农历应是十二月二十六，但因为...

公历转农历公式,在EXCEL中怎样把公历转换成农历
答：因此，公历规定每4年里有1个闰年，将这一天加在天数最少的2月，故闰年的2月有29天。实际操作中为了方便，规定凡是能被4除尽的年份（末尾有两个0的要被400除尽），皆为闰年。如1996，2000等皆为闰年。公历转农历公式：公历农历转换 农历也是“历”，又称为夏历、阴历、旧历，是东亚传统历法之一...

excel表格如何显示农历日期
答：设置excel表格日期显示为农历的步骤如下：1、打开需要操作的excel表格，在A1单元格输入如下函数：“=TEXT(NOW(),"[$-130000]YYYY-M-D")”。2、单元格中可以观察到出现了一个日期：农历日期为六月初一。3、如果要将农历形式完全显示出来，就可以输入下列公式：=MID(" 甲乙丙丁戊己庚辛壬癸",MOD(...

C#中如何将农历转换为公历
答：例： ChineseLunisolarCalendar cls = new ChineseLunisolarCalendar();DateTime dt= cls.ToDateTime(2010, 1,1, 0, 0, 0, 0); 注意：碰到诸如闰月的情况，则润月后面的月份自动加一,既2009年润五月，则闰五月为6月，后面的月加一处理，实例中的运行结果为2010-2-14 ...