2013日历 Excel版

2011日历 Excel版 [含标准农历算法]

Calendar in a Worksheet

简介

本日历已套用标准农历算法,能够有效计算1900年到2020年万年历。配有阳历和农历对照。全年365天显示。 使用Excel系统日期与公式运算而组成。可以打印, 画面、颜色、文字、字形等更可以个人化。模板能够永久再用，只需要输入年份和修正有关图画。

使用指南

1只需要在"日历"工作表单元方位 C13 "=DATE(2010,1,1)" 内的年份兑换为 2011 或其他年份便可, 或依照阁下的计算机日期格式直接输入日期, 例如 2011-1-1 或 1-1-2011。

2全年365天日历将会按照阁下所输入的年份而自动更新和显示。

3Excel日期显示格式是根据系统区域设定而定, 而且每台计算机的区域设定也许不同。 如果日期显示发生错误只好自行修正便是, 或者可以从正常显示单元格拷贝过去(使用复制>选择性贴上>格式/copy--paste special>format)。

4农历资料库储藏在"Nongli" 工作表里。请不要移动或更改 "Nongli" 工作表里面的资料, 以避免链接或公式发生错误。新修订版套用了标准农历算法 (拷贝了共享资料里的农历算法), 它现在能够有效的计算 1900年 到 2020年万年历。所使用的农历资料库可以在 2020年后再更新。

5农历的大小月是经过推算决定的，把朔望月的时间作为历月的平均时间,运用了设置闰月的办法和二十四节气的办法计算。农历是根据月亮的朔望月共12个月算一年，一年是354天。每三年有一个闰年。闰年是383天或384天。农历把月亮圆缺循环一次的时间算做一个月，12个月算一年。然而月亮圆缺循环一次=一个朔望月，是29天12时44分3秒，比29天多，又比30天少。为方便，农历把月份分成大月和小两种，逢单的月是大月30天，逢双的月是小月29天，一年共是354天。变数多端, 因此农历是使用资料库方式处理。

6阳历，是以地球绕太阳运动作为根据的历法。它以地球绕太阳一周（一回归年）为一年。 一回归年的长度是365.2422日，也就是365天5小时48分46 秒，积累4年共有23小时15分4秒，大约等于一天，所以每4年增加1天，加在2月的末尾，得366天，就是闰年。但是4年加1天实际回归年多了44分 56秒，积满128年左右就又多算了一天，也就是在400年中约多算了3天。阳历已内建在Excel系统里。

7可以随意在公共假期,节日等日期自行更改它们的字体或颜色来突出显示。

8需要手动修正有关 “wordart” 图画所显示的年份。 图画、颜色、文字、字形等等可以自行替换,设计或改造。

9关于阳历的算法, 每个月份的最后一天是一个变数。

能够正确地显示日历的关键是在于计算每个月份的最后一天。要计算每个月份的最后一天也不难，只是需要利用下一个月份的第一天再减去一天便可以计算出来。在"Calendar"工作表单元方位 C17 所用的公式是 =DATE(YEAR(年份),MONTH(月份)+1,0).

在日期 (年，月, 日)公式内的日期输入(年，月, 0日) (第一天 =1, 减去一天 = 1-1 =0; 因此在日期输入0)。在月份输入 m+1 也就是按照阁下所需要的月份 +1, 就可以获得下一个月份的数据 。

User Manual

1In the "Calendar" Worksheet, change the value of the year in cell C13 "=DATE(2010,1,1)"

from 2010 to 2011 etc (or simply enter a date in the format of 1-1-2011 or 2011-1-1 etc depending on the default date format of your computer).

2All dates in the calendar will be automatically updated and displayed.

3Do not move or delete cells in the Worksheet "Nongli", in order not to cause any error in formulae or links. Nónglì = Traditional Chinese Calendar.

Traditional Chinese Calendar is now fully formula based, the file is now halved its

original size! It now has an effective Traditional Chinese Calendar from year 1900-2020.

As the Traditional Chinese Calender runs in pre-determined cycle, the database may be appended if required.

4Public Holidays are high-lighted / edited manually.

5All texts, font colours, pictures and values may be edited. Double click on the text in WordArt to change the text.

6Last Day of the Month calculated from the 1st day of next month less one day. The first day of the month is obtained using the date format "date(year,month,0)". Day is entered as 0 or 1-1 since the first day is 1 and less 1 day equals 0.

=DATE(YEAR(B5),MONTH(B5)+1,0)

Enter Zero in the day will return the last day of the previous month!!!

7The key to displaying the calendar correctly lies in the calculation of the last day of each month which is a variable, the last day of each month can be easily obtained by

taking the first day of the following month less 1 day.

8The Calendar is now fully formula based, with New Look Portrait & Landscape versions J.H. Lim

jhl008@https://www.sodocs.net/doc/ee14140666.html,

Last Revision 10-11-2009

参考资料

Put Your Calendar in an Excel Worksheet

From the June 1997 Issue of PC World

I've had requests recently from readers asking how to set up a basic calendar in a

spreadsheet. All it takes is a few formulas and a bit of fancy formatting. This month we'll create a calendar in Excel. Next month we'll show how to copy information from a spreadsheet database into your calendar.

Our example was constructed in Microsoft's Excel 97; however, Excel 5 and 95 use the same instructions.

1. Open a new worksheet, double-click the current sheet's tab, and name it Calendar. In

cell A1, enter the first day of the month that you want your calendar to display--for example, 7/1/97. Select Format?Cells, and in the Format Cells dialog box, click the

Number tab, select Custom in the Category list, and enter mmmm yyyy in the Type edit box.

Before closing the dialog box, click the Font tab and choose an appropriately large font

2. Select A1:G1 and click the Center Across Columns (or Align Over Columns) button on the

Formatting toolbar.

3. Enter the numbers and the days of the week shown in rows 2 and 3.

4. In cell A4, type in this formula: = if (weekday($A$1)>A2,"",$A$1+A2-weekday($A$1)). Use the procedure described above to assign A4 the custom number format d, then copy A4 to B4:G4. This formula calculates the dates for the month, beginning on the appropriate

5. In A6, enter =G4+1. In B6, enter =A6+1 and copy it to C6:G

6. Then copy A6:G6 to A8:G8 and A10:G10. Don't worry about the number formats yet; we'll set those in a moment.

6. Since the month might end on any day in the last two weeks of the calendar (located in rows 12 and 14), we must create a formula-based range name: Select A1:G10, and choose Insert?Name?Define. Enter LastDay in the 'Names in Workbook' box. In the 'Refers to' box, enter the formula =calendar!$A$1+day(date(year(calendar!$A$1),month(calendar!$A$1),+1,1)-

7. Select rows 12 and 14 and choose Format?Cell. Make sure the Number tab is selected, then click Custom in the Category list and enter d in the Type edit box.

8. Now, in cell A12, enter the formula =if(A10+7>lastday,"",A10+7), and copy the formula to B12:G12.

9. In cell A14, enter =if(A10+14>lastday,"",A12+7) and copy it to B14:G14.

10. Now let's format the calendar. First, turn off the worksheet's grid lines: Select Tools?Options, make sure that the View tab is selected, uncheck Gridlines in the Windows Options section of the dialog box, and click OK. Next, select columns A through G, select Format?Column?Width, enter 16, and click OK. Select row 4, select Format?Cells, click the Font tab, set the point size to 14, and click OK. Select row 5, choose Format?Cells,

click the Alignment tab, set the Vertical Alignment to Top, click the Wrap Text box, and click OK. With row 5 still selected, choose Format?Row?Height, enter 54, and click OK. Select A3:G3, choose Format?Cells, click the Border tab, click Outline, and then click OK. Now click the Borders button on the Formatting toolbar and select a vertical alignment button from the resulting drop-down menu (which is shown here). Repeat the Format?Cells?Border command for A4:G5.

11. Finally, select rows 4 and 5, double-click the Format Painter tool, then in succession select rows 6, 8, 10, 12, and so on to complete the formatting. Click the Format Painter icon again to turn off the tool. As a finishing touch, you may want to hide the numbers in row 2: Select that row, then choose Format?Row?Hide.

12. Save your worksheet.

To create a calendar for a different month, just enter in cell A1 the date of the first day you want to display for the new month. If you want to make notations, enter them on the odd-numbered rows.

Find additional tips at https://www.sodocs.net/doc/ee14140666.html,/hereshow. Spreadsheets welcomes your tips and questions and pays $50 for published items.

Contributing Editor Richard Scoville is a software training consultant in Chapel Hill, North Carolina.