Creating an Annual Calendar with a Single-Row Formula in Google Sheets - 2

Last time, we created a simple single-column calendar using a single-row formula, but this time, let's try creating a slightly more practical calendar.

Previous article

Q2. If I enter a year in cell A1, I want to generate an annual calendar like the one below, starting on Sunday with 7 columns.

This time, we will also achieve this using a single-row formula (entering the formula in only one cell). In the capture above, the cell where the formula is entered is
B2.

It bothers me that 2/1 comes right after 1/31, but for now, let's assume that it's okay for the month transitions to be connected. (Because that's easier.) While we could use conditional formatting to gray out the parts where the previous or next year appears, this time, let's

hide them using the formula itself.

How about it? Do you think you can create the formula?

↓ The answer starts here.

A2. Annual calendar starting on Sunday with 7 columns created with a single-row formula

It's not that difficult, so this time, we'll use the answer immediately approach. First, let's create a simple formula.

Answer 1: First, create a formula that doesn't worry about the turn of the year

=SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1)

For now, this is fine.

I will explain the numerical values and the formula inside later. Before the explanation, let's try running it.

As with last time, the cell range to be expanded must be set to the 'Date' format in advance. Since it's hard to read with the year included, it's better to set it to "m/d" as a custom format.

It is correctly set up as a calendar where January 1, 2021, starts on a Friday.

However, the dates from the end of December of the previous year are being displayed. Although not visible in the image, the dates for January 2022 of the following year are also being displayed in the very last row at the bottom.

Conditional formatting could be used to hide parts that are not in the current year (by making the text white) or to make them look like a calendar with light gray text, but since I want to handle this on the formula side this time, I need to add one more tweak to the formula above.

Answer 2: Using IF for conditional branching to erase dates from the previous and following years [Completed Version]

=ARRAYFORMULA(IF(YEAR(SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1))=A1,
  SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1),))

Hmm, I might be told "this isn't one line" again, but it's just wrapped for readability; it is written in a single line.

When I try it here, only that year is displayed correctly! Mission accomplished.

The process is simple; I just extract the year from each generated date using the YEAR function and use IF to return the date only when it matches A1 (the specified year), and leave it blank if it does not match.

However, it is frustrating that sheet functions cannot be declared as variables (without using Lambda). The repeating part of the same content, in this case

appears twice, making the formula complicated.
When creating complex formulas, this happens often.

Of course, now you can shorten it with LAMBDA. I will also write the formula for when it is LAMBDA-fied (Lambda-ized) at the end.

Key points of this formula

Let's explain this formula. There are two key points.

1. The SEQUENCE function again (expanding into rows and columns)

2. Adjusting the start date by getting the day of the week as a number with the WEEKDAY function

Point 1. The SEQUENCE function again (expanding into rows and columns)

The SEQUENCE function, which is mir's favorite function (the function I'm rooting for), takes the following four arguments.

The second argument and subsequent arguments are optional; if omitted, they are treated as 1. In other words,

SEQUENCE(366) => SEQUENCE(366,1,1,1)
*Returns an array of 366 rows and 1 column starting at 1 and increasing by 1 downwards

is what it means.

The formula used this time,
SEQUENCE( 53 , 7 , DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1 ) ,

means the following. This allows the result of a single formula to be expanded vertically and horizontally.

By the way, the sequential numbers in SEQUENCE increase horizontally and wrap around to the next line.
That is why it is very suitable for creating calendars.

Point 2. Get the day of the week as a number using the WEEKDAY function (adjusting the start date)

Another point is the adjustment of the start date (start value).

It is this part below.

Since this is a "continuous" calendar (not concerned with month breaks), the initial start date (start value), which is the date entered in cell B2, should be the first Sunday on or before January 1st of the specified year.

This adjustment of the start date is

↑ handled by this formula.

The WEEKDAY function is a function that returns a numerical value corresponding to the day of the week for a given date.

By default,

This is how it is treated.

This time, I want to adjust the start value by converting the day of the week of DATE(A1,1,1) (January 1st of the year in A1) into a number using the WEEKDAY function. In other words, if 1/1 is a Sunday, no adjustment = 0 is fine, so I am adding +1 at the end.

With this, if 1/1 were a Monday,
WEEKDAY(DATE(A1,1,1)) would be 2,
and -WEEKDAY(DATE(A1,1,1))+1 would be -2 +1
which is -1, so the start value becomes

DATE(A1,1,1)-1 → the day before 1/1 → December 31st of the previous year

This becomes the most recent Sunday (start date) before 1/1 of that year.
By adjusting the start date this way, the dates will be entered into the correct day of the week (column).

Can you visualize it?

For now, an annual calendar with 7 columns starting on Sunday that displays the entire year was achieved with a single-line formula.

Single-line formula annual calendar: Application examples of this answer

Since this was a relatively simple topic, let's introduce some application examples of the answer.

Application: Trying to use LAMBDA (Lambda-ing)

=ARRAYFORMULA(IF(YEAR(SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1))=A1,
  SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1),))

Let's organize this formula by using LAMBDA (Lambda-ing it).

In the formula above,

SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1

appears twice as a long formula.
This time, as a test, we will only Lambda this part.

The basic idea of using LAMBDA is to treat this part as x.
In other words,

This is all you need to do.
The ability to write this within the formula is a feature of the LAMBDA function.

↓ LAMBDA version answer

=LAMBDA(x,ARRAYFORMULA(IF(YEAR(x)=A1,x,)))(SEQUENCE(53,7,DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1))+1))

You could DATE(A1,1,1) which appears twice, and Lambda it further (nesting LAMBDA), but that would stray from the main topic, so let's leave it at this for now.

If you want to learn more about the new LAMBDA function added to Google Sheets in September 2022, please refer to a casual explanation of the LAMBDA function.

Advanced Application: Single-Line Formula Monthly Calendar

By applying the annual calendar, it is also possible to create a monthly calendar with a single-line formula. This monthly calendar is simpler and might be in higher demand.

//A1セルに年、C1セルに月 の数字があるとして

=ARRAYFORMULA(IF(MONTH(DATE(A1,C1,SEQUENCE(6,7)-WEEKDAY(DATE(A1,C1,1))+1))=C1,
DATE(A1,C1,SEQUENCE(6,7)-WEEKDAY(DATE(A1,C1,1))+1),))

Similarly, if you want to always display the current month's calendar, you can use the following formula with the TODAY() function.
Since it doesn't reference any cells, you can use it by simply copying and pasting.

//当月のみ表示する式
=ARRAYFORMULA(IF(MONTH(EOMONTH(TODAY(),-1)-WEEKDAY(EOMONTH(TODAY(),-1))+SEQUENCE(6,7))=MONTH(TODAY()),
EOMONTH(TODAY(),-1)-WEEKDAY(EOMONTH(TODAY(),-1))+SEQUENCE(6,7),))

//月カレンダーなら前月、翌月は数式側ではそのままで、条件付き書式でグレー文字にするとかで良いかも
=ARRAYFORMULA(EOMONTH(TODAY(),-1)-WEEKDAY(EOMONTH(TODAY(),-1))+SEQUENCE(6,7))

That was an introduction to an application example.

For those who want to practice LAMBDA, try 'lambdifying' the monthly calendar yourself.

Q3. If I enter the year in cell A1, I want to generate an annual calendar that starts on Sunday, has 7 columns, and 'breaks (starts a new line) at the change of the month' like the one below.

We have created an annual calendar and an applied monthly calendar using single-line formulas.

However, I think some people will say that this annual calendar is hard to read because there are no month breaks!

'I want it to look like a calendar and be easy to read by separating it by month!'
Naturally, requests like this will come up.

As a final step, let's achieve this with a single-line formula in cell B2. It looks like the image above.

However, since it has become long, that will be for next time.

From here, the difficulty level increases by about 3 stages. It's about Gear 4.

Since there is some time until next week's article post, if you are a function geek, please try it yourself.

■ Next article in this series