It's 2024 soon, and it's time to plan for the new year as soon as the new year comes, and it's a great way to make plans with a calendar. And this calendar should best support any year, any month, that is, we often call a 10,000-year calendar.
There are many ways to make a perpetual calendar, and the method shared by Mr. Gu today is to make it with the core function sequence function with the date function and summary function, and after understanding the ideas and logic, you can easily make a perpetual calendar with the effect of Figure 1 as shown below.
Determine the year
"Perpetual calendar" is a metaphor, in fact, the year we need to use is about 20 years, so we take 365 days a year to calculate, 20 years, that is, 20 * 365 days. According to the arguments of the function sequence:
The first parameter: the number of rows, that is, the year, is entered into 20*365, of course, there is a leap year factor, and the second parameter: the number of columns, which is not entered here.
The third parameter: the number of starts, is the date of start, here January 1, 2023 is the start calculation, and the corresponding number is 44927
The fourth parameter: increment, the default is 1, no need to enter.
Enter the corresponding function:
sequence(20*365,,44927)
The effect is shown in Figure 2 below
Year, month, day, week, week
Once we have dates, we need to determine the year, month, day, week, and week and week of the week. The function of judging this information is also very easy to remember, that is, the corresponding English, and the parameters are relatively simple, that is, you can select the date just generated. Enter the following functions separately:
Year: C3=year(B3), the year in which the date is judged;
Month: d3=month(b3), the month of the judgment date;
Day: e3=day(b3), the specific date of the judgment date;
Week: F3=Weeknum(B3,2), pay attention to select parameter 2, which means starting from Monday.
Day: g3=weekday(b3,2), pay attention to select parameter 2, which means starting from Monday.
The effect is shown in Figure 3 below
Determine the query format
The date function determines the specific year, month, day, as well as the number of weeks and the end of the week. Start to design the format corresponding to the perpetual calendar, a conditional area, the conditional area is made with the data verification drop-down option, and two auxiliary column formulas are entered:
Query year: Q3=unique(c3), delete the duplicate value of the year;
Query day: r3=unique(d3), delete the duplicate value of the month;
i2 sets the data validity, and the point sequence is **=q3
i3 also sets the data validity, in sequence the point sequence **=r3
In this way, the query conditions will be pulled down.
After completion, it is shown in Figure 4 below
Format the perpetual calendar:
After the query format is set, the format of the perpetual calendar is set, and the day of the week is entered firstEntry Function:
i5=sequence(,7,2), produce a column of numbers, and format it as "aaa", so that you get the title of the week, and if you don't want to enter the formula, you can also enter it directly.
1 2 3 4 5 6 ".
When completed, it is shown in Figure 5 below
Set the perpetual calendar function:
After the query format is set and the week title is set, you need to start designing the function of the perpetual calendar, which is actually the result of a multi-condition summation.
Input function: i6=sumifs(e3 ,c3 ,i2,d3 ,i3,f3 ,unique(filter(f3 ,(c3 =i2)*(d3 =i3))))g3 ,sequence(,7))).
Function Definition: Summing area: e3, day;Displays each day that the conditions are met;
Condition 1 area: C3, year column;
Condition 1: Judgment Condition 1: i2=2024;
Condition 2 area: d3, month column;
Condition 2: Judgment condition 2: i3 = 1 month;
Condition 3 area: f3, week column;
Condition 3: Condition 3: The number of weeks after the unique(filter(f3,(c3=i2)*(d3=i3))) is deduplicated, and the number (1,2,3,4,5) is returned
Condition 4 area: G3, week of the week;
Condition 4: Judgment condition 4: sequence(,7), returns the number, that is, the day of the week.
If you meet this at the same time, it is the calendar of this month, dynamic array formula, one-click generation;
When completed, it is shown in Figure 6 below
Set up a focused work calendar:
Table 1 is the 10,000-year work calendar, which can be queried according to this work calendar, and has begun to evolve into a variety of needs.
Monthly Priorities:
Enter the formula: b5=chooserows (Table 1!).i6#
b7=chooserows (Table 1!.)i6#
.5 formulas have been entered, and the meaning of the formula is that the above results are selected as the first row and the second line ......The purpose of this is to insert a row of editable cells in the next row, so that it is convenient to fill in the monthly work plan. Usually, you can mark the content of the key meeting first.
After completion, after setting the cell formatting, color matching, conditional formatting, etc., the effect after the setting is completed is shown in Figure 7 below
Weekly Work List:
Each week of the year can be filtered out separately to make a work plan, and the filter conditions can be entered in advance, for example: filter year, filter week, and with these two filter conditions, enter the following functions:
date=filter (Table 1!.)b3, (Table 1!.)f3 = c2)* (Table 1!.)c3#=b2))
Day = b5
Just get a list of your weekly work.
As shown in Figure 8 below: