How to calculate lunar calendar date with excel

Cell B2 is the first case. "20 16-9- 17" means the seventeenth day of the ninth lunar month. The formula is: = text (a2,' [$- 130000] yyyy-m-d').

TEXT () is used to convert the text format. The key of this formula is: [$- 130000], which is the parameter of converting solar calendar into lunar calendar in Excel. However, there is a problem that leap months cannot be calculated. It is estimated that foreigners can't understand China's concept of leap month. Every leap year directly means 13 months in a year, and the reason is the same.

Advanced edition:

C2 cells are advanced compared with B2 cells. It is expressed in Chinese, and the year is expressed in heavenly stems and earthly branches.

The formula is definitely more complicated than before: =MID ('A, b, d, e, n, n', MOD (text (A2,' [$- 130000] e')-4, 10) 1,/kloc. MID ('Zi Chou Mao Yinchen has applied for a letter at noon', mod (text (a2,' [$- 130000] e')-4, 12) 1,1)&; Year'&; Text (a2，'[$- 130000][DBM num 1]m month d day ')。

Can be divided into two parts:

1, calculation year: MID ('A, b, d, e, g, n, n'), mod (text (a2,' [$- 130000] e')-4,10)/kloc. [$-130000] e')-4,655 year', where a MID () text extraction function is added to obtain heavenly stems and earthly branches.

2. Calculate month and day: text (a2,' [$-130000] [db num 1] m month and d day'), in which the parameter [dbnum1] is added, indicating that the numerical value is in the Chinese format of "one, two, three ...". The combination of the two is the result of column C. As China people, we know that January is not called January, but the first month.

"December" is not called "December", but "twelfth month"; Similarly, the first 10 days of each month are the first day and the second day ... from 20th to 20th, from 2nd1to 30th, and so on.