Динамический ежемесячный календарь Excel

Here's what I've come up with. Adjust instructions as needed to fit your own template.

Instructions

1. Set A1 to the value of the year for the month you want represented.
   • For the date of this post's first revision, it would be "2015".
2. Set A2 to the full name of the month you want represented.
   • For the date of this post's first revision, it would be "May".
3. Fill A3:G3 with the days of the week, starting with "Sunday" in A3 and ending with "Saturday" in G3.
4. Put the following formula in A4: =IF(WEEKDAY(DATEVALUE(CONCATENATE($A$2," 1, ",$A$1)))=1,1,"")
   • Depending on the Month/Year, this formula may return a blank value here. (It will for May 2015.) This is normal.
5. Put the following formula in B4: =IF(A4="",IF(WEEKDAY(DATEVALUE(CONCATENATE($A$2," 1, ",$A$1)))=COLUMNS($A$4:B4),1,""),A4+1)
   • Depending on the Month/Year, this formula may return a blank value here. (It will for May 2015.) This is normal.
6. Copy the formula from B4 across cells C4:G4. Excel should automatically adjust the cell references in the formula as needed.
   • At this point, the formula should evaluate to "1" in one of the cells in row 4, and each cell afterward should return an increment higher than the previous cell. Cells before the "1" should be blank. For May 2015, you should see blanks in A4:E4, "1" in F4 (Friday), and "2" in G4 (Saturday).
7. Put the following formula in A5: =IF(ISERROR(DATEVALUE(CONCATENATE($A$2," ",G4+1,", ",$A$1))),"",G4+1)
   • The formula result should be one higher than the value of G4.
8. Copy the formula in A5 down cells A6:A9. Excel should automatically adjust the cell references in the formula as needed.
   • A6:A9 will evaluate to blanks for now. That is normal.
9. Put the following formula in B5: =IF(ISERROR(DATEVALUE(CONCATENATE($A$2," ",A5+1,", ",$A$1))),"",A5+1)
   • The formula result should be one higher than the value of A5.
10. Copy the formula in B5 across cells C5:G5. Excel should automatically adjust cell references in the formula as needed.
    • Formulas in row 5 should now evaluate to a set of numbers increasing incrementally from column A to column G.
11. Select B5:G5 and copy those cells down through B6:G9. Excel should automatically adjust cell references in the formula as needed.
    • At this point, the calendar should be appropriately populated for the given month. Cells before the first of the month, and after the last day of the month, should be blank.
    • For certain months, such as January 2015, Row 9 will be entirely blank. This is normal.

Functions

• CONCATENATE joins text strings together.
• DATEVALUE returns an Excel "serial number" value for the date represented by a text string. It will return an error if the string does not match a known date format, or if the string does not represent an actual date (e.g.: "January 32, 2015" or "February 29, 2015").
• WEEKDAY returns a value, from 1 to 7, representing the weekday of a given date.
• COLUMNS returns the number of columns spanned by a specified array of cells.
• IF allows you to return a chosen value (or formula result) based on whether a specified formula evaluates to TRUE or FALSE.
• ISERROR returns TRUE if the given formula results in an error, or FALSE if the formula does not generate an error.

Formula Explanations

• A4 assembles information from A1 and A2 to generate a text string representing the first day of the month. (e.g.: "May 1, 2015".) Then, it checks to see if the weekday of that date would be Sunday. Returns 1 if it is a Sunday, blank if not.

• B4:G4 check the preceding cell to see if it is blank. If not, returns a value one higher than the previous cell. If the previous cell is blank, checks to see if the current weekday matches the first day of the given month - returns 1 if so, blank if not.

• A5:A9 add 1 to the value from the end of the preceding row, then combines the result with information from A1 and A2 to form a date string. Checks to see if the date actually exists. Returns the day-of-month number if so, blank if not.

• B5:G9 add 1 to the value from preceding cell, then combines the result with information from A1 and A2 to form a date string. Checks to see if the date actually exists. Returns the day-of-month number if so, blank if not.

Screenshot

Example output for May 2015.

Create New Workbook from template. Calendar sunday search string.

Select days area and press Manage Rules in Conditional Formatting menu.

Add New Rule

Add rule equal =TODAY()

Result:

Введите эти элементы в указанные ячейки:

A1: 2015 B1: первый день C1: = ДАТА ЗНАЧЕНИЯ (A1 & "-" & A2 & "- 01") A2: 5 B2: неделя-воскресенье C2: = C1-WEEKDAY (C1; 2)  C4: = ТЕКСТ (КОЛОННЫ ($ A $ 4: A4); "Ddd") C5: = IF (ТЕКСТ ($ C $ 2-1 + КОЛОННЫ ($ A $ 4: A4) + (ROWS ($ A $ 4: A4) -1) * 7; «ГГГГ-ММ») = ТЕКСТ ($ C $ 1; "YYYY-MM"); $ C $ 2-1 + КОЛОННЫ ($ A $ 4: A4) + (ЧСТРОК ($ A $ 4: А4) -1) * 7; "")  

Затем скопируйте C4 и C5, чтобы заполнить семь (7) ячеек шириной.
Затем следует копия строки 7, чтобы заполнить в общей сложности шесть (6) строк.
Выделите ячейки в последних 6 строках и нажмите CTRL+ 1, нажмите «Пользовательский» на левой панели и введите один Dдля пользовательского формата.

Результат будет выглядеть так:

2015 первый день 2015-05-01  5 неделя-воскресенье 2015-04-26   Вс пн вт ср чт пт сб 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31  

Это будет работать в Excel, LibreOffice и, скорее всего, в любой другой электронной таблице.

Linux:

Open a terminal, then type:

ncal 2015 and press ENTER (at the $ prompt).

The output will look like:

 2015 January February March April Su 4 11 18 25 1 8 15 22 1 8 15 22 29 5 12 19 26 Mo 5 12 19 26 2 9 16 23 2 9 16 23 30 6 13 20 27 Tu 6 13 20 27 3 10 17 24 3 10 17 24 31 7 14 21 28 We 7 14 21 28 4 11 18 25 4 11 18 25 1 8 15 22 29 Th 1 8 15 22 29 5 12 19 26 5 12 19 26 2 9 16 23 30 Fr 2 9 16 23 30 6 13 20 27 6 13 20 27 3 10 17 24 Sa 3 10 17 24 31 7 14 21 28 7 14 21 28 4 11 18 25 May June July August Su 3 10 17 24 31 7 14 21 28 5 12 19 26 2 9 16 23 30 Mo 4 11 18 25 1 8 15 22 29 6 13 20 27 3 10 17 24 31 Tu 5 12 19 26 2 9 16 23 30 7 14 21 28 4 11 18 25 We 6 13 20 27 3 10 17 24 1 8 15 22 29 5 12 19 26 Th 7 14 21 28 4 11 18 25 2 9 16 23 30 6 13 20 27 Fr 1 8 15 22 29 5 12 19 26 3 10 17 24 31 7 14 21 28 Sa 2 9 16 23 30 6 13 20 27 4 11 18 25 1 8 15 22 29 September October November December Su 6 13 20 27 4 11 18 25 1 8 15 22 29 6 13 20 27 Mo 7 14 21 28 5 12 19 26 2 9 16 23 30 7 14 21 28 Tu 1 8 15 22 29 6 13 20 27 3 10 17 24 1 8 15 22 29 We 2 9 16 23 30 7 14 21 28 4 11 18 25 2 9 16 23 30 Th 3 10 17 24 1 8 15 22 29 5 12 19 26 3 10 17 24 31 Fr 4 11 18 25 2 9 16 23 30 6 13 20 27 4 11 18 25 Sa 5 12 19 26 3 10 17 24 31 7 14 21 28 5 12 19 26 

... with current date marked with a "reversed block" number. Copy and paste as text.
The amount of work required depends on the tool you use.

edit: Typing at the Bash shell prompt;
$ y=2015; for (( i=1 ; i<13 ; i++)) do cal $i $y ; done | sed -re 's/([^ ]+ 2015|[a-zåäö]|[ 0-9] )/"\1",/g' | sed -re 's/"\,[ ]*$/"/' >calendar-$y.csv

Creates a CSV-file that can be loaded into any Spreadsheet. change y=2015 to reflect the year for which you wish create a calendar.