Calculate lunar dates with Excel

1. YYYYY, mm, dd, and dd day conversion.

If we meet 2018-09-01, this kind of date, how should we convert it?

yyyyymmdd to yyyymmdd

Formula: =text(a2,"yyyymmdd")」

yyy mm month dd day to yyyy mm dd

Formula: =text(a2,"yyyy/mm/dd")」

yyyyymmdd to yyyymmdd

Formula: =text(a2,"yyyy-mm-dd")」

YYYY MM dd day turn.

Formula: =text(a2,"")」

yyyy, mm, dd, day, yyyydd

Formula: =text(a2,"")」

Description: text: Converts a numeric value to text in a specified number format.

yyyymmdd: indicates the year, month, and day.

2. YYYY mm DD conversion.

If your date looks like this: 2018-09-01, how do you convert it to something else? Actually, it's the same method as the first one, so I won't repeat it here.

3. yyyymmdd conversion.

This type is 20180901 if the date entered. Conversion Method:

yyyymmdd to yyyyymmdd

Formula: =mid(a2,1,4)&"years"&mid(a2,5,2)&"month"&mid(a2,7,2)&"day"」

yyyymmdd to yyyy-mm-dd

Formula: =mid(a2,1,4)&"mid(a2,5,2)&"mid(a2,7,2)」

yyyymmdd turn.

Formula: =mid(a2,1,4)&"mid(a2,5,2)&"mid(a2,7,2)」

Note: mid(a2,1,4): Gets the last 4 bits starting from the first one.

mid(a2,5,2): Gets the last 2 digits from the fifth.

mid(a2,7,2): Gets the last 2 digits from the seventh.

4. Conversion. If the date is entered with a dot, such as this, it is actually the same as the third method. Here's how to convert:

Transferred to yyyyy, mm, dd, day.

Formula: =mid(a2,1,4)&"years"&mid(a2,6,2)&"month"&mid(a2,9,2)&"day"」

Turn yyyy-mm-dd

Formula: =mid(a2,1,4)&"mid(a2,6,2)&"mid(a2,9,2)」

Turn yyyy-mm-dd

Formula: =mid(a2,1,4)&"mid(a2,6,2)&"mid(a2,9,2)」

1.For example, today is September 2, 2010, and the following formula can be used to get the 24th day of the seventh lunar month: 2

Although the results are a bit unsatisfactory when encountering a leap month, as shown in the figure below, the lunar calendar on February 9, 2010 should be December 26, but because there was a leap month in that year, there was an extra month. But it's much faster than making up **, and leap months don't happen every day. 3.

If you change the formula to: =mid("A, B, C, D, 戊, Ji Geng, Xin Ren, Guizi, Chou Yin, Mao Chen, Si Wu, Wei Shen You, Xuhai",mod(text(a2,"[130000]e")-4,12)+1,1)&"years"&text(a2,"[130000][dbnum1]m on the tenth day of the month",if(--text(a2,"[130000]d")>10,"[130000][dbnum1]d","Chu[$-130000][dbnum1]d"It's more like a lunar notation, but because the start date of the lunar calendar is different every year, it's difficult for the function to achieve very accurate results, so it can only be used.

Select the "days360" function. Detailed steps:

1. Open Excel**.

2. Enter the date you want to calculate.

3. Click "Formula" on the taskbar at the top and select the insert function.

4. Select "Date & Time" in the category.

5. Select the "days360" function.

6. According to the instructions, enter the start and end times into ** respectively.

7. Then the date is automatically calculated, and the other dates are calculated in the same way.

In Excel, you can add and subtract dates, and the specific operation process is as follows:

1. First of all, open the excel software, enter the date that needs to be calculated in the cell, and the date format is: year, month, day.

2. Enter the equals sign on the blank cell, click the final date with the mouse, enter the minus sign, and click the initial date with the mouse.

3. After the input is completed, click enter to display the number of days between the two dates.

4. Enter the equals sign on the blank cell, click the start date with the mouse, enter the plus sign, and click the final date with the mouse.

5. After the input is completed, click enter to display the total number of days between the two dates.

The steps to set the Excel date to be displayed as lunar are as follows:

1. Open the Excel** that needs to be operated, and enter the following function in cell A1: "=text(now(),130000]yyyy-m-d")”

2. The cell can be lit up to observe the appearance of a date: the date of the agricultural defeat cherry blossom calendar is the first day of June.

3. If you want to fully display the dry and wide form of the lunar calendar, you can enter the following formula: =mid("A, B, C, D, 戊, Ji Geng, Xin Ren, Guizi, Chou Yin, Mao Chen, Si Wu, Wei Shen You, Xuhai",mod(text(now(),130000]e")-4,12)+1,1)&"years"&text(now(),130000][dbnum1]mmonthdday")。

Steps. 1. First open Excel** and enter some time.

2. Then enter the formula =hour(h2)*60+minute(h2)+second(h2) 60 on the right, hour: the number of hours that return the time value, minute: the minutes that return the time value, second(time):

The number of seconds in which the time value is returned.

3. Then press and hold the enter key and find that the result appears, click on the regular and change the time to the normal time point.

4. At this time, the routine is changed to minutes.

5. Drag the mouse down to get all the results out.

Summary. Hello dear, you can convert lunar calendar date to solar calendar date with formulas in excel. Here is the formula and method to convert a lunar date to a solar date:

1.Enter the lunar calendar date first. Assuming the lunar date is in cell A1 in the format of "Year, Month and Day" (for example, on the first day of the first month of the first month of 2022, enter "January 1, 2022" in cell A1), then the following formula can convert the lunar date to the solar calendar date:

text(lunar date,"[0804] yyyyyyear m month d day"Among them,"[0804]"Indicates the use of China Standard Time"YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY"Indicates that the format of the output is year, month, and day. For example, if you apply a formula to cell B1, the cell will read: February 1, 2022.

2.If you need to enter multiple lunar dates and convert them all to solar calendar dates, you can use this formula to put the lunar dates in cells A1 to A3: text(Lunar date1,"[0804] yyyyyyear m month d day"&text(Lunar date 2,"[0804] yyyyyyear m month d day"&text(Lunar date 3,"[0804] yyyyyyear m month d day")`

Hello dear, you can convert lunar calendar date to solar calendar date with formulas in excel. Here are the formulas and methods to convert lunar dates to solar calendar dates:1

Enter the lunar calendar date first. Assuming that the lunar calendar date is located in cell A1 in the format of "year, month and day" (for example, on the first day of the first month of the first month of 2022, enter "January 1, 2022" in the upgraded cell A1), then the following formula can convert the lunar date to the solar calendar date: text(lunar date,"[0804] yyyyyyear m month d day"Among them,"[0804]"Indicates the use of China Standard Time"YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY"The format of the output is year, month, and day.

For example, if you apply a formula to cell B1, the cell will read: February 1, 2022. 2.

If you need to enter multiple lunar dates and convert them all to solar calendar dates, you can use this formula to put the lunar dates in cells A1 to A3: text(Lunar date1,"[0804] yyyyyyear m month d day"&text(Lunar date 2,"[0804] yyyyyyear m month d day"&text(Lunar date 3,"[0804] yyyyyyear m month d day")`

Use this formula to put the lunar calendar date in cells A1 to A3, apply the formula in cell B1, this cell will be displayed as: February 1, 2022, February 2, 2022, February 3, 2022. The above is the method and formula for converting the lunar calendar to the solar calendar, I hope to help you complete the corresponding operation.

If you have any other questions, please feel free to ask me.