How does WPS form convert Gregorian calendar into lunar calendar date?

Assuming that column A is the Gregorian calendar date and A 1 is the Gregorian calendar date, we enter the formula in cell B 1: = text (A 1, "[$- 130000] YYY year m month"&; If (len (-text (a 1，"[$- 130000]DD ")= 1，" Chu "，" ")&； "d"), drop down to complete the conversion from Gregorian calendar to lunar calendar.

Excel solar calendar to lunar calendar method 2: Use the text+MID function to achieve a more complete conversion.

Column a is still Gregorian calendar, and B 1 input formula:

=MID ("A，B，D，E，E "，MOD (text (A 1，" $- 130000))-4， 10)+ 1)&； MID ("Zi Chou Mao Yinchen has arrived in Xu Hai at noon in Shen You", mod (text (a 1, "[$- 130000] e")-4, 12)+ 1, 1). Year "&; Text (a 1, "[$-130000] [dbmnum1] m month d day")

Do it.

The parameter [DBNum 1] is added here, indicating that the numerical value is displayed in the Chinese format of "one, two, three ...".

Excel solar calendar to lunar calendar method 3: use the text+MID+ CHOOSE+ YEAR function.

A 1 is the Gregorian calendar, and B 1 input:

= choose (mod (year (a1)-1900, 10)+ 1, "Geng", "Xin", "Man", "Ghost", "A" and "B" Text (a 1, "[dbnum1] [$-130000] year m month"&; IF( - TEXT(A 1，"[$- 130000]d ")& lt； 1 1, "Chu", ")&; TEXT(A 1，"[db num 1][$- 130000]d "))

You can display the first few days here.

Excel solar calendar to lunar calendar method 4: use custom functions.

The conversion from excel Gregorian calendar to Lunar calendar introduced above is completed by using excel built-in functions. One disadvantage is that the formula is too long. If you use user-defined functions, you can simplify the input in the formula column.

As shown in the figure below, we can enter the formula = Nongli (A 1) in B2. The formula of the lunar abbreviation C 1 for column c is = right (b 1, len (b 1)-8).

The following is the code related to this custom function:

Public? Function? Nongli (optional? XX_DATE？ As? Date)

Dim? MonthAdd( 1 1)，？ NongliData(99)，？ God damn it (9), Di Zhi (1 1),? Shuxiang (1 1),? DayName(30)，？ MonName( 12)

Dim? Curtis? Kornian? Ke Yue? Kurde

Dim? Gong Li? NongliStr？ Non-liDayStr

Dim? Me? m，？ n，？ k，？ isEnd？ Bit,? date

Get the current system time

Curtis? =? XX _ date

Tianganming

God damn it (0)? =? " A "

God damn it (1)? =? " B "

God damn it (2)? =? " C "

God damn it (3)? =? "ding"

Heavenly dry (4)? =? " E "

Heavenly dry (5)? =? "self"

Heavenly dry (6)? =? "Geng"

Heavenly dry (7)? =? "new"

Heavenly dry (8)? =? "Ren"

Heavenly dry (9)? =? "deck"

Name of earthly branch

Di Zhi (0)? =? "son"

Di Zhi (1)? =? "Ugliness"

Di zhi 2? =? "yin"

Di Zhi (3)? =? "Mao"

Di Zhi (4)? =? Chen

Di Zhi (5)? =? "thinking"

Di Zhi (6)? =? "noon"

Di Zhi (7)? =? "no"

Di Zhi (8)? =? "Shen"

Di Zhi (9)? =? "single"

Di Zhi (10)? =? "er"

Di Zhi (1 1)? =? "sea"

Animal constellation name

Shuxiang (0)? =? "mouse"

Shuxiang (1)? =? "Niu"

Shuxiang (2)? =? "tiger"

Shuxiang (3)? =? "rabbit"

Shuxiang (4)? =? "dragon"

Shuxiang (5)? =? "snake"

Shuxiang (6)? =? "Horse"

Shuxiang (7)? =? "sheep"

Shuxiang (8)? =? "monkey"

Shuxiang (9)? =? "chicken"

Shuxiang (10)? =? "Dog"

Shuxiang (1 1)? =? "pig"

Lunar date name

DayName(0)？ =? "*"

DayName( 1)？ =? "first grade"

DayName(2)？ =? The second day of junior high school

DayName(3)？ =? "third grade"

DayName(4)？ =? "fourth grade"

DayName(5)？ =? "the fifth day"

DayName(6)？ =? "the sixth day"

DayName(7)？ =? "the seventh day"

DayName(8)？ =? "the eighth day"

DayName(9)？ =? "the ninth day"

DayName( 10)？ =? "the tenth day"

DayName( 1 1)？ =? "Eleventh"

DayName( 12)？ =? "Twelve"

DayName( 13)？ =? Thirteen

DayName( 14)？ =? "Fourteen"

DayName( 15)？ =? Tenth five-year plan

DayName( 16)？ =? "Sixteen"

DayName( 17)？ =? "Seventeen"

DayName( 18)？ =? "Eighteen"

DayName( 19)？ =? "Nineteen"

DayName(20)？ =? "twenty"

DayName(2 1)？ =? "twenty-one"

DayName(22)？ =? "twenty-two"

DayName(23)？ =? "twenty-three"

DayName(24)？ =? "twenty-four"

DayName(25)？ =? "twenty-five"

DayName(26)？ =? "Twenty-six"

DayName(27)？ =? "twenty-seven"

DayName(28)？ =? "twenty-eight"

DayName(29)？ =? "Twenty-nine"

DayName(30)？ =? "Thirty"

Lunar month name

MonName(0)？ =? "*"

MonName( 1)？ =? "positive"

MonName(2)？ =? "two"

MonName(3)？ =? "three"

MonName(4)？ =? "four"

MonName(5)？ =? "five"

MonName(6)？ =? "six"

MonName(7)？ =? "seven"

MonName(8)？ =? "Eight"

MonName(9)？ =? "Nine"

MonName( 10)？ =? "ten"

MonName( 1 1)？ =? "Eleventh"

MonName( 12)？ =? "wax"

The first few days of each month in the Gregorian calendar

MonthAdd(0)？ =? 0

MonthAdd( 1)？ =? 3 1

Menghad (2)? =? 59

MonthAdd(3)？ =? 90

Menghad (4)? =? 120

Monthard (5)? =? 15 1

Menghad (6)? =? 18 1

Menghad (7)? =? 2 12

MonthAdd(8)？ =? 243

Monthard (9)? =? 273

MonthAdd( 10)？ =? 304

MonthAdd( 1 1)？ =? 334

Lunar data

NongliData(0)？ =? 2635

NongliData( 1)？ =? 333387

NongliData(2)？ =? 170 1

NongliData(3)？ =? 1748

NongliData(4)？ =? 26770 1

NongliData(5)？ =? 694

NongliData(6)？ =? 239 1

NongliData(7)？ =? 133423

NongliData(8)？ =? 1 175

NongliData(9)？ =? 396438

NongliData( 10)？ =? 3402

NongliData( 1 1)？ =? 3749

NongliData( 12)？ =? 33 1 177

NongliData( 13)？ =? 1453

NongliData( 14)？ =? 694

NongliData( 15)？ =? 20 1326

NongliData( 16)？ =? 2350

NongliData( 17)？ =? 465 197

NongliData( 18)？ =? 322 1

NongliData( 19)？ =? 3402

NongliData(20)？ =? 400202

NongliData(2 1)？ =? 290 1

NongliData(22)？ =? 1386

NongliData(23)？ =? 2676 1 1

NongliData(24)？ =? 605

NongliData(25)？ =? 2349

NongliData(26)？ =? 1375 15

NongliData(27)？ =? 2709

NongliData(28)？ =? 464533

NongliData(29)？ =? 1738

NongliData(30)？ =? 290 1

NongliData(3 1)？ =? 33042 1

NongliData(32)？ =? 1242

NongliData(33)？ =? 265 1

NongliData(34)？ =? 199255

NongliData(35)？ =? 1323

NongliData(36)？ =? 529706

NongliData(37)？ =? 3733

NongliData(38)？ =? 1706

NongliData(39)？ =? 398762

NongliData(40)？ =? 274 1

NongliData(4 1)？ =? 1206

NongliData(42)？ =? 267438

NongliData(43)？ =? 2647

NongliData(44)？ =? 13 18

NongliData(45)？ =? 204070

NongliData(46)？ =? 3477

NongliData(47)？ =? 46 1653

NongliData(48)？ =? 1386

NongliData(49)？ =? 24 13

NongliData(50)？ =? 330077

NongliData(5 1)？ =? 1 197

NongliData(52)？ =? 2637

NongliData(53)？ =? 268877

NongliData(54)？ =? 3365

NongliData(55)？ =? 53 1 109

NongliData(56)？ =? 2900

NongliData(57)？ =? 2922

NongliData(58)？ =? 398042

NongliData(59)？ =? 2395

NongliData(60)？ =? 1 179

NongliData(6 1)？ =? 2674 15

NongliData(62)？ =? 2635

NongliData(63)？ =? 66 1067

NongliData(64)？ =? 170 1

NongliData(65)？ =? 1748

NongliData(66)？ =? 398772

NongliData(67)？ =? 2742

NongliData(68)？ =? 239 1

NongliData(69)？ =? 33003 1

NongliData(70)？ =? 1 175

NongliData(7 1)？ =? 16 1 1

NongliData(72)？ =? 2000 10

NongliData(73)？ =? 3749

NongliData(74)？ =? 5277 17

NongliData(75)？ =? 1452

NongliData(76)？ =? 2742

NongliData(77)？ =? 332397

NongliData(78)？ =? 2350

NongliData(79)？ =? 3222

NongliData(80)？ =? 268949

NongliData(8 1)？ =? 3402

NongliData(82)？ =? 3493

NongliData(83)？ =? 133973

NongliData(84)？ =? 1386

NongliData(85)？ =? 4642 19

NongliData(86)？ =? 605

NongliData(87)？ =? 2349

NongliData(88)？ =? 334 123

NongliData(89)？ =? 2709

NongliData(90)？ =? 2890

NongliData(9 1)？ =? 267946

NongliData(92)？ =? 2773

NongliData(93)？ =? 592565

NongliData(94)？ =? 12 10

NongliData(95)？ =? 265 1

NongliData(96)？ =? 395863

NongliData(97)？ =? 1323

NongliData(98)？ =? 2707

NongliData(99)？ =? 265877

Generate the current Gregorian calendar year, month and day? = = & gt？ Gong Li site

Kornian? =? Year (Deadline)

Ke Yue? =? Month (time)

curDay？ =? Day (off-duty time)

Gong Li? =? Kornian? & amp？ "year"

What if? (curMonth？ & lt？ 10)? then

Gong Li? =? Gong Li? & amp？ "0"? & amp？ Ke Yue? & amp？ "Moon"

other

Gong Li? =? Gong Li? & amp？ Ke Yue? & amp？ "Moon"

End? if

What if? (curDay？ & lt？ 10)? then

Gong Li? =? Gong Li? & amp？ "0"? & amp？ curDay？ & amp？ "day"

other

Gong Li? =? Gong Li? & amp？ curDay？ & amp？ "day"

End? if

Start time1921Days from February 8th: 192 1-2-8 (the first day of the first month).

Date? =? (curYear？ -? 192 1)? *? 365? +? Int((curYear？ -? 192 1)? /? 4)? +? curDay？ +? MonthAdd(curMonth？ -? 1)? -? 38

What if? ((curYear？ Mod？ 4)? =? 0? And then what? Ke Yue? & gt？ 2)? then

Date? =? Date? +? 1

End? if

Calculate the heavenly stems and earthly branches month and day of the lunar calendar.

isEnd？ =? 0

m？ =? 0

do

What if? (NongliData(m)？ & lt？ 4095)? then

k？ =? 1 1

other

k？ =? 12

End? if

n？ =? k

do

What if? (n？ & lt？ 0)? then

Quit do

End? if

Gets the value of the nth binary bit of NongliData(m)

Bit? =? NongliData(m)

For what? Me? =? 1? Where to? n？ One step? 1

Bit? =? Int(bit？ /? 2)

then

Bit? =? Bit? Mod？ 2

What if? (Date? & lt=? 29? +? bit)？ then

isEnd？ =? 1

Quit do

End? if

Date? =? Date? -? 29? -? a little

n？ =? n？ -? 1

ring

What if? (isEnd？ =? 1)? then

Quit do

End? if

m？ =? m？ +? 1

ring

Kornian? =? 192 1? +? m

Ke Yue? =? k？ -? n？ +? 1

curDay？ =? date

What if? (k？ =? 12)? then

What if? (curMonth？ =? (Int(NongliData(m)？ /? 65536)? +? 1))? then

Ke Yue? =? 1? -? Keyue

Elser? (curMonth？ & gt？ (Int(NongliData(m)？ /? 65536)? +? 1))? then

Ke Yue? =? Ke Yue? -? 1

End? if

End? if

Generate the heavenly stems and earthly branches Zodiac of the Lunar New Year? = = & gt？ Nonlister

NongliStr？ =? "Lunar calendar"? & amp？ God damn it ((curYear? -? 4)? Mod？ 60)? Mod？ 10)? & amp？ Di Zhi ((curYear? -? 4)? Mod？ 60)? Mod？ 12)? & amp？ "year"

NongliStr？ =? NongliStr？ & amp？ "("? & amp？ Shuxiang ((curYear? -? 4)? Mod？ 60)? Mod？ 12)? & amp？ ")"

Generate lunar month and day? = = & gt？ Non-liDayStr

What if? (curMonth？ & lt？ 1)? then

NongliDayStr？ =? Leap & MonName(- 1？ *? curMonth)

other

NongliDayStr？ =? MonName (new moon)

End? if

NongliDayStr？ =? NongliDayStr？ & amp？ "Moon"

NongliDayStr？ =? NongliDayStr？ & amp？ Japanese name

Nong Li? =? NongliStr？ & amp？ Non-liDayStr

End? function