EXCEL solar calendar to lunar calendar formula?

Excel built-in functions can not be perfectly implemented, at least there will be problems in leap months.

You can only consider using VBA programming to customize a function.

Figure 1: effect display figure:

Effect display diagram

Figure 2: Schematic diagram of steps:

Schematic diagram of steps

Figure 3: Complete code diagram:

Steps:

1. If the file is in. Xlsx format, which must be saved as a macro-enabled workbook. Xlsm format.

2. Press Alt+F 1 1 to open the code editor.

3. Click "insert"-"module",

4. Double-click the module in the left column.

5. Paste the code into the editing area on the right.

6. When reporting an error, check whether the code turns red.

7. Close the code editor.

Important note:

Description 1. For the code copied from here, Baidu will insert extra blank lines between lines. If the code turns red due to an error, you can delete blank lines between lines.

Note 2. This code is not omnipotent. It is limited by the date range. It can only be converted from the first day of the first month of 192 1 to the thirtieth day of the twelfth month of 2 100, and the corresponding Gregorian calendar range is 192 1/2/8 to 2 100/2.

Note 3. When using a formula in a table, you can indicate the date directly in the formula or refer to the date in the cell.

Example 1, specific conversion date:

=iNlStr (date (August, 2020, 18))

Example 2, forever convert today's date:

=iNlStr(TODAY())

Example 3, conversion from cell reference date:

=iNlStr(A 1)

The code is as follows for you to copy:

Option explicit

Take public tf as Boolean value'

Common sZhouJ as variant "week array"

Variant of "Ten Days Dry Array"

Common sDiZhi as a variant' twelve-branched array'

Male sSXiao is a variety' Zodiac Array'

Public sNlMon as a variant of "Lunar Month Array"

As a variant of the public day "Lunar Day Array"

Public sNlShu "Lunar Data Array" as a Variant

Subload array ()

SZhouJ = Split("*/ one/two/three/four/five/six/day ","/") week array

Stigan = split ("A/B/C/D/E/F/G/F/N/D", "/") celestial array.

SDiZhi = Split ("Zi/Ugly/Yin/Mao/Chen/Si/Wu/Wei/Shen/You/Xu/Hai", "/)' geocentric array

SSXiao = Split ("Rat/Cattle/Tiger/Rabbit/Dragon/Snake/Horse/Sheep/Monkey/Chicken/Dog/Pig", "/)' Zodiac Array.

SNlMon = Split("*/ first month/February/March/April/May/June/July/August/September/October/winter/twelfth month ","/") month array.

sNlDay = Split( _

"*/grade1/2/3/4/5/6/7/8/9/10" u

& "/Eleven/Twelve/Thirteen/Fourteen/Fifteen/Sixteen/Seventeen/Eighteen/Nineteen/Twenty"

& amp“/2 1/22/23/24/25/26/27/28/29/30”、“/””

sNlShu = Split( _

" 002635/333387/00 170 1/00 1748/26770 1/000694/00239 1/ 133423/0 1 175/396438/" & amp； _

" 003402/003749/33 1 177/00 1453/000694/20 1326/002350/465 197/00322 1/003402/" & amp； _

" 400202/00290 1/00 1386/2676 1 1/000605/002349/ 1375 15/002709/464533/0065438/" & amp； _

" 00290 1/33042 1/00 1242/00265 1/ 199255/00 1323/529706/003733/00 1706/398762/" & amp； _

" 00274 1/00 1206/267438/002647/00 18/204070/003477/46 1653/00 1386/0024 13/" & amp； _

" 330077/00 1 197/002637/268877/003365/53 109/002900/002922/398042/002395/" & amp； _

" 00 1 179/2674 15/002635/66 1067/00 170 1748/398772/002742/00239 1/33003 1/"_

" 00 1 175/00 16 1/2000 10/003749/5277 17/00 1452/002742/332397/002350/003222/" & amp； _

" 268949/003402/003493/ 133973/00 1386/4642 19/000605/002349/334 123/002709/" & amp； _

" 002890/267946/002773/592565/00 10/00265 1/395863/00 1323/002707/265877/" & amp； _

" 00 1706/002773/ 133557/00 1206/397998/002638/003366/335 142/034 1 1/00 1450/" & amp； _

" 200042/0024 13/723293/00 197/002637/399947/003365/0034 10/334676/002906/" & amp； _

" 00 1389/ 133467/00 179/464023/002635/002725/333477/00 1746/002778/ 199350/" & amp； _

" 002359/526639/00 1 175/06 16 1/3966 18/003749/00 17 14/267628/002734/002350/" & amp； _

" 203054/003222/465557/003402/003493/33058 1/00 1386/002669/264797/00 1325/" & amp； _

" 529707/002709/002890/3990 18/002773/00 1370/267450/00265 1/00 1323/202023/" & amp； _

" 00 1683/4624 19/00 1706/002773/330 165/00 1206/002647/264782/003366/53 1750/" & amp； _

"0034 10/003498/396650/00 1389/00 1 198/26742 1/002637/003349/ 13800"

When marked as "Tf = True", the array has been loaded.

End joint

The function in lstr (idate as date) is the string "solar calendar to lunar calendar".

If it is not tf, the load array is called

Dim i%，t & amp，k%，m%，n%，ext%，bit & amp

Calculate the total number of days from 192 1-2-8 (that is, the first day of the first month in 192 1) to the target date.

t = iDate-# 2/8/ 192 1 #+ 1

Calculate the values of lunar year, month and day.

do

if Val(sNlShu(m))& lt； 4095 then k = 1 1 otherwise k = 12.

n = k

do

Get the value of the nth binary bit of sNlShu(m)

bit = Val(sNlShu(m))

For i = 1 to n

Bit = bit \ 2

then

bit = bit Mod 2

Calculate the number of days in the lunar calendar

If t<= 29+bit

ext = 1

Exit Do

If ... it will be over.

T = t-29 bit

n = n - 1

Loop until n<0.

If it is ext, then exit Do.

m = m + 1

Loop until an error occurs.

Calculate three values of the lunar calendar

Dim cy%，cm%，cd%

cy = 192 1 + m？ The value of the lunar year

Cm = k-n+ 1' lunar month value.

cd = t？ The value of lunar calendar day

If k = 12, then

Select caseval (snlshu (m)) 65536+1'to correct the value of cm.

The case is = cm.

Cm = 1 cm

The case is< cm.

cm = cm - 1

End selection

If ... it will be over.

Computing Chinese text

Dim ar( 1 to 5) as a string.

M = ((cy-4) Mod 60) Mod 10' heavenly stem index value.

N = ((cy-4) Mod 60) Mod 12' index value of earthly branch and zodiac.

If cm< 1 and then ar( 1) = "leap "

ar( 1)= ar( 1)& amp； SNlMon(Abs(cm))' Lunar month

Ar(2) = sNlDay(cd) "Lunar Day"

Ar(3) = sTiGan(m) and ampsDiZhi(n)? Trunk and branches of the moon

ar(4) = sSXiao(n)？ Lunar zodiac

Ar(5) = sZhouJ(Weekday(iDate, 2))' Zhou Chinese character

Match the output results (hint: the array ar( 1 to 5) has 5 elements, so you can freely match the output results as required).

INlStr = ar(3) and amp "Year Zodiac"&; Ar(4) and amp "& ampar (1) and ampar(2) and amp" Zhou "&; Ar(5)' output result

End function