公历转换成农历,怎么分清楚阴历阳历

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

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

WPS表格怎么将公历转化成农历日期?

excel公历转农历方法一：使用text函数

假设A列是公历日期，A1是公历日期，我们在B1单元格输入公式：=TEXT(A1,"[$-130000]yyyy年m月"&IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")&"d")，下拉完成公历转农历。

excel公历转农历方法二：使用text MID函数实现更完整的转换

A列仍然是公历，B1输入公式：

=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-130000]e")-4,10) 1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-130000]e")-4,12) 1,1)&"年"&TEXT(A1,"[$-130000][DBNum1]m月d日")

即可。

这里增加了参数[DBNum1]，表示数值以“一、二、三......”中文格式显示。

excel公历转农历方法三：使用text MID CHOOSE YEAR函数

A1为公历，B1输入：

=CHOOSE(MOD(YEAR(A1)-1900,10) 1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(A1)-1900,12) 1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")&TEXT(A1,"[dbnum1][$-130000]年m月"&IF(--TEXT(A1,"[$-130000]d")<11,"初","")&TEXT(A1,"[dbnum1][$-130000]d"))

这里可以对初几进行显示

excel公历转农历方法四：使用自定义函数

上面介绍的excel公历转农历都是使用excel内置函数完成的，有一个弊端就是公式太长，如果使用自定义函数，在公式栏就可以简化输入。

如下图所示，我们在B2输入公式：=nongli(A1)，即可。C列农历简称中C1公式是：=RIGHT(B1,LEN(B1)-8)。

下面是此自定义函数相关的代码：

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

'获取当前系统时间

curTime = XX_DATE

'天干名称

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(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

'计算到初始时间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)

NongLi = NongliStr & NongliDayStr

End Function

公历怎么转农历

阳历和阴历之间是不能简单换算的，二者采用的计算方法不同，没有特殊关系可以查询，只能分别计算。

公历是阳历的一种，阳历就是以地球绕太阳公转的运动周期为基础而制定的历法。

中国农历中每月的天数是根据月亮运动周期确立的,平均每月29.5306日。一年12个月只有354.3672天。而以地球围绕太阳公转时间确定的公(阳)历,一年有365.2422天。这样,按农历计算,一年的天数比公历就少了10.875天,差不多是11天;19年就少了209天。按农历每月30天计,209天除以30是6.9666。也就是说,在19年中,农历时间比公历差不多少了7个月。为了补上7个月,农历采取了设置闰月的方法——19年七闰。即在农历的19年中,有12个每年12个月的平年,和7个每年13个月的闰年。闰年分别为第3、5、8、11、14、17、19年。这样一来,19个公历年的日数和19个农历年的日数就差不多相等了

这样一算,公历所对应的农历日子,每19年轮回一次。因此,人们每逢19的倍数生辰的时候,生日当天有可能既是农历生日又是公历生日。但因为农历与公历的准确天数都不是整数,农历和公历在19年中的天数也有细微的差别。经过几十年甚至上百年的积累,当小数变成了整数时,两种历法生日才会完全重合;更多的时候是相差一天。另外值得一提的是,2月29日出生的人,在76岁那年,两种历法生日才有可能重合.我就是没有重合的那一个。

公历和农历是如何换算的?

首先，进行公历农历换算必须将天干、地支编号记住：天干：甲(4)、乙(5)、丙(6)、丁(7)、戊(8)、己(9)、庚(10)、辛(1)、壬(2)、癸(3)。地支：子(4)、丑(5)、寅(6)、卯(7)、辰(8)、巳(9)、午(10)、未(1)、申(2)、酉(3)、戌(4)、亥(5)。

如要将公历农历换算即将公历换成干支纪年，以公元年的尾数在天干中找出所对应的。然后，将公元纪年除以12，用余数在地支中找出所对应的地支。这样，公元纪年就换算成了干支纪年。如：公元2004年是干支纪年的什么年？这里教你一个较简单的换算方法。

进行公历农历换算首先要先记准十个天干和十二个地支，再记住一个历史事件的公元纪年和干支纪年分别是哪年，如辛亥革命是1911年。之后就可以以此为借助点来计算。如：1911年是辛亥年，问你2009年公历农历换算后是干支纪年的什么年？换算步骤如下：(1)先2009-1911=98，再98÷10=9余8，用这个余数8定天干。（也就是假如没有余数那2008年的天干就和1911年的天干是一样的即是“辛”假如余数是1，那2009年的天干就是在1911年天干“辛”的后面一个字，即是“壬”。以此类推之。）也就是2009年的天干是“辛”后第8个字即“己”。0用98÷12=8余2，用这个余数2定地支。（方法同上）也就是2009年的地支是“亥”后第二个字即“丑”。把天干和地支合在一起即2009年的干支纪年是己丑年。

农历,阴历,阳历,公历都怎么区分啊??哪些是同一个意思??

农历=旧历=阴历是根据月亮的运行来记年，英文里叫lunar calendar. 我们国家传统的记年都是用农历，过节都用农历来表示，称其为农历是因为农民根据阴历来决定播种收获的时间，而且表明了具体的节气，比如雨水，霜降，小雪，大雪，三伏，三九，等等。这些都是为了农活的方便制定的，所以我们国家一直重视农业的发展。根据月亮的运行调节节气的变化。

阳历=日历=公历是根据太阳的运行来记年，英文里就叫calendar. 中西接轨，所以我们也开始用公历。但老外比较*，只知道识别春夏秋冬四季，所以春分，夏至，秋分，冬至都是固定的日子。

阴历和阳历怎么区分

阳历就是国际上通行的历法，也叫公历

而阴历是中国等一些国家由农业耕作的规律总结出来的历法，也叫农历

区分的方法很简单，日历上一般都是同时写出阳历和阴历的。写数字的就是阳历，比如10月31日。写汉字的就是阴历，比如八月初一

阴历的月份比阳历的月份晚将近两个月。比如阳历12月，阴历是10月